And has anyone any recommendations how to get around this? The only thing I
can think of is to make 2 separate calls from the client, which I would be
very sad to do.
This demonstrates what I'm talking about. Run each block and check the
output before proceeding to the next block (don't run line by line).
Thanks for any insight.
Berg
-- BLOCK 1
CREATE TABLE t1 (
t1_code char(3) NOT NULL PRIMARY KEY
);
CREATE TABLE t2 (
t1_code char(3) REFERENCES t1 (t1_code) ON UPDATE CASCADE ON DELETE SET
NULL,
t2_name varchar
);
INSERT INTO t1 (t1_code) VALUES ('ABC');
INSERT INTO t2 (t1_code, t2_name) VALUES ('ABC', 'I refer to ABC');
UPDATE t1 SET t1_code = 'DEF' WHERE t1_code = 'ABC';
UPDATE t2 SET t2_name = 'I refer to ' || t1_code;
SELECT * FROM t2;
-- shows ABC | I refer to ABC
-- BLOCK 2
SELECT * FROM t2;
-- shows DEF | I refere to ABC
-- BLOCK 3 : clean up
DROP TABLE t2;
DROP TABLE t1;
> Within a function (and therefore within a transaction), if I UPDATE the
> primary key of a table, the old value remains visible in the child table
> through the end of the function. Is this
8.0 runs immediate triggers after each statement of a function and also
forces referential actions to be treated as immediate and as such should
change the behavior mentioned.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)