diff options
Diffstat (limited to 'doc/src/sgml/ref/create_rule.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_rule.sgml | 452 |
1 files changed, 178 insertions, 274 deletions
diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index 6185f644825..ff7f2bfb134 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.38 2002/11/21 23:34:43 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.39 2003/04/22 10:08:08 petere Exp $ PostgreSQL documentation --> @@ -8,138 +8,22 @@ PostgreSQL documentation <refentrytitle id="sql-createrule-title">CREATE RULE</refentrytitle> <refmiscinfo>SQL - Language Statements</refmiscinfo> </refmeta> + <refnamediv> - <refname> - CREATE RULE - </refname> - <refpurpose> - define a new rewrite rule - </refpurpose> + <refname>CREATE RULE</refname> + <refpurpose>define a new rewrite rule</refpurpose> </refnamediv> + <refsynopsisdiv> - <refsynopsisdivinfo> - <date>2001-01-05</date> - </refsynopsisdivinfo> - <synopsis> +<synopsis> CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</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: - -NOTHING -| <replaceable class="parameter">query</replaceable> -| ( <replaceable class="parameter">query</replaceable> ; <replaceable class="parameter">query</replaceable> ... ) - </synopsis> - - <refsect2 id="R2-SQL-CREATERULE-1"> - <refsect2info> - <date>2001-01-05</date> - </refsect2info> - <title> - Inputs - </title> - <para> - - <variablelist> - <varlistentry> - <term><replaceable class="parameter">name</replaceable></term> - <listitem> - <para> - 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> - <varlistentry> - <term><replaceable class="parameter">event</replaceable></term> - <listitem> - <para> - Event is one of <literal>SELECT</literal>, - <literal>UPDATE</literal>, <literal>DELETE</literal> - or <literal>INSERT</literal>. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><replaceable class="parameter">table</replaceable></term> - <listitem> - <para> - The name (optionally schema-qualified) of the table or view the rule - applies to. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><replaceable class="parameter">condition</replaceable></term> - <listitem> - <para> - Any SQL conditional expression (returning <type>boolean</type>). - The condition expression may not - refer to any tables except <literal>new</literal> and - <literal>old</literal>, and may not contain aggregate functions. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><replaceable class="parameter">query</replaceable></term> - <listitem> - <para> - The query or queries making up the - <replaceable class="PARAMETER">action</replaceable> - can be any SQL <literal>SELECT</literal>, <literal>INSERT</literal>, - <literal>UPDATE</literal>, <literal>DELETE</literal>, or - <literal>NOTIFY</literal> statement. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - - <para> - 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. - <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 - rules to refer to the existing row being updated or deleted. - </para> - </refsect2> - - <refsect2 id="R2-SQL-CREATERULE-2"> - <refsect2info> - <date>1998-09-11</date> - </refsect2info> - <title> - Outputs - </title> - <para> - - <variablelist> - <varlistentry> - <term><computeroutput> -CREATE RULE - </computeroutput></term> - <listitem> - <para> - Message returned if the rule is successfully created. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect2> + DO [ INSTEAD ] { NOTHING | <replaceable class="parameter">command</replaceable> | ( <replaceable class="parameter">command</replaceable> ; <replaceable class="parameter">command</replaceable> ... ) } +</synopsis> </refsynopsisdiv> - <refsect1 id="R1-SQL-CREATERULE-1"> - <refsect1info> - <date>1998-09-11</date> - </refsect1info> - <title> - Description - </title> + <refsect1> + <title>Description</title> <para> <command>CREATE RULE</command> defines a new rule applying to a specified @@ -150,186 +34,206 @@ CREATE RULE </para> <para> - The <productname>PostgreSQL</productname> - <firstterm>rule system</firstterm> allows one to define an - alternate action to be performed on inserts, updates, or deletions - from database tables. Rules are used to - implement table views as well. + The <productname>PostgreSQL</productname> rule system allows one to + define an alternate action to be performed on insertions, updates, + or deletions in database tables. Roughly speaking, a rule causes + additional commands to be executed when a given command on a given + table is executed. Alternatively, a rule can replace a given + command by another, or cause a command not to be executed at all. + Rules are used to implement table views as well. It is important + to realize that a rule is really a command transformation + mechanism, or command macro. The transformation happens before the + execution of the commands starts. If you actually want an + operation that fires independently for each physical row, you + probably want to use a trigger, not a rule. More information about + the rules system is in <xref linkend="rules">. </para> - + <para> - The semantics of a rule is that at the time an individual instance (row) - is - 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 - 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 - executed. The <replaceable class="parameter">action</replaceable> is - done instead of the original query if INSTEAD is specified; otherwise - it is done after the original query in the case of ON INSERT, or before - the original query in the case of ON UPDATE or ON DELETE. - Within both the <replaceable class="parameter">condition</replaceable> - and <replaceable class="parameter">action</replaceable>, values from - fields in the old instance and/or the new instance are substituted for - <literal>old.</literal><replaceable class="parameter">attribute-name</replaceable> - and <literal>new.</literal><replaceable class="parameter">attribute-name</replaceable>. + Presently, <literal>ON SELECT</literal> rules must be unconditional + <literal>INSTEAD</literal> rules and must have actions that consist + of a single <command>SELECT</command> command. Thus, an + <literal>ON SELECT</literal> rule effectively turns the table into + a view, whose visible contents are the rows returned by the rule's + <command>SELECT</command> command rather than whatever had been + stored in the table (if anything). It is considered better style + to write a <command>CREATE VIEW</command> command than to create a + real table and define an <literal>ON SELECT</literal> rule for it. </para> <para> - 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. 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 - is useless. + You can create the illusion of an updatable view by defining + <literal>ON INSERT</literal>, <literal>ON UPDATE</literal>, and + <literal>ON DELETE</literal> rules (or any subset of those that's + sufficient for your purposes) to replace update actions on the view + with appropriate updates on other tables. </para> <para> - The <replaceable class="parameter">action</replaceable> part of the rule - executes with the same command and transaction identifier as the user - command that caused activation. + There is a catch if you try to use conditional rules for view + updates: there <emphasis>must</> be an unconditional + <literal>INSTEAD</literal> rule for each action you wish to allow + on the view. If the rule is conditional, or is not + <literal>INSTEAD</literal>, then the system will still reject + attempts to perform the update action, because it thinks it might + end up trying to perform the action on the dummy table of the view + in some cases. If you want to handle all the useful cases in + conditional rules, you can; just add an unconditional <literal>DO + INSTEAD NOTHING</literal> rule to ensure that the system + understands it will never be called on to update the dummy table. + Then make the conditional rules not <literal>INSTEAD</literal>; in + the cases where they are applied, they add to the default + <literal>INSTEAD NOTHING</literal> action. </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="parameter">name</replaceable></term> + <listitem> + <para> + The name of a rule to create. This must be distinct from the + name of any other rule for the same table. Multiple rules on + the same table and same event type are applied in alphabetical + name order. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">event</replaceable></term> + <listitem> + <para> + The even is one of <literal>SELECT</literal>, + <literal>INSERT</literal>, <literal>UPDATE</literal>, or + <literal>DELETE</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">table</replaceable></term> + <listitem> + <para> + The name (optionally schema-qualified) of the table or view the + rule applies to. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">condition</replaceable></term> + <listitem> + <para> + Any SQL conditional expression (returning <type>boolean</type>). + The condition expression may not refer to any tables except + <literal>NEW</literal> and <literal>OLD</literal>, and may not + contain aggregate functions. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">command</replaceable></term> + <listitem> + <para> + The command or commands that make up the rule action. Valid + commands are <literal>SELECT</literal>, + <literal>INSERT</literal>, <literal>UPDATE</literal>, + <literal>DELETE</literal>, or <literal>NOTIFY</literal>. + </para> + </listitem> + </varlistentry> + </variablelist> <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. + Within <replaceable class="parameter">condition</replaceable> and + <replaceable class="parameter">command</replaceable>, the special + table names <literal>NEW</literal> and <literal>OLD</literal> may + be used to refer to values in the referenced table. + <literal>NEW</literal> is valid in <literal>ON INSERT</literal> and + <literal>ON UPDATE</literal> rules to refer to the new row being + inserted or updated. <literal>OLD</literal> is valid in + <literal>ON UPDATE</literal> and <literal>ON DELETE</literal> rules + to refer to the existing row being updated or deleted. </para> - - <refsect2 id="R2-SQL-CREATERULE-3"> - <refsect2info> - <date>2001-11-06</date> - </refsect2info> - <title> - Rules and Views - </title> - <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 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 - and define an ON SELECT rule for it. - </para> + </refsect1> - <para> - <xref linkend="sql-createview" endterm="sql-createview-title"> creates a dummy table (with no underlying - storage) and associates an ON SELECT rule with it. The system will not - allow updates to the view, since it knows there is no real table there. - You can create the - illusion of an updatable view by defining ON INSERT, ON UPDATE, and - ON DELETE rules (or any subset of those that's sufficient - for your purposes) to replace update actions on the view with - appropriate updates on other tables. - </para> + <refsect1> + <title>Diagnostics</title> + + <variablelist> + <varlistentry> + <term><computeroutput>CREATE RULE</computeroutput></term> + <listitem> + <para> + Message returned if the rule was successfully created. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> - <para> - There is a catch if you try to use conditional - rules for view updates: there <emphasis>must</> be an unconditional - INSTEAD rule for each action you wish to allow on the view. If the - rule is conditional, or is not INSTEAD, then the system will still reject - attempts to perform the update action, because it thinks it might end up - trying to perform the action on the dummy table in some cases. - If you want to - handle all the useful cases in conditional rules, you can; just add an - unconditional DO INSTEAD NOTHING rule to ensure that the system - understands it will never be called on to update the dummy table. Then - make the conditional rules non-INSTEAD; in the cases where they fire, - they add to the default INSTEAD NOTHING action. - </para> - </refsect2> + <refsect1> + <title>Notes</title> - <refsect2 id="R2-SQL-CREATERULE-4"> - <refsect2info> - <date>2001-01-05</date> - </refsect2info> - <title> - Notes - </title> - <para> - You must have rule definition access to a table in order - to define a rule on it. Use <command>GRANT</command> - and <command>REVOKE</command> to change permissions. - </para> + <para> + You must have the privilege <literal>RULE</literal> on a table to + be allowed to define a rule on it. + </para> - <para> - It is very important to take care to avoid circular rules. - For example, though each - of the following two rule definitions are accepted by - <productname>PostgreSQL</productname>, the - select command will cause <productname>PostgreSQL</productname> to - report an error because the query cycled too many times: + <para> + It is very important to take care to avoid circular rules. For + example, though each of the following two rule definitions are + accepted by <productname>PostgreSQL</productname>, the + <command>SELECT</command> command would cause + <productname>PostgreSQL</productname> to report an error because + the query cycled too many times: <programlisting> CREATE RULE "_RETURN" AS - ON SELECT TO emp + ON SELECT TO t1 DO INSTEAD - SELECT * FROM toyemp; + SELECT * FROM t2; CREATE RULE "_RETURN" AS - ON SELECT TO toyemp + ON SELECT TO t2 DO INSTEAD - SELECT * FROM emp; -</programlisting> - - This attempt to select from <literal>EMP</literal> will cause - <productname>PostgreSQL</productname> to issue an error - because the queries cycled too many times: + SELECT * FROM t1; - <programlisting> -SELECT * FROM emp; +SELECT * FROM t1; </programlisting> - </para> + </para> - <para> - Presently, if a rule contains a NOTIFY query, the NOTIFY will be executed - unconditionally --- that is, the NOTIFY will be issued even if there are - not any rows that the rule should apply to. For example, in - <programlisting> + <para> + Presently, if a rule action contains a <command>NOTIFY</command> + command, the <command>NOTIFY</command> command will be executed + unconditionally, that is, the <command>NOTIFY</command> will be + issued even if there are not any rows that the rule should apply + to. For example, in +<programlisting> CREATE RULE notify_me AS ON UPDATE TO mytable DO NOTIFY mytable; UPDATE mytable SET name = 'foo' WHERE id = 42; - </programlisting> - one NOTIFY event will be sent during the UPDATE, whether or not there - are any rows with id = 42. This is an implementation restriction that - may be fixed in future releases. - </para> - </refsect2> +</programlisting> + one <command>NOTIFY</command> event will be sent during the + <command>UPDATE</command>, whether or not there are any rows with + <literal>id = 42</literal>. This is an implementation restriction + that may be fixed in future releases. + </para> </refsect1> - <refsect1 id="R1-SQL-CREATERULE-4"> - <title> - Compatibility - </title> + <refsect1> + <title>Compatibility</title> - <refsect2 id="R2-SQL-CREATERULE-5"> - <refsect2info> - <date>1998-09-11</date> - </refsect2info> - <title> - SQL92 - </title> - - <para> - <command>CREATE RULE</command> is a <productname>PostgreSQL</productname> - language extension. - There is no <command>CREATE RULE</command> statement in <acronym>SQL92</acronym>. - </para> - </refsect2> + <para> + <command>CREATE RULE</command> is a + <productname>PostgreSQL</productname> language extension, as is the + entire rules system. + </para> </refsect1> </refentry> |