diff options
Diffstat (limited to 'doc/src/sgml/ref/create_rule.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_rule.sgml | 344 |
1 files changed, 0 insertions, 344 deletions
diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml deleted file mode 100644 index 86046359b4a..00000000000 --- a/doc/src/sgml/ref/create_rule.sgml +++ /dev/null @@ -1,344 +0,0 @@ -<!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.36 2002/05/18 15:44:47 petere Exp $ -PostgreSQL documentation ---> - -<refentry id="SQL-CREATERULE"> - <refmeta> - <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> - </refnamediv> - <refsynopsisdiv> - <refsynopsisdivinfo> - <date>2001-01-05</date> - </refsynopsisdivinfo> - <synopsis> -CREATE 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>. - </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> - </refsynopsisdiv> - - <refsect1 id="R1-SQL-CREATERULE-1"> - <refsect1info> - <date>1998-09-11</date> - </refsect1info> - <title> - Description - </title> - - <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. - </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>. - </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. - </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. - </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> - <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> - - <para> - <xref linkend="sql-createview"> 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> - - <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> - - <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> - 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: - -<programlisting> -CREATE RULE "_RETURN" AS - ON SELECT TO emp - DO INSTEAD - SELECT * FROM toyemp; - -CREATE RULE "_RETURN" AS - ON SELECT TO toyemp - 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: - - <programlisting> -SELECT * FROM emp; -</programlisting> - </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> -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> - </refsect1> - - <refsect1 id="R1-SQL-CREATERULE-4"> - <title> - Compatibility - </title> - - <refsect2 id="R2-SQL-CREATERULE-5"> - <refsect2info> - <date>1998-09-11</date> - </refsect2info> - <title> - SQL92 - </title> - - <para> - <command>CREATE RULE</command> statement is a <productname>PostgreSQL</productname> - language extension. - There is no <command>CREATE RULE</command> statement in <acronym>SQL92</acronym>. - </para> - </refsect2> - </refsect1> -</refentry> - -<!-- Keep this comment at the end of the file -Local variables: -mode: sgml -sgml-omittag:nil -sgml-shorttag:t -sgml-minimize-attributes:nil -sgml-always-quote-attributes:t -sgml-indent-step:1 -sgml-indent-data:t -sgml-parent-document:nil -sgml-default-dtd-file:"../reference.ced" -sgml-exposed-tags:nil -sgml-local-catalogs:"/usr/lib/sgml/catalog" -sgml-local-ecat-files:nil -End: ---> |