diff options
Diffstat (limited to 'doc/src/sgml/ref/create_rule.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_rule.sgml | 306 |
1 files changed, 306 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml new file mode 100644 index 0000000..4871a89 --- /dev/null +++ b/doc/src/sgml/ref/create_rule.sgml @@ -0,0 +1,306 @@ +<!-- +doc/src/sgml/ref/create_rule.sgml +PostgreSQL documentation +--> + +<refentry id="sql-createrule"> + <indexterm zone="sql-createrule"> + <primary>CREATE RULE</primary> + </indexterm> + + <refmeta> + <refentrytitle>CREATE RULE</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>CREATE RULE</refname> + <refpurpose>define a new rewrite rule</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable> + TO <replaceable class="parameter">table_name</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ] + DO [ ALSO | INSTEAD ] { NOTHING | <replaceable class="parameter">command</replaceable> | ( <replaceable class="parameter">command</replaceable> ; <replaceable class="parameter">command</replaceable> ... ) } + +<phrase>where <replaceable class="parameter">event</replaceable> can be one of:</phrase> + + SELECT | INSERT | UPDATE | DELETE +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>CREATE RULE</command> defines a new rule applying to a specified + table or view. + <command>CREATE OR REPLACE RULE</command> will either create a + new rule, or replace an existing rule of the same name for the same + table. + </para> + + <para> + The <productname>PostgreSQL</productname> rule system allows one to + define an alternative 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, an <literal>INSTEAD</literal> + rule can replace a given command by another, or cause a command + not to be executed at all. Rules are used to implement SQL + 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 command 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> + Presently, <literal>ON SELECT</literal> rules can only be attached + to views. Such a rule must be named <literal>"_RETURN"</literal>, + must be an unconditional <literal>INSTEAD</literal> rule, and must have + an action that consists of a single <command>SELECT</command> command. + This command defines the visible contents of the view. (The view + itself is basically a dummy table with no storage.) It's best to + regard such a rule as an implementation detail. While a view can be + redefined via <literal>CREATE OR REPLACE RULE "_RETURN" AS + ...</literal>, it's better style to use <literal>CREATE OR REPLACE + VIEW</literal>. + </para> + + <para> + 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. If you want to support + <command>INSERT RETURNING</command> and so on, then be sure to put a suitable + <literal>RETURNING</literal> clause into each of these rules. + </para> + + <para> + There is a catch if you try to use conditional rules for complex view + updates: there <emphasis>must</emphasis> 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, 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 non-<literal>INSTEAD</literal>; in + the cases where they are applied, they add to the default + <literal>INSTEAD NOTHING</literal> action. (This method does not + currently work to support <literal>RETURNING</literal> queries, however.) + </para> + + <note> + <para> + A view that is simple enough to be automatically updatable (see <xref + linkend="sql-createview"/>) does not require a user-created rule in + order to be updatable. While you can create an explicit rule anyway, + the automatic update transformation will generally outperform an + explicit rule. + </para> + + <para> + Another alternative worth considering is to use <literal>INSTEAD OF</literal> + triggers (see <xref linkend="sql-createtrigger"/>) in place of rules. + </para> + </note> + </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 event is one of <literal>SELECT</literal>, + <literal>INSERT</literal>, <literal>UPDATE</literal>, or + <literal>DELETE</literal>. Note that an + <command>INSERT</command> containing an <literal>ON + CONFLICT</literal> clause cannot be used on tables that have + either <literal>INSERT</literal> or <literal>UPDATE</literal> + rules. Consider using an updatable view instead. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">table_name</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 <acronym>SQL</acronym> conditional expression (returning + <type>boolean</type>). The condition expression cannot refer + to any tables except <literal>NEW</literal> and <literal>OLD</literal>, and + cannot contain aggregate functions. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>INSTEAD</option></term> + <listitem> + <para><literal>INSTEAD</literal> indicates that the commands should be + executed <emphasis>instead of</emphasis> the original command. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>ALSO</option></term> + <listitem> + <para><literal>ALSO</literal> indicates that the commands should be + executed <emphasis>in addition to</emphasis> the original + command. + </para> + + <para> + If neither <literal>ALSO</literal> nor + <literal>INSTEAD</literal> is specified, <literal>ALSO</literal> + is the default. + </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 <command>SELECT</command>, + <command>INSERT</command>, <command>UPDATE</command>, + <command>DELETE</command>, or <command>NOTIFY</command>. + </para> + </listitem> + </varlistentry> + </variablelist> + + <para> + Within <replaceable class="parameter">condition</replaceable> and + <replaceable class="parameter">command</replaceable>, the special + table names <literal>NEW</literal> and <literal>OLD</literal> can + 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> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + You must be the owner of a table to create or change rules for it. + </para> + + <para> + In a rule for <literal>INSERT</literal>, <literal>UPDATE</literal>, or + <literal>DELETE</literal> on a view, you can add a <literal>RETURNING</literal> + clause that emits the view's columns. This clause will be used to compute + the outputs if the rule is triggered by an <command>INSERT RETURNING</command>, + <command>UPDATE RETURNING</command>, or <command>DELETE RETURNING</command> command + respectively. When the rule is triggered by a command without + <literal>RETURNING</literal>, the rule's <literal>RETURNING</literal> clause will be + ignored. The current implementation allows only unconditional + <literal>INSTEAD</literal> rules to contain <literal>RETURNING</literal>; furthermore + there can be at most one <literal>RETURNING</literal> clause among all the rules + for the same event. (This ensures that there is only one candidate + <literal>RETURNING</literal> clause to be used to compute the results.) + <literal>RETURNING</literal> queries on the view will be rejected if + there is no <literal>RETURNING</literal> clause in any available rule. + </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 + <command>SELECT</command> command would cause + <productname>PostgreSQL</productname> to report an error because + of recursive expansion of a rule: + +<programlisting> +CREATE RULE "_RETURN" AS + ON SELECT TO t1 + DO INSTEAD + SELECT * FROM t2; + +CREATE RULE "_RETURN" AS + ON SELECT TO t2 + DO INSTEAD + SELECT * FROM t1; + +SELECT * FROM t1; +</programlisting> + </para> + + <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 ALSO NOTIFY mytable; + +UPDATE mytable SET name = 'foo' WHERE id = 42; +</programlisting> + one <command>NOTIFY</command> event will be sent during the + <command>UPDATE</command>, whether or not there are any rows that + match the condition <literal>id = 42</literal>. This is an + implementation restriction that might be fixed in future releases. + </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>CREATE RULE</command> is a + <productname>PostgreSQL</productname> language extension, as is the + entire query rewrite system. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-alterrule"/></member> + <member><xref linkend="sql-droprule"/></member> + </simplelist> + </refsect1> + +</refentry> |