diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/create_rule.sgml | 40 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_trigger.sgml | 40 | ||||
| -rw-r--r-- | doc/src/sgml/trigger.sgml | 101 |
3 files changed, 115 insertions, 66 deletions
diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index c9cc209843a..1c5786a0c91 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.33 2002/03/22 19:20:39 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.34 2002/04/19 16:36:08 tgl Exp $ PostgreSQL documentation --> @@ -22,7 +22,7 @@ PostgreSQL documentation </refsynopsisdivinfo> <synopsis> CREATE RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable> - TO <replaceable class="parameter">object</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ] + TO <replaceable class="parameter">table</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ] DO [ INSTEAD ] <replaceable class="parameter">action</replaceable> where <replaceable class="PARAMETER">action</replaceable> can be: @@ -48,7 +48,8 @@ NOTHING <term><replaceable class="parameter">name</replaceable></term> <listitem> <para> - The name of a rule to create. + The name of a rule to create. This must be distinct from the name + of any other rule for the same table. </para> </listitem> </varlistentry> @@ -63,14 +64,11 @@ NOTHING </listitem> </varlistentry> <varlistentry> - <term><replaceable class="parameter">object</replaceable></term> + <term><replaceable class="parameter">table</replaceable></term> <listitem> <para> - Object is either <replaceable class="parameter">table</replaceable> - or <replaceable class="parameter">table</replaceable>.<replaceable - class="parameter">column</replaceable>. (Currently, only the - <replaceable class="parameter">table</replaceable> form is - actually implemented.) + The name (optionally schema-qualified) of the table or view the rule + applies to. </para> </listitem> </varlistentry> @@ -103,8 +101,7 @@ NOTHING Within the <replaceable class="parameter">condition</replaceable> and <replaceable class="PARAMETER">action</replaceable>, the special table names <literal>new</literal> and <literal>old</literal> may be - used to refer to values in the referenced table (the - <replaceable class="parameter">object</replaceable>). + used to refer to values in the referenced table. <literal>new</literal> is valid in ON INSERT and ON UPDATE rules to refer to the new row being inserted or updated. <literal>old</literal> is valid in ON UPDATE and ON DELETE @@ -159,7 +156,7 @@ CREATE accessed, inserted, updated, or deleted, there is an old instance (for selects, updates and deletes) and a new instance (for inserts and updates). All the rules for the given event type and the given target - object (table) are examined, in an unspecified order. If the + table are examined successively (in order by name). If the <replaceable class="parameter">condition</replaceable> specified in the WHERE clause (if any) is true, the <replaceable class="parameter">action</replaceable> part of the rule is @@ -178,8 +175,7 @@ CREATE The <replaceable class="parameter">action</replaceable> part of the rule can consist of one or more queries. To write multiple queries, surround them with parentheses. Such queries will be performed in the - specified order (whereas there are no guarantees about the execution - order of multiple rules for an object). The <replaceable + specified order. The <replaceable class="parameter">action</replaceable> can also be NOTHING indicating no action. Thus, a DO INSTEAD NOTHING rule suppresses the original query from executing (when its condition is true); a DO NOTHING rule @@ -191,6 +187,20 @@ CREATE executes with the same command and transaction identifier as the user command that caused activation. </para> + + <para> + It is important to realize that a rule is really a query transformation + mechanism, or query macro. The entire query is processed to convert it + into a series of queries that include the rule actions. This occurs + before evaluation of the query starts. So, conditional rules are + handled by adding the rule condition to the WHERE clause of the action(s) + derived from the rule. The above description of a rule as an operation + that executes for each row is thus somewhat misleading. If you actually + want an operation that fires independently for each physical row, you + probably want to use a trigger not a rule. Rules are most useful for + situations that call for transforming entire queries independently of + the specific data being handled. + </para> <refsect2 id="R2-SQL-CREATERULE-3"> <refsect2info> @@ -202,7 +212,7 @@ CREATE <para> Presently, ON SELECT rules must be unconditional INSTEAD rules and must have actions that consist of a single SELECT query. Thus, an ON SELECT - rule effectively turns the object table into a view, whose visible + rule effectively turns the table into a view, whose visible contents are the rows returned by the rule's SELECT query rather than whatever had been stored in the table (if anything). It is considered better style to write a CREATE VIEW command than to create a real table diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index f3e82766380..1d24be9a2b4 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.22 2002/01/20 22:19:56 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.23 2002/04/19 16:36:08 tgl Exp $ PostgreSQL documentation --> @@ -44,23 +44,24 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE <term><replaceable class="parameter">name</replaceable></term> <listitem> <para> - The name to give the new trigger. + The name to give the new trigger. This must be distinct from the name + of any other trigger for the same table. </para> </listitem> </varlistentry> <varlistentry> - <term><replaceable class="parameter">table</replaceable></term> + <term><replaceable class="parameter">event</replaceable></term> <listitem> <para> - The name of an existing table. + One of INSERT, DELETE or UPDATE. </para> </listitem> </varlistentry> <varlistentry> - <term><replaceable class="parameter">event</replaceable></term> + <term><replaceable class="parameter">table</replaceable></term> <listitem> <para> - One of INSERT, DELETE or UPDATE. + The name (optionally schema-qualified) of the table the trigger is for. </para> </listitem> </varlistentry> @@ -68,7 +69,20 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE <term><replaceable class="parameter">func</replaceable></term> <listitem> <para> - A user-supplied function. + A user-supplied function that is declared as taking no arguments + and returning type <literal>opaque</>. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><replaceable class="parameter">arguments</replaceable></term> + <listitem> + <para> + An optional comma-separated list of arguments to be provided to the + function when the trigger is executed, along with the standard trigger + data such as old and new tuple contents. The arguments are literal + string constants. Simple names and numeric constants may be written + here too, but they will all be converted to strings. </para> </listitem> </varlistentry> @@ -130,6 +144,12 @@ CREATE after the event, all changes, including the last insertion, update, or deletion, are <quote>visible</quote> to the trigger. </para> + + <para> + If multiple triggers of the same kind are defined for the same event, + they will be fired in alphabetical order by name. + </para> + <para> <command>SELECT</command> does not modify any rows so you can not create <command>SELECT</command> triggers. Rules and views are more @@ -262,6 +282,12 @@ CREATE TABLE distributors ( </listitem> </itemizedlist> </para> + + <para> + SQL99 specifies that multiple triggers should be fired in + time-of-creation order. <productname>PostgreSQL</productname> + uses name order, which was judged more convenient to work with. + </para> </listitem> </varlistentry> </variablelist> diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 8dd9815e0c6..5456f4d0cdb 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.22 2002/04/01 22:36:06 tgl Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.23 2002/04/19 16:36:08 tgl Exp $ --> <chapter id="triggers"> @@ -14,8 +14,8 @@ $Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.22 2002/04/01 22:36:06 tgl AFTER on INSERT, DELETE or UPDATE of a tuple as a trigger event. </para> - <sect1 id="trigger-create"> - <title>Trigger Creation</title> + <sect1 id="trigger-definition"> + <title>Trigger Definition</title> <para> If a trigger event occurs, the trigger manager (called by the Executor) @@ -24,13 +24,17 @@ $Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.22 2002/04/01 22:36:06 tgl </para> <para> - The trigger function must be defined before the trigger is created as a - function taking no arguments and returning opaque. If the function is - written in C, it must use the <quote>version 1</> function manager interface. + The trigger function must be defined before the trigger itself can be + created. The trigger function must be declared as a + function taking no arguments and returning type <literal>opaque</>. + (The trigger function receives its input through a TriggerData + structure, not in the form of ordinary function arguments.) + If the function is written in C, it must use the <quote>version 1</> + function manager interface. </para> <para> - The syntax for creating triggers is as follows: + The syntax for creating triggers is: <programlisting> CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | DELETE | UPDATE [ OR ... ] ] @@ -48,9 +52,9 @@ CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | </term> <listitem> <para> - The name of the trigger is - used if you ever have to delete the trigger. - It is used as an argument to the <command>DROP TRIGGER</command> command. + The trigger must have a name distinct from all other triggers on + the same table. The name is needed + if you ever have to delete the trigger. </para> </listitem> </varlistentry> @@ -72,7 +76,7 @@ CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | <term>UPDATE</term> <listitem> <para> - The next element of the command determines on what event(s) will trigger + The next element of the command determines what event(s) will trigger the function. Multiple events can be specified separated by OR. </para> </listitem> @@ -82,7 +86,7 @@ CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | <term><replaceable>relation</replaceable></term> <listitem> <para> - The relation name determines which table the event applies to. + The relation name indicates which table the event applies to. </para> </listitem> </varlistentry> @@ -94,6 +98,7 @@ CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | <para> The FOR EACH clause determines whether the trigger is fired for each affected row or before (or after) the entire statement has completed. + Currently only the ROW case is supported. </para> </listitem> </varlistentry> @@ -102,7 +107,7 @@ CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | <term><replaceable>procedure</replaceable></term> <listitem> <para> - The procedure name is the function called. + The procedure name is the function to be called. </para> </listitem> </varlistentry> @@ -112,23 +117,23 @@ CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | <listitem> <para> The arguments passed to the function in the TriggerData structure. - The purpose of passing arguments to the function is to allow different - triggers with similar requirements to call the same function. + This is either empty or a list of one or more simple literal + constants (which will be passed to the function as strings). </para> <para> - Also, <replaceable>procedure</replaceable> - may be used for triggering different relations (these - functions are named as <firstterm>general trigger functions</>). - </para> - - <para> - As example of using both features above, there could be a general - function that takes as its arguments two field names and puts the current - user in one and the current timestamp in the other. This allows triggers to - be written on INSERT events to automatically track creation of records in a - transaction table for example. It could also be used as a <quote>last updated</> - function if used in an UPDATE event. + The purpose of including arguments in the trigger definition + is to allow different + triggers with similar requirements to call the same function. + As an example, there could be a generalized trigger + function that takes as its arguments two field names and puts the + current user in one and the current timestamp in the other. + Properly written, this trigger function would be independent of + the specific table it is triggering on. So the same function + could be used for INSERT events on any table with suitable fields, + to automatically track creation of records in a transaction table for + example. It could also be used to track last-update events if + defined as an UPDATE trigger. </para> </listitem> </varlistentry> @@ -136,8 +141,8 @@ CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | </para> <para> - Trigger functions return HeapTuple to the calling Executor. This - is ignored for triggers fired after an INSERT, DELETE or UPDATE operation + Trigger functions return a HeapTuple to the calling Executor. The return + value is ignored for triggers fired AFTER an operation, but it allows BEFORE triggers to: <itemizedlist> @@ -150,33 +155,41 @@ CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | <listitem> <para> - Return a pointer to another tuple (INSERT and UPDATE only) which will - be inserted (as the new version of the updated tuple if UPDATE) instead - of original tuple. + For INSERT and UPDATE triggers only, the returned tuple becomes the + tuple which will be inserted or will replace the tuple being updated. + This allows the trigger function to modify the row being inserted or + updated. </para> </listitem> </itemizedlist> + + A BEFORE trigger that does not intend to cause either of these behaviors + must be careful to return the same NEW tuple it is passed. </para> <para> Note that there is no initialization performed by the CREATE TRIGGER - handler. This will be changed in the future. Also, if more than one trigger - is defined for the same event on the same relation, the order of trigger - firing is unpredictable. This may be changed in the future. + handler. This may be changed in the future. </para> <para> - If a trigger function executes SQL-queries (using SPI) then these queries - may fire triggers again. This is known as cascading triggers. There is no - explicit limitation on the number of cascade levels. + If more than one trigger + is defined for the same event on the same relation, the triggers will + be fired in alphabetical order by name. In the case of BEFORE triggers, + the possibly-modified tuple returned by each trigger becomes the input + to the next trigger. If any BEFORE trigger returns NULL, the operation + is abandoned and subsequent triggers are not fired. </para> <para> - If a trigger is fired by INSERT and inserts a new tuple in the same - relation then this trigger will be fired again. Currently, there is nothing - provided for synchronization (etc) of these cases but this may change. At - the moment, there is function funny_dup17() in the regress tests which uses - some techniques to stop recursion (cascading) on itself... + If a trigger function executes SQL-queries (using SPI) then these queries + may fire triggers again. This is known as cascading triggers. There is no + direct limitation on the number of cascade levels. It is possible for + cascades to cause recursive invocation of the same trigger --- for + example, an INSERT trigger might execute a query that inserts an + additional tuple into the same table, causing the INSERT trigger to be + fired again. It is the trigger programmer's + responsibility to avoid infinite recursion in such scenarios. </para> </sect1> @@ -326,7 +339,7 @@ typedef struct TriggerData <para> is a pointer to structure describing the triggered relation. Look at src/include/utils/rel.h for details about this structure. The most - interest things are tg_relation->rd_att (descriptor of the relation + interesting things are tg_relation->rd_att (descriptor of the relation tuples) and tg_relation->rd_rel->relname (relation's name. This is not char*, but NameData. Use SPI_getrelname(tg_relation) to get char* if you need a copy of name). |