Add missing /contrib files

2 files changed, 172 insertions, 0 deletions

diff --git a/contrib/spi/README.spi b/contrib/spi/README.spi
new file mode 100644
index 0000000000..65868f0fc7
--- /dev/null
+++ b/contrib/spi/README.spi
@@ -0,0 +1,104 @@
+
+Here are general trigger functions provided as workable examples
+of using SPI and triggers. "General" means that functions may be
+used for defining triggers for any tables but you have to specify
+table/field names (as described below) while creating a trigger.
+
+1. refint.c - functions for implementing referential integrity.
+
+check_primary_key () is to used for foreign keys of a table.
+   
+   You are to create trigger (BEFORE INSERT OR UPDATE) using this 
+function on a table referencing another table. You are to specify
+as function arguments: triggered table column names which correspond
+to foreign key, referenced table name and column names in referenced
+table which correspond to primary/unique key.
+   You may create as many triggers as you need - one trigger for
+one reference.
+
+check_foreign_key () is to used for primary/unique keys of a table.
+
+   You are to create trigger (BEFORE DELETE OR UPDATE) using this
+function on a table referenced by another table(s). You are to specify
+as function arguments: number of references for which function has to
+performe checking, action if referencing key found ('cascade' - to delete
+corresponding foreign key, 'restrict' - to abort transaction if foreign keys 
+exist, 'setnull' - to set foreign key referencing primary/unique key
+being deleted to null), triggered table column names which correspond
+to primary/unique key, referencing table name and column names corresponding
+to foreign key (, ... - as many referencing tables/keys as specified
+by first argument).
+   Note, that NOT NULL constraint and unique index have to be defined by
+youself.
+
+   There are examples in refint.example and regression tests
+(sql/triggers.sql).
+
+   To CREATE FUNCTIONs use refint.sql (will be made by gmake from
+refint.source).
+
+
+2. timetravel.c - functions for implementing time travel feature.
+
+   Old internally supported time-travel (TT) used insert/delete
+transaction commit times. To get the same feature using triggers
+you are to add to a table two columns of abstime type to store
+date when a tuple was inserted (start_date) and changed/deleted 
+(stop_date):
+
+CREATE TABLE XXX (
+	...		...
+	date_on		abstime default currabstime(),
+	date_off	abstime default 'infinity'
+	...		...
+);
+
+- so, tuples being inserted with NULLs in date_on/date_off will get
+_current_date_ in date_on (name of start_date column in XXX) and INFINITY in
+date_off (name of stop_date column in XXX).
+
+   Tuples with stop_date equal INFINITY are "valid now": when trigger will
+be fired for UPDATE/DELETE of a tuple with stop_date NOT equal INFINITY then
+this tuple will not be changed/deleted!
+
+   If stop_date equal INFINITY then on
+
+UPDATE: only stop_date in tuple being updated will be changed to current
+date and new tuple with new data (coming from SET ... in UPDATE) will be
+inserted. Start_date in this new tuple will be setted to current date and
+stop_date - to INFINITY.
+
+DELETE: new tuple will be inserted with stop_date setted to current date
+(and with the same data in other columns as in tuple being deleted).
+
+   NOTE:
+1. To get tuples "valid now" you are to add _stop_date_ = 'infinity'
+   to WHERE. Internally supported TT allowed to avoid this...
+   Fixed rewriting RULEs could help here...
+   As work arround you may use VIEWs...
+2. You can't change start/stop date columns with UPDATE! 
+   Use set_timetravel (below) if you need in this.
+
+   FUNCTIONs:
+
+timetravel() is general trigger function.
+
+   You are to create trigger BEFORE (!!!) UPDATE OR DELETE using this
+function on a time-traveled table. You are to specify two arguments: name of
+start_date column and name of stop_date column in triggered table.
+
+currabstime() may be used in DEFAULT for start_date column to get
+current date.
+
+set_timetravel() allows you turn time-travel ON/OFF for a table:
+
+   set_timetravel('XXX', 1) will turn TT ON for table XXX (and report
+old status).
+   set_timetravel('XXX', 0) will turn TT OFF for table XXX (-"-).
+
+Turning TT OFF allows you do with a table ALL what you want.
+
+   There is example in timetravel.example.
+
+   To CREATE FUNCTIONs use timetravel.sql (will be made by gmake from
+timetravel.source).
diff --git a/contrib/spi/new_example.example b/contrib/spi/new_example.example
new file mode 100644
index 0000000000..0204907894
--- /dev/null
+++ b/contrib/spi/new_example.example
@@ -0,0 +1,68 @@
+--Column ID of table A is primary key:
+
+CREATE TABLE A (
+	ID	int4 not null,
+	id1	int4 not null,
+primary key (ID,ID1)
+);
+
+--Columns REFB of table B and REFC of C are foreign keys referenting ID of A:
+
+CREATE TABLE B (
+	REFB	int4,
+	REFB1	INT4
+);
+CREATE INDEX BI ON B (REFB);
+
+CREATE TABLE C (
+	REFC	int4,
+	REFC1	int4
+);
+CREATE INDEX CI ON C (REFC);
+
+--Trigger for table A:
+
+CREATE TRIGGER AT BEFORE DELETE  ON A FOR EACH ROW
+EXECUTE PROCEDURE 
+check_foreign_key (2, 'cascade', 'ID','id1', 'B', 'REFB','REFB1', 'C', 'REFC','REFC1');
+
+
+CREATE TRIGGER AT1  AFTER UPDATE  ON A FOR EACH ROW
+EXECUTE PROCEDURE 
+check_foreign_key (2, 'cascade', 'ID','id1', 'B', 'REFB','REFB1', 'C', 'REFC','REFC1');
+
+
+CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW
+EXECUTE PROCEDURE 
+check_primary_key ('REFB','REFB1', 'A', 'ID','ID1');
+
+CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW
+EXECUTE PROCEDURE 
+check_primary_key ('REFC','REFC1', 'A', 'ID','ID1');
+
+
+
+-- Now try
+
+INSERT INTO A VALUES (10,10);
+INSERT INTO A VALUES (20,20);
+INSERT INTO A VALUES (30,30);
+INSERT INTO A VALUES (40,41);
+INSERT INTO A VALUES (50,50);
+
+INSERT INTO B VALUES (1);	-- invalid reference
+INSERT INTO B VALUES (10,10);
+INSERT INTO B VALUES (30,30);
+INSERT INTO B VALUES (30,30);
+
+INSERT INTO C VALUES (11);	-- invalid reference
+INSERT INTO C VALUES (20,20);
+INSERT INTO C VALUES (20,21);
+INSERT INTO C VALUES (30,30);
+
+-- now update work well 
+update  A set ID = 100 , ID1 = 199 where ID=30 ;
+
+SELECT * FROM A;
+SELECT * FROM B;
+SELECT * FROM C;