diff options
Diffstat (limited to 'contrib/spi/expected/refint.out')
| -rw-r--r-- | contrib/spi/expected/refint.out | 113 |
1 files changed, 113 insertions, 0 deletions
diff --git a/contrib/spi/expected/refint.out b/contrib/spi/expected/refint.out new file mode 100644 index 00000000000..79633603217 --- /dev/null +++ b/contrib/spi/expected/refint.out @@ -0,0 +1,113 @@ +CREATE EXTENSION refint; +create table pkeys (pkey1 int4 not null, pkey2 text not null); +create table fkeys (fkey1 int4, fkey2 text, fkey3 int); +create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null); +create index fkeys_i on fkeys (fkey1, fkey2); +create index fkeys2_i on fkeys2 (fkey21, fkey22); +create index fkeys2p_i on fkeys2 (pkey23); +insert into pkeys values (10, '1'); +insert into pkeys values (20, '2'); +insert into pkeys values (30, '3'); +insert into pkeys values (40, '4'); +insert into pkeys values (50, '5'); +insert into pkeys values (60, '6'); +create unique index pkeys_i on pkeys (pkey1, pkey2); +-- +-- For fkeys: +-- (fkey1, fkey2) --> pkeys (pkey1, pkey2) +-- (fkey3) --> fkeys2 (pkey23) +-- +create trigger check_fkeys_pkey_exist + after insert or update on fkeys + for each row + execute function + check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); +create trigger check_fkeys_pkey2_exist + after insert or update on fkeys + for each row + execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23'); +-- +-- For fkeys2: +-- (fkey21, fkey22) --> pkeys (pkey1, pkey2) +-- +create trigger check_fkeys2_pkey_exist + after insert or update on fkeys2 + for each row + execute procedure + check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2'); +-- +-- For pkeys: +-- ON DELETE/UPDATE (pkey1, pkey2) CASCADE: +-- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22) +-- +create trigger check_pkeys_fkey_cascade + after delete or update on pkeys + for each row + execute procedure + check_foreign_key (2, 'cascade', 'pkey1', 'pkey2', + 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22'); +-- +-- For fkeys2: +-- ON DELETE/UPDATE (pkey23) RESTRICT: +-- fkeys (fkey3) +-- +create trigger check_fkeys2_fkey_restrict + after delete or update on fkeys2 + for each row + execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3'); +insert into fkeys2 values (10, '1', 1); +insert into fkeys2 values (30, '3', 2); +insert into fkeys2 values (40, '4', 5); +insert into fkeys2 values (50, '5', 3); +-- no key in pkeys +insert into fkeys2 values (70, '5', 3); +ERROR: tuple references non-existent key +DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys". +insert into fkeys values (10, '1', 2); +insert into fkeys values (30, '3', 3); +insert into fkeys values (40, '4', 2); +insert into fkeys values (50, '5', 2); +-- no key in pkeys +insert into fkeys values (70, '5', 1); +ERROR: tuple references non-existent key +DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys". +-- no key in fkeys2 +insert into fkeys values (60, '6', 4); +ERROR: tuple references non-existent key +DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2". +delete from pkeys where pkey1 = 30 and pkey2 = '3'; +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted +ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys" +CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 " +delete from pkeys where pkey1 = 40 and pkey2 = '4'; +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted +update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5'; +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are updated +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are updated +update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1'; +ERROR: duplicate key value violates unique constraint "pkeys_i" +DETAIL: Key (pkey1, pkey2)=(7, 70) already exists. +SELECT trigger_name, event_manipulation, event_object_schema, event_object_table, + action_order, action_condition, action_orientation, action_timing, + action_reference_old_table, action_reference_new_table + FROM information_schema.triggers + WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2') + ORDER BY trigger_name COLLATE "C", 2; + trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table +----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+---------------------------- + check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | AFTER | | + check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | AFTER | | + check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | AFTER | | + check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | AFTER | | + check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | AFTER | | + check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | AFTER | | + check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | AFTER | | + check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | AFTER | | + check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | AFTER | | + check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | AFTER | | +(10 rows) + +DROP TABLE pkeys; +DROP TABLE fkeys; +DROP TABLE fkeys2; |