it seems to be a simple problem, but it is not.
i have declared 2 simple tables:
CREATE TABLE public.test1
(
id int4 NOT NULL,
data float4,
CONSTRAINT mytest_pkey PRIMARY KEY (id)
) WITH OIDS;
CREATE TABLE public.test2
(
id1 int4 NOT NULL,
data1 float4,
CONSTRAINT test2_pkey PRIMARY KEY (id1),
CONSTRAINT "$1" FOREIGN KEY (id1) REFERENCES public.test1 (id) ON UPDATE
CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
) WITH OIDS;
then i have written a function in PLPGSQL:
CREATE OR REPLACE FUNCTION public.test_func()
RETURNS bool AS
'
DECLARE
mission RECORD;
ret bool;
BEGIN
ret:=FALSE;
raise notice\'begin\';
SET CONSTRAINTS ALL IMMEDIATE;
FOR mission IN SELECT * FROM public.test1
LOOP
raise notice\'before update\';
UPDATE public.test2 SET data1=data1+1;
END LOOP;
FOR mission IN SELECT * FROM public.test1
LOOP
raise notice\'after update\';
DELETE FROM public.test1 WHERE id=mission.id;
END LOOP;
ret:=TRUE;
raise notice\'end\';
RETURN ret;
END;'
LANGUAGE 'plpgsql' VOLATILE;
my expecting behavior is:
update all rows in test2
delete all from test1 und then delete all from test1 because of the ON
DELETE CASCADE
BUT:
NOTICE: begin
NOTICE: before update
NOTICE: before update
NOTICE: before update
NOTICE: after update
NOTICE: after update
NOTICE: after update
NOTICE: end
ERROR: insert or update on table "test2" violates foreign key constraint
"$1"
DETAIL: Key (id1)=(1) is not present in table "test1".
why?
i have asked already in postgresql chan for help, but nobody could solve
the problem.
I hope you can.
Big THX
Oleg
PS: i tried NOT DEFERRABLE too, it does not work too. :-(
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
