Hello!

I encounter the following problems in v7.3.2.

CREATE TABLE tb1 (c1 INTEGER PRIMARY KEY);

CREATE TABLE tb2 (
c1 INTEGER,
c2 INTEGER,
PRIMARY KEY(c1,c2),
CONSTRAINT fktb2 FOREIGN KEY (c1) REFERENCES tb1 (c1) ON UPDATE CASCADE
ON DELETE CASCADE
);

CREATE OR REPLACE FUNCTION test1(INTEGER) RETURNS BOOLEAN AS '
BEGIN
DELETE FROM tb1 WHERE c1 = $1;

INSERT INTO tb1 VALUES($1);
INSERT INTO tb2 VALUES($1,200);

RETURN TRUE;
END' LANGUAGE PLPGSQL STABLE;

SELECT test1(1);
SELECT test1(1);

The second "SELECT test1(1)" produces:

ERROR: Canont insert a duplicate key into unique index tb2_key

If rows in tb1 is deleted in psql prompt:

DELETE FROM tb1;

then "SELECT test1(1)" works again.

CREATE OR REPLACE FUNCTION test2(INTEGER) RETURNS INT2 AS '
DECLARE
n int2;
BEGIN
DELETE FROM tb2 WHERE c1 = $1;
DELETE FROM tb1 WHERE c1 = $1;

INSERT INTO tb1 VALUES($1);
INSERT INTO tb2 VALUES($1,200);

GET DIAGNOSTICS n=ROW_COUNT;
RETURN n;
END' LANGUAGE PLPGSQL STABLE;

SELECT test2(1); --1 row is returned
SELECT test2(1); --1 row is returned
SELECT * FROM tb2; --0 row is returned.

Regards,

CN

--
http://www.fastmail.fm - The way an email service should be

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings