summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-createrule.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-16 19:46:48 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-16 19:46:48 +0000
commit311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch)
tree0ec307299b1dada3701e42f4ca6eda57d708261e /doc/src/sgml/html/sql-createrule.html
parentInitial commit. (diff)
downloadpostgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.tar.xz
postgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.zip
Adding upstream version 15.4.upstream/15.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/sql-createrule.html')
-rw-r--r--doc/src/sgml/html/sql-createrule.html174
1 files changed, 174 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..f50b69f
--- /dev/null
+++ b/doc/src/sgml/html/sql-createrule.html
@@ -0,0 +1,174 @@
+<?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.4 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 must be unconditional
+ <code class="literal">INSTEAD</code> rules and must have actions that consist
+ of a single <code class="command">SELECT</code> command. Thus, an
+ <code class="literal">ON SELECT</code> rule effectively turns the table into
+ a view, whose visible contents are the rows returned by the rule's
+ <code class="command">SELECT</code> command rather than whatever had been
+ stored in the table (if anything). It is considered better style
+ to write a <code class="command">CREATE VIEW</code> command than to create a
+ real table and define an <code class="literal">ON SELECT</code> rule for it.
+ </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.4 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
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-01 01:49:47 +0000