RI within PLPGSQL

Поиск
Список
Период
Сортировка
От cnliou
Тема RI within PLPGSQL
Дата
Msg-id 1071225309.92707.cnliou@so-net.net.tw
обсуждение исходный текст
Ответы Re: RI within PLPGSQL  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список pgsql-bugs
Hi!

It looks to me a referential integrity problem (only?)
within PLPGSQL. Plesase see the test result below.

Thank you!
CN
=======
CREATE TABLE test1(c1 INTEGER PRIMARY KEY) WITHOUT OIDS;

CREATE TABLE test2
( c1 INTEGER,
  c2 INTEGER,
  PRIMARY KEY (c1,c2),
  CONSTRAINT ctest2 FOREIGN KEY (c1) REFERENCES test1 (c1)
ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION f1(int) RETURNS VOID AS '
BEGIN
  DELETE FROM test1 WHERE c1= $1;

  INSERT INTO test1 VALUES($1);
  INSERT INTO test2 VALUES($1,2);
  RETURN;
END' LANGUAGE 'plpgsql' STABLE;

CREATE OR REPLACE FUNCTION f2(int) RETURNS VOID AS '
BEGIN
  DELETE FROM test2 WHERE c1= $1;
  DELETE FROM test1 WHERE c1= $1;

  INSERT INTO test1 VALUES($1);
  INSERT INTO test2 VALUES($1,2);
  RETURN;
END' LANGUAGE 'plpgsql' STABLE;

db1=# select f1(1);
 f1
----

(1 row)

db1=# select * from test1; select * from test2;
 c1
----
  1
(1 row)

 c1 | c2
----+----
  1 |  2
(1 row)

db1=# select f1(1);
ERROR:  duplicate key violates unique constraint
"test2_pkey"
CONTEXT:  PL/pgSQL function "f1" line 5 at SQL statement
db1=# select * from test1; select * from test2;
 c1
----
  1
(1 row)

 c1 | c2
----+----
  1 |  2
(1 row)

db1=# select f2(1);
 f2
----

(1 row)

db1=# select * from test1; select * from test2;
 c1
----
  1
(1 row)

 c1 | c2
----+----
(0 rows)

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Seum-Lim Gan
Дата:
Сообщение: Re: dyntest.pgc not working in 7.4 ?
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: RI within PLPGSQL