Thread: Contraints problem in PLPGSQL

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