hi,
i have table1 and table2. in table1 there is a foreign key referencing id
in table2. i'd like to do something like this:
CREATE FUNCTION foo() RETURNS INTEGER AS '
DECLARE
BEGIN
UPDATE table1 SET not_key = 100;
DELETE FROM table1;
DELETE FROM table2;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
when i run select foo(); i always get:
ERROR: <unnamed> referential integrity violation - key referenced from
table1 not found in table2
(i think that's because constraints are checked at the end of statement
and this function is treated as one statement... or am i wrong?)
when i try it in psql as separate commands in one transaction it's ok:
BEGIN;
UPDATE table1 SET not_key = 100;
DELETE FROM table1;
DELETE FROM table2;
COMMIT;
is there any way to do something like this in plpgsql function?
thanks in advance,
kuba
