Movatterモバイル変換

F.10. spi
Prev	Up	Appendix F. Additional Supplied Modules	Home	Next

F.10. spi

F.10.1. refint — Functions for Implementing Referential Integrity
F.10.2. timetravel — Functions for Implementing Time Travel
F.10.3. autoinc — Functions for Autoincrementing Fields
F.10.4. insert_username — Functions for Tracking Who Changed a Table
F.10.5. moddatetime — Functions for Tracking Last Modification Time

Thespi module provides several workable examples of using theServer Programming Interface (SPI) and triggers. While these functions are of some value in their own right, they are even more useful as examples to modify for your own purposes. The functions are general enough to be used with any table, but you have to specify table and field names (as described below) while creating a trigger.

Each of the groups of functions described below is provided as a separately-installable extension.

F.10.1. refint — Functions for Implementing Referential Integrity

check_primary_key() andcheck_foreign_key() are used to check foreign key constraints. (This functionality is long since superseded by the built-in foreign key mechanism, of course, but the module is still useful as an example.)

check_primary_key() checks the referencing table. To use, create aBEFORE INSERT OR UPDATE trigger using this function on a table referencing another table. Specify as the trigger arguments: the referencing table's column name(s) which form the foreign key, the referenced table name, and the column names in the referenced table which form the primary/unique key. To handle multiple foreign keys, create a trigger for each reference.

check_foreign_key() checks the referenced table. To use, create aBEFORE DELETE OR UPDATE trigger using this function on a table referenced by other table(s). Specify as the trigger arguments: the number of referencing tables for which the function has to perform checking, the action if a referencing key is found (cascade — to delete the referencing row,restrict — to abort transaction if referencing keys exist,setnull — to set referencing key fields to null), the triggered table's column names which form the primary/unique key, then the referencing table name and column names (repeated for as many referencing tables as were specified by first argument). Note that the primary/unique key columns should be marked NOT NULL and should have a unique index.

There are examples inrefint.example.

F.10.2. timetravel — Functions for Implementing Time Travel

Long ago,Postgres Pro had a built-in time travel feature that kept the insert and delete times for each tuple. This can be emulated using these functions. To use these functions, you must add to a table two columns ofabstime type to store the date when a tuple was inserted (start_date) and changed/deleted (stop_date):

CREATE TABLE mytab (        ...             ...        start_date      abstime,        stop_date       abstime        ...             ...);

The columns can be named whatever you like, but in this discussion we'll call them start_date and stop_date.

When a new row is inserted, start_date should normally be set to current time, and stop_date toinfinity. The trigger will automatically substitute these values if the inserted data contains nulls in these columns. Generally, inserting explicit non-null data in these columns should only be done when re-loading dumped data.

Tuples with stop_date equal toinfinity are“valid now”, and can be modified. Tuples with a finite stop_date cannot be modified anymore — the trigger will prevent it. (If you need to do that, you can turn off time travel as shown below.)

For a modifiable row, on update only the stop_date in the tuple being updated will be changed (to current time) and a new tuple with the modified data will be inserted. Start_date in this new tuple will be set to current time and stop_date toinfinity.

A delete does not actually remove the tuple but only sets its stop_date to current time.

To query for tuples“valid now”, includestop_date = 'infinity' in the query's WHERE condition. (You might wish to incorporate that in a view.) Similarly, you can query for tuples valid at any past time with suitable conditions on start_date and stop_date.

timetravel() is the general trigger function that supports this behavior. Create aBEFORE INSERT OR UPDATE OR DELETE trigger using this function on each time-traveled table. Specify two trigger arguments: the actual names of the start_date and stop_date columns. Optionally, you can specify one to three more arguments, which must refer to columns of typetext. The trigger will store the name of the current user into the first of these columns during INSERT, the second column during UPDATE, and the third during DELETE.

set_timetravel() allows you to turn time-travel on or off for a table.set_timetravel('mytab', 1) will turn TT ON for tablemytab.set_timetravel('mytab', 0) will turn TT OFF for tablemytab. In both cases the old status is reported. While TT is off, you can modify the start_date and stop_date columns freely. Note that the on/off status is local to the current database session — fresh sessions will always start out with TT ON for all tables.

get_timetravel() returns the TT state for a table without changing it.

There is an example intimetravel.example.

F.10.3. autoinc — Functions for Autoincrementing Fields

autoinc() is a trigger that stores the next value of a sequence into an integer field. This has some overlap with the built-in“serial column” feature, but it is not the same:autoinc() will override attempts to substitute a different field value during inserts, and optionally it can be used to increment the field during updates, too.

To use, create aBEFORE INSERT (or optionallyBEFORE INSERT OR UPDATE) trigger using this function. Specify two trigger arguments: the name of the integer column to be modified, and the name of the sequence object that will supply values. (Actually, you can specify any number of pairs of such names, if you'd like to update more than one autoincrementing column.)

There is an example inautoinc.example.

F.10.4. insert_username — Functions for Tracking Who Changed a Table

insert_username() is a trigger that stores the current user's name into a text field. This can be useful for tracking who last modified a particular row within a table.

To use, create aBEFORE INSERT and/orUPDATE trigger using this function. Specify a single trigger argument: the name of the text column to be modified.

There is an example ininsert_username.example.

F.10.5. moddatetime — Functions for Tracking Last Modification Time

moddatetime() is a trigger that stores the current time into atimestamp field. This can be useful for tracking the last modification time of a particular row within a table.

To use, create aBEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of typetimestamp ortimestamp with time zone.

There is an example inmoddatetime.example.

Prev	Up	Next
F.9. citext	Home	F.11. cube

By continuing to browse this website, you agree to the use of cookies. Go toPrivacy Policy.

[8]ページ先頭

©2009-2025 Movatter.jp