From 5e45211a64149b3c659b90ff2de6fa982a5a93ed Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:17:33 +0200 Subject: Adding upstream version 15.5. Signed-off-by: Daniel Baumann --- contrib/spi/refint.example | 82 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 82 insertions(+) create mode 100644 contrib/spi/refint.example (limited to 'contrib/spi/refint.example') diff --git a/contrib/spi/refint.example b/contrib/spi/refint.example new file mode 100644 index 0000000..299166d --- /dev/null +++ b/contrib/spi/refint.example @@ -0,0 +1,82 @@ +--Column ID of table A is primary key: + +CREATE TABLE A ( + ID int4 not null +); +CREATE UNIQUE INDEX AI ON A (ID); + +--Columns REFB of table B and REFC of C are foreign keys referencing ID of A: + +CREATE TABLE B ( + REFB int4 +); +CREATE INDEX BI ON B (REFB); + +CREATE TABLE C ( + REFC int4 +); +CREATE INDEX CI ON C (REFC); + +--Trigger for table A: + +CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON A FOR EACH ROW +EXECUTE PROCEDURE +check_foreign_key (2, 'cascade', 'ID', 'B', 'REFB', 'C', 'REFC'); +/* +2 - means that check must be performed for foreign keys of 2 tables. +cascade - defines that corresponding keys must be deleted. +ID - name of primary key column in triggered table (A). You may + use as many columns as you need. +B - name of (first) table with foreign keys. +REFB - name of foreign key column in this table. You may use as many + columns as you need, but number of key columns in referenced + table (A) must be the same. +C - name of second table with foreign keys. +REFC - name of foreign key column in this table. +*/ + +--Trigger for table B: + +CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW +EXECUTE PROCEDURE +check_primary_key ('REFB', 'A', 'ID'); + +/* +REFB - name of foreign key column in triggered (B) table. You may use as + many columns as you need, but number of key columns in referenced + table must be the same. +A - referenced table name. +ID - name of primary key column in referenced table. +*/ + +--Trigger for table C: + +CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW +EXECUTE PROCEDURE +check_primary_key ('REFC', 'A', 'ID'); + +-- Now try + +INSERT INTO A VALUES (10); +INSERT INTO A VALUES (20); +INSERT INTO A VALUES (30); +INSERT INTO A VALUES (40); +INSERT INTO A VALUES (50); + +INSERT INTO B VALUES (1); -- invalid reference +INSERT INTO B VALUES (10); +INSERT INTO B VALUES (30); +INSERT INTO B VALUES (30); + +INSERT INTO C VALUES (11); -- invalid reference +INSERT INTO C VALUES (20); +INSERT INTO C VALUES (20); +INSERT INTO C VALUES (30); + +DELETE FROM A WHERE ID = 10; +DELETE FROM A WHERE ID = 20; +DELETE FROM A WHERE ID = 30; + +SELECT * FROM A; +SELECT * FROM B; +SELECT * FROM C; -- cgit v1.2.3