codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE RULE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createrole.html" title="CREATE ROLE" /><link rel="next" href="sql-createschema.html" title="CREATE SCHEMA" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE RULE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createrole.html" title="CREATE ROLE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createschema.html" title="CREATE SCHEMA">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATERULE"><div class="titlepage"></div><a id="id-1.9.3.79.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE RULE</span></h2><p>CREATE RULE — define a new rewrite rule</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3CREATE [ OR REPLACE ] RULE <em class="replaceable"><code>name</code></em> AS ON <em class="replaceable"><code>event</code></em>4    TO <em class="replaceable"><code>table_name</code></em> [ WHERE <em class="replaceable"><code>condition</code></em> ]5    DO [ ALSO | INSTEAD ] { NOTHING | <em class="replaceable"><code>command</code></em> | ( <em class="replaceable"><code>command</code></em> ; <em class="replaceable"><code>command</code></em> ... ) }6 7<span class="phrase">where <em class="replaceable"><code>event</code></em> can be one of:</span>8 9    SELECT | INSERT | UPDATE | DELETE10</pre></div><div class="refsect1" id="id-1.9.3.79.5"><h2>Description</h2><p>11   <code class="command">CREATE RULE</code> defines a new rule applying to a specified12   table or view.13   <code class="command">CREATE OR REPLACE RULE</code> will either create a14   new rule, or replace an existing rule of the same name for the same15   table.16  </p><p>17   The <span class="productname">PostgreSQL</span> rule system allows one to18   define an alternative action to be performed on insertions, updates,19   or deletions in database tables.  Roughly speaking, a rule causes20   additional commands to be executed when a given command on a given21   table is executed.  Alternatively, an <code class="literal">INSTEAD</code>22   rule can replace a given command by another, or cause a command23   not to be executed at all.  Rules are used to implement SQL24   views as well.  It is important to realize that a rule is really25   a command transformation mechanism, or command macro.  The26   transformation happens before the execution of the command starts.27   If you actually want an operation that fires independently for each28   physical row, you probably want to use a trigger, not a rule.29   More information about the rules system is in <a class="xref" href="rules.html" title="Chapter 41. The Rule System">Chapter 41</a>.30  </p><p>31   Presently, <code class="literal">ON SELECT</code> rules can only be attached32   to views.  Such a rule must be named <code class="literal">"_RETURN"</code>,33   must be an unconditional <code class="literal">INSTEAD</code> rule, and must have34   an action that consists of a single <code class="command">SELECT</code> command.35   This command defines the visible contents of the view.  (The view36   itself is basically a dummy table with no storage.)  It's best to37   regard such a rule as an implementation detail.  While a view can be38   redefined via <code class="literal">CREATE OR REPLACE RULE "_RETURN" AS39   ...</code>, it's better style to use <code class="literal">CREATE OR REPLACE40   VIEW</code>.41  </p><p>42   You can create the illusion of an updatable view by defining43   <code class="literal">ON INSERT</code>, <code class="literal">ON UPDATE</code>, and44   <code class="literal">ON DELETE</code> rules (or any subset of those that's45   sufficient for your purposes) to replace update actions on the view46   with appropriate updates on other tables.  If you want to support47   <code class="command">INSERT RETURNING</code> and so on, then be sure to put a suitable48   <code class="literal">RETURNING</code> clause into each of these rules.49  </p><p>50   There is a catch if you try to use conditional rules for complex view51   updates: there <span class="emphasis"><em>must</em></span> be an unconditional52   <code class="literal">INSTEAD</code> rule for each action you wish to allow53   on the view.  If the rule is conditional, or is not54   <code class="literal">INSTEAD</code>, then the system will still reject55   attempts to perform the update action, because it thinks it might56   end up trying to perform the action on the dummy table of the view57   in some cases.  If you want to handle all the useful cases in58   conditional rules, add an unconditional <code class="literal">DO59   INSTEAD NOTHING</code> rule to ensure that the system60   understands it will never be called on to update the dummy table.61   Then make the conditional rules non-<code class="literal">INSTEAD</code>; in62   the cases where they are applied, they add to the default63   <code class="literal">INSTEAD NOTHING</code> action.  (This method does not64   currently work to support <code class="literal">RETURNING</code> queries, however.)65  </p><div class="note"><h3 class="title">Note</h3><p>66    A view that is simple enough to be automatically updatable (see <a class="xref" href="sql-createview.html" title="CREATE VIEW"><span class="refentrytitle">CREATE VIEW</span></a>) does not require a user-created rule in67    order to be updatable.  While you can create an explicit rule anyway,68    the automatic update transformation will generally outperform an69    explicit rule.70   </p><p>71    Another alternative worth considering is to use <code class="literal">INSTEAD OF</code>72    triggers (see <a class="xref" href="sql-createtrigger.html" title="CREATE TRIGGER"><span class="refentrytitle">CREATE TRIGGER</span></a>) in place of rules.73   </p></div></div><div class="refsect1" id="id-1.9.3.79.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>74      The name of a rule to create.  This must be distinct from the75      name of any other rule for the same table.  Multiple rules on76      the same table and same event type are applied in alphabetical77      name order.78     </p></dd><dt><span class="term"><em class="replaceable"><code>event</code></em></span></dt><dd><p>79      The event is one of <code class="literal">SELECT</code>,80      <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, or81      <code class="literal">DELETE</code>.  Note that an82      <code class="command">INSERT</code> containing an <code class="literal">ON83      CONFLICT</code> clause cannot be used on tables that have84      either <code class="literal">INSERT</code> or <code class="literal">UPDATE</code>85      rules.  Consider using an updatable view instead.86     </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>87      The name (optionally schema-qualified) of the table or view the88      rule applies to.89     </p></dd><dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt><dd><p>90      Any <acronym class="acronym">SQL</acronym> conditional expression (returning91      <code class="type">boolean</code>).  The condition expression cannot refer92      to any tables except <code class="literal">NEW</code> and <code class="literal">OLD</code>, and93      cannot contain aggregate functions.94     </p></dd><dt><span class="term"><code class="option">INSTEAD</code></span></dt><dd><p><code class="literal">INSTEAD</code> indicates that the commands should be95      executed <span class="emphasis"><em>instead of</em></span> the original command.96     </p></dd><dt><span class="term"><code class="option">ALSO</code></span></dt><dd><p><code class="literal">ALSO</code> indicates that the commands should be97      executed <span class="emphasis"><em>in addition to</em></span> the original98      command.99     </p><p>100      If neither <code class="literal">ALSO</code> nor101      <code class="literal">INSTEAD</code> is specified, <code class="literal">ALSO</code>102      is the default.103     </p></dd><dt><span class="term"><em class="replaceable"><code>command</code></em></span></dt><dd><p>104      The command or commands that make up the rule action.  Valid105      commands are <code class="command">SELECT</code>,106      <code class="command">INSERT</code>, <code class="command">UPDATE</code>,107      <code class="command">DELETE</code>, or <code class="command">NOTIFY</code>.108     </p></dd></dl></div><p>109   Within <em class="replaceable"><code>condition</code></em> and110   <em class="replaceable"><code>command</code></em>, the special111   table names <code class="literal">NEW</code> and <code class="literal">OLD</code> can112   be used to refer to values in the referenced table.113   <code class="literal">NEW</code> is valid in <code class="literal">ON INSERT</code> and114   <code class="literal">ON UPDATE</code> rules to refer to the new row being115   inserted or updated.  <code class="literal">OLD</code> is valid in116   <code class="literal">ON UPDATE</code> and <code class="literal">ON DELETE</code> rules117   to refer to the existing row being updated or deleted.118  </p></div><div class="refsect1" id="id-1.9.3.79.7"><h2>Notes</h2><p>119   You must be the owner of a table to create or change rules for it.120  </p><p>121   In a rule for <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, or122   <code class="literal">DELETE</code> on a view, you can add a <code class="literal">RETURNING</code>123   clause that emits the view's columns.  This clause will be used to compute124   the outputs if the rule is triggered by an <code class="command">INSERT RETURNING</code>,125   <code class="command">UPDATE RETURNING</code>, or <code class="command">DELETE RETURNING</code> command126   respectively.  When the rule is triggered by a command without127   <code class="literal">RETURNING</code>, the rule's <code class="literal">RETURNING</code> clause will be128   ignored.  The current implementation allows only unconditional129   <code class="literal">INSTEAD</code> rules to contain <code class="literal">RETURNING</code>; furthermore130   there can be at most one <code class="literal">RETURNING</code> clause among all the rules131   for the same event.  (This ensures that there is only one candidate132   <code class="literal">RETURNING</code> clause to be used to compute the results.)133   <code class="literal">RETURNING</code> queries on the view will be rejected if134   there is no <code class="literal">RETURNING</code> clause in any available rule.135  </p><p>136   It is very important to take care to avoid circular rules.  For137   example, though each of the following two rule definitions are138   accepted by <span class="productname">PostgreSQL</span>, the139   <code class="command">SELECT</code> command would cause140   <span class="productname">PostgreSQL</span> to report an error because141   of recursive expansion of a rule:142 143</p><pre class="programlisting">144CREATE RULE "_RETURN" AS145    ON SELECT TO t1146    DO INSTEAD147        SELECT * FROM t2;148 149CREATE RULE "_RETURN" AS150    ON SELECT TO t2151    DO INSTEAD152        SELECT * FROM t1;153 154SELECT * FROM t1;155</pre><p>156  </p><p>157   Presently, if a rule action contains a <code class="command">NOTIFY</code>158   command, the <code class="command">NOTIFY</code> command will be executed159   unconditionally, that is, the <code class="command">NOTIFY</code> will be160   issued even if there are not any rows that the rule should apply161   to.  For example, in:162</p><pre class="programlisting">163CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;164 165UPDATE mytable SET name = 'foo' WHERE id = 42;166</pre><p>167   one <code class="command">NOTIFY</code> event will be sent during the168   <code class="command">UPDATE</code>, whether or not there are any rows that169   match the condition <code class="literal">id = 42</code>.  This is an170   implementation restriction that might be fixed in future releases.171  </p></div><div class="refsect1" id="id-1.9.3.79.8"><h2>Compatibility</h2><p>172   <code class="command">CREATE RULE</code> is a173   <span class="productname">PostgreSQL</span> language extension, as is the174   entire query rewrite system.175  </p></div><div class="refsect1" id="id-1.9.3.79.9"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alterrule.html" title="ALTER RULE"><span class="refentrytitle">ALTER RULE</span></a>, <a class="xref" href="sql-droprule.html" title="DROP RULE"><span class="refentrytitle">DROP RULE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createrole.html" title="CREATE ROLE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createschema.html" title="CREATE SCHEMA">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE ROLE </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE SCHEMA</td></tr></table></div></body></html>