summaryrefslogtreecommitdiff
path: root/contrib/spi
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/spi')
-rw-r--r--contrib/spi/.gitignore4
-rw-r--r--contrib/spi/Makefile2
-rw-r--r--contrib/spi/expected/autoinc.out50
-rw-r--r--contrib/spi/expected/refint.out113
-rw-r--r--contrib/spi/meson.build12
-rw-r--r--contrib/spi/sql/autoinc.sql33
-rw-r--r--contrib/spi/sql/refint.sql97
7 files changed, 311 insertions, 0 deletions
diff --git a/contrib/spi/.gitignore b/contrib/spi/.gitignore
new file mode 100644
index 00000000000..5dcb3ff9723
--- /dev/null
+++ b/contrib/spi/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/spi/Makefile b/contrib/spi/Makefile
index c9c34ff3889..7ccbef8c926 100644
--- a/contrib/spi/Makefile
+++ b/contrib/spi/Makefile
@@ -10,6 +10,8 @@ DATA = autoinc--1.0.sql \
refint--1.0.sql
PGFILEDESC = "spi - examples of using SPI and triggers"
+REGRESS = autoinc refint
+
DOCS = $(addsuffix .example, $(MODULES))
# this is needed for the regression tests;
diff --git a/contrib/spi/expected/autoinc.out b/contrib/spi/expected/autoinc.out
new file mode 100644
index 00000000000..07ae8ad1f2b
--- /dev/null
+++ b/contrib/spi/expected/autoinc.out
@@ -0,0 +1,50 @@
+CREATE EXTENSION autoinc;
+create sequence aitest_seq increment 10 start 0 minvalue 0;
+create table aitest (
+ price_id int4,
+ price_val int4,
+ price_on int4
+);
+create trigger aiserial
+ before insert or update on aitest
+ for each row
+ execute procedure
+ autoinc (price_on, aitest_seq);
+insert into aitest values (1, 1, null);
+insert into aitest values (2, 2, 0);
+insert into aitest values (3, 3, 1);
+select * from aitest;
+ price_id | price_val | price_on
+----------+-----------+----------
+ 1 | 1 | 10
+ 2 | 2 | 20
+ 3 | 3 | 1
+(3 rows)
+
+update aitest set price_on = 11;
+select * from aitest;
+ price_id | price_val | price_on
+----------+-----------+----------
+ 1 | 1 | 11
+ 2 | 2 | 11
+ 3 | 3 | 11
+(3 rows)
+
+update aitest set price_on = 0;
+select * from aitest;
+ price_id | price_val | price_on
+----------+-----------+----------
+ 1 | 1 | 30
+ 2 | 2 | 40
+ 3 | 3 | 50
+(3 rows)
+
+update aitest set price_on = null;
+select * from aitest;
+ price_id | price_val | price_on
+----------+-----------+----------
+ 1 | 1 | 60
+ 2 | 2 | 70
+ 3 | 3 | 80
+(3 rows)
+
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;
diff --git a/contrib/spi/meson.build b/contrib/spi/meson.build
index eeab1ab210b..3832a91019a 100644
--- a/contrib/spi/meson.build
+++ b/contrib/spi/meson.build
@@ -107,3 +107,15 @@ install_data('refint.control', 'refint--1.0.sql',
install_data('refint.example',
kwargs: contrib_doc_args,
)
+
+tests += {
+ 'name': 'spi',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'regress': {
+ 'sql': [
+ 'autoinc',
+ 'refint',
+ ],
+ },
+}
diff --git a/contrib/spi/sql/autoinc.sql b/contrib/spi/sql/autoinc.sql
new file mode 100644
index 00000000000..b240dcdc082
--- /dev/null
+++ b/contrib/spi/sql/autoinc.sql
@@ -0,0 +1,33 @@
+CREATE EXTENSION autoinc;
+
+create sequence aitest_seq increment 10 start 0 minvalue 0;
+
+create table aitest (
+ price_id int4,
+ price_val int4,
+ price_on int4
+);
+
+create trigger aiserial
+ before insert or update on aitest
+ for each row
+ execute procedure
+ autoinc (price_on, aitest_seq);
+
+insert into aitest values (1, 1, null);
+insert into aitest values (2, 2, 0);
+insert into aitest values (3, 3, 1);
+
+select * from aitest;
+
+update aitest set price_on = 11;
+
+select * from aitest;
+
+update aitest set price_on = 0;
+
+select * from aitest;
+
+update aitest set price_on = null;
+
+select * from aitest;
diff --git a/contrib/spi/sql/refint.sql b/contrib/spi/sql/refint.sql
new file mode 100644
index 00000000000..63458127917
--- /dev/null
+++ b/contrib/spi/sql/refint.sql
@@ -0,0 +1,97 @@
+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);
+
+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);
+-- no key in fkeys2
+insert into fkeys values (60, '6', 4);
+
+delete from pkeys where pkey1 = 30 and pkey2 = '3';
+delete from pkeys where pkey1 = 40 and pkey2 = '4';
+update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
+update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
+
+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;
+
+DROP TABLE pkeys;
+DROP TABLE fkeys;
+DROP TABLE fkeys2;
generated by cgit v1.2.3 (git 2.39.1) at 2025-07-17 01:03:31 +0000