readme.spi

来自「PostgreSQL 8.2中增加了很多企业用户所需要的功能和性能上的提高,其开」· SPI 代码 · 共 105 行

SPI
105 行
字号
Here are general trigger functions provided as workable examplesof using SPI and triggers. "General" means that functions may beused for defining triggers for any tables but you have to specifytable/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 specifyas function arguments: triggered table column names which correspondto foreign key, referenced table name and column names in referencedtable which correspond to primary/unique key.   You may create as many triggers as you need - one trigger forone reference.check_foreign_key () is to used for primary/unique keys of a table.   You are to create trigger (BEFORE DELETE OR UPDATE) using thisfunction on a table referenced by another table(s). You are to specifyas function arguments: number of references for which function has toperforme checking, action if referencing key found ('cascade' - to deletecorresponding foreign key, 'restrict' - to abort transaction if foreign keys exist, 'setnull' - to set foreign key referencing primary/unique keybeing deleted to null), triggered table column names which correspondto primary/unique key, referencing table name and column names correspondingto foreign key (, ... - as many referencing tables/keys as specifiedby first argument).   Note, that NOT NULL constraint and unique index have to be defined byyouself.   There are examples in refint.example and regression tests(sql/triggers.sql).   To CREATE FUNCTIONs use refint.sql (will be made by gmake fromrefint.source).2. timetravel.c - functions for implementing time travel feature.   Old internally supported time-travel (TT) used insert/deletetransaction commit times. To get the same feature using triggersyou are to add to a table two columns of abstime type to storedate 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 indate_off (name of stop_date column in XXX).   Tuples with stop_date equal INFINITY are "valid now": when trigger willbe fired for UPDATE/DELETE of a tuple with stop_date NOT equal INFINITY thenthis tuple will not be changed/deleted!   If stop_date equal INFINITY then onUPDATE: only stop_date in tuple being updated will be changed to currentdate and new tuple with new data (coming from SET ... in UPDATE) will beinserted. Start_date in this new tuple will be setted to current date andstop_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 thisfunction on a time-traveled table. You are to specify two arguments: name ofstart_date column and name of stop_date column in triggered table.currabstime() may be used in DEFAULT for start_date column to getcurrent 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 reportold 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 fromtimetravel.source).

⌨️ 快捷键说明

复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?