diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createrule.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createrule.html | 176 |
1 files changed, 176 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createrule.html b/doc/src/sgml/html/sql-createrule.html new file mode 100644 index 0000000..e90a8f9 --- /dev/null +++ b/doc/src/sgml/html/sql-createrule.html @@ -0,0 +1,176 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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 15.5 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"> +CREATE [ OR REPLACE ] RULE <em class="replaceable"><code>name</code></em> AS ON <em class="replaceable"><code>event</code></em> + TO <em class="replaceable"><code>table_name</code></em> [ WHERE <em class="replaceable"><code>condition</code></em> ] + 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> ... ) } + +<span class="phrase">where <em class="replaceable"><code>event</code></em> can be one of:</span> + + SELECT | INSERT | UPDATE | DELETE +</pre></div><div class="refsect1" id="id-1.9.3.79.5"><h2>Description</h2><p> + <code class="command">CREATE RULE</code> defines a new rule applying to a specified + table or view. + <code class="command">CREATE OR REPLACE RULE</code> will either create a + new rule, or replace an existing rule of the same name for the same + table. + </p><p> + The <span class="productname">PostgreSQL</span> 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 <code class="literal">INSTEAD</code> + 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 <a class="xref" href="rules.html" title="Chapter 41. The Rule System">Chapter 41</a>. + </p><p> + Presently, <code class="literal">ON SELECT</code> rules can only be attached + to views. (Attaching one to a table converts the table into a view.) + Such a rule must be named <code class="literal">"_RETURN"</code>, + must be an unconditional <code class="literal">INSTEAD</code> rule, and must have + an action that consists of a single <code class="command">SELECT</code> 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 <code class="literal">CREATE OR REPLACE RULE "_RETURN" AS + ...</code>, it's better style to use <code class="literal">CREATE OR REPLACE + VIEW</code>. + </p><p> + You can create the illusion of an updatable view by defining + <code class="literal">ON INSERT</code>, <code class="literal">ON UPDATE</code>, and + <code class="literal">ON DELETE</code> 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 + <code class="command">INSERT RETURNING</code> and so on, then be sure to put a suitable + <code class="literal">RETURNING</code> clause into each of these rules. + </p><p> + There is a catch if you try to use conditional rules for complex view + updates: there <span class="emphasis"><em>must</em></span> be an unconditional + <code class="literal">INSTEAD</code> rule for each action you wish to allow + on the view. If the rule is conditional, or is not + <code class="literal">INSTEAD</code>, 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 <code class="literal">DO + INSTEAD NOTHING</code> rule to ensure that the system + understands it will never be called on to update the dummy table. + Then make the conditional rules non-<code class="literal">INSTEAD</code>; in + the cases where they are applied, they add to the default + <code class="literal">INSTEAD NOTHING</code> action. (This method does not + currently work to support <code class="literal">RETURNING</code> queries, however.) + </p><div class="note"><h3 class="title">Note</h3><p> + 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 in + order to be updatable. While you can create an explicit rule anyway, + the automatic update transformation will generally outperform an + explicit rule. + </p><p> + Another alternative worth considering is to use <code class="literal">INSTEAD OF</code> + triggers (see <a class="xref" href="sql-createtrigger.html" title="CREATE TRIGGER"><span class="refentrytitle">CREATE TRIGGER</span></a>) in place of rules. + </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> + 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. + </p></dd><dt><span class="term"><em class="replaceable"><code>event</code></em></span></dt><dd><p> + The event is one of <code class="literal">SELECT</code>, + <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, or + <code class="literal">DELETE</code>. Note that an + <code class="command">INSERT</code> containing an <code class="literal">ON + CONFLICT</code> clause cannot be used on tables that have + either <code class="literal">INSERT</code> or <code class="literal">UPDATE</code> + rules. Consider using an updatable view instead. + </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of the table or view the + rule applies to. + </p></dd><dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt><dd><p> + Any <acronym class="acronym">SQL</acronym> conditional expression (returning + <code class="type">boolean</code>). The condition expression cannot refer + to any tables except <code class="literal">NEW</code> and <code class="literal">OLD</code>, and + cannot contain aggregate functions. + </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 be + executed <span class="emphasis"><em>instead of</em></span> the original command. + </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 be + executed <span class="emphasis"><em>in addition to</em></span> the original + command. + </p><p> + If neither <code class="literal">ALSO</code> nor + <code class="literal">INSTEAD</code> is specified, <code class="literal">ALSO</code> + is the default. + </p></dd><dt><span class="term"><em class="replaceable"><code>command</code></em></span></dt><dd><p> + The command or commands that make up the rule action. Valid + commands are <code class="command">SELECT</code>, + <code class="command">INSERT</code>, <code class="command">UPDATE</code>, + <code class="command">DELETE</code>, or <code class="command">NOTIFY</code>. + </p></dd></dl></div><p> + Within <em class="replaceable"><code>condition</code></em> and + <em class="replaceable"><code>command</code></em>, the special + table names <code class="literal">NEW</code> and <code class="literal">OLD</code> can + be used to refer to values in the referenced table. + <code class="literal">NEW</code> is valid in <code class="literal">ON INSERT</code> and + <code class="literal">ON UPDATE</code> rules to refer to the new row being + inserted or updated. <code class="literal">OLD</code> is valid in + <code class="literal">ON UPDATE</code> and <code class="literal">ON DELETE</code> rules + to refer to the existing row being updated or deleted. + </p></div><div class="refsect1" id="id-1.9.3.79.7"><h2>Notes</h2><p> + You must be the owner of a table to create or change rules for it. + </p><p> + In a rule for <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, or + <code class="literal">DELETE</code> on a view, you can add a <code class="literal">RETURNING</code> + clause that emits the view's columns. This clause will be used to compute + the outputs if the rule is triggered by an <code class="command">INSERT RETURNING</code>, + <code class="command">UPDATE RETURNING</code>, or <code class="command">DELETE RETURNING</code> command + respectively. When the rule is triggered by a command without + <code class="literal">RETURNING</code>, the rule's <code class="literal">RETURNING</code> clause will be + ignored. The current implementation allows only unconditional + <code class="literal">INSTEAD</code> rules to contain <code class="literal">RETURNING</code>; furthermore + there can be at most one <code class="literal">RETURNING</code> clause among all the rules + for the same event. (This ensures that there is only one candidate + <code class="literal">RETURNING</code> clause to be used to compute the results.) + <code class="literal">RETURNING</code> queries on the view will be rejected if + there is no <code class="literal">RETURNING</code> clause in any available rule. + </p><p> + It is very important to take care to avoid circular rules. For + example, though each of the following two rule definitions are + accepted by <span class="productname">PostgreSQL</span>, the + <code class="command">SELECT</code> command would cause + <span class="productname">PostgreSQL</span> to report an error because + of recursive expansion of a rule: + +</p><pre class="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; +</pre><p> + </p><p> + Presently, if a rule action contains a <code class="command">NOTIFY</code> + command, the <code class="command">NOTIFY</code> command will be executed + unconditionally, that is, the <code class="command">NOTIFY</code> will be + issued even if there are not any rows that the rule should apply + to. For example, in: +</p><pre class="programlisting"> +CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable; + +UPDATE mytable SET name = 'foo' WHERE id = 42; +</pre><p> + one <code class="command">NOTIFY</code> event will be sent during the + <code class="command">UPDATE</code>, whether or not there are any rows that + match the condition <code class="literal">id = 42</code>. This is an + implementation restriction that might be fixed in future releases. + </p></div><div class="refsect1" id="id-1.9.3.79.8"><h2>Compatibility</h2><p> + <code class="command">CREATE RULE</code> is a + <span class="productname">PostgreSQL</span> language extension, as is the + entire query rewrite system. + </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 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE SCHEMA</td></tr></table></div></body></html>
\ No newline at end of file |