Add OLD/NEW support to RETURNING in DML queries.

This allows the RETURNING list of INSERT/UPDATE/DELETE/MERGE queries
to explicitly return old and new values by using the special aliases
"old" and "new", which are automatically added to the query (if not
already defined) while parsing its RETURNING list, allowing things
like:

  RETURNING old.colname, new.colname, ...

  RETURNING old.*, new.*

Additionally, a new syntax is supported, allowing the names "old" and
"new" to be changed to user-supplied alias names, e.g.:

  RETURNING WITH (OLD AS o, NEW AS n) o.colname, n.colname, ...

This is useful when the names "old" and "new" are already defined,
such as inside trigger functions, allowing backwards compatibility to
be maintained -- the interpretation of any existing queries that
happen to already refer to relations called "old" or "new", or use
those as aliases for other relations, is not changed.

For an INSERT, old values will generally be NULL, and for a DELETE,
new values will generally be NULL, but that may change for an INSERT
with an ON CONFLICT ... DO UPDATE clause, or if a query rewrite rule
changes the command type. Therefore, we put no restrictions on the use
of old and new in any DML queries.

Dean Rasheed, reviewed by Jian He and Jeff Davis.

Discussion: https://postgr.es/m/CAEZATCWx0J0-v=Qjc6gXzR=KtsdvAE7Ow=D=mu50AgOe+pvisQ@mail.gmail.com

6 files changed, 214 insertions, 11 deletions

diff --git a/doc/src/sgml/dml.sgml b/doc/src/sgml/dml.sgml
index 3d95bdb94e7..458aee788b7 100644
--- a/doc/src/sgml/dml.sgml
+++ b/doc/src/sgml/dml.sgml
@@ -308,7 +308,8 @@ DELETE FROM products;
   </para>
 
   <para>
-   In an <command>INSERT</command>, the data available to <literal>RETURNING</literal> is
+   In an <command>INSERT</command>, the default data available to
+   <literal>RETURNING</literal> is
    the row as it was inserted.  This is not so useful in trivial inserts,
    since it would just repeat the data provided by the client.  But it can
    be very handy when relying on computed default values.  For example,
@@ -325,7 +326,8 @@ INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
   </para>
 
   <para>
-   In an <command>UPDATE</command>, the data available to <literal>RETURNING</literal> is
+   In an <command>UPDATE</command>, the default data available to
+   <literal>RETURNING</literal> is
    the new content of the modified row.  For example:
 <programlisting>
 UPDATE products SET price = price * 1.10
@@ -335,7 +337,8 @@ UPDATE products SET price = price * 1.10
   </para>
 
   <para>
-   In a <command>DELETE</command>, the data available to <literal>RETURNING</literal> is
+   In a <command>DELETE</command>, the default data available to
+   <literal>RETURNING</literal> is
    the content of the deleted row.  For example:
 <programlisting>
 DELETE FROM products
@@ -345,7 +348,8 @@ DELETE FROM products
   </para>
 
   <para>
-   In a <command>MERGE</command>, the data available to <literal>RETURNING</literal> is
+   In a <command>MERGE</command>, the default data available to
+   <literal>RETURNING</literal> is
    the content of the source row plus the content of the inserted, updated, or
    deleted target row.  Since it is quite common for the source and target to
    have many of the same columns, specifying <literal>RETURNING *</literal>
@@ -360,6 +364,35 @@ MERGE INTO products p USING new_products n ON p.product_no = n.product_no
   </para>
 
   <para>
+   In each of these commands, it is also possible to explicitly return the
+   old and new content of the modified row.  For example:
+<programlisting>
+UPDATE products SET price = price * 1.10
+  WHERE price &lt;= 99.99
+  RETURNING name, old.price AS old_price, new.price AS new_price,
+            new.price - old.price AS price_change;
+</programlisting>
+   In this example, writing <literal>new.price</literal> is the same as
+   just writing <literal>price</literal>, but it makes the meaning clearer.
+  </para>
+
+  <para>
+   This syntax for returning old and new values is available in
+   <command>INSERT</command>, <command>UPDATE</command>,
+   <command>DELETE</command>, and <command>MERGE</command> commands, but
+   typically old values will be <literal>NULL</literal> for an
+   <command>INSERT</command>, and new values will be <literal>NULL</literal>
+   for a <command>DELETE</command>.  However, there are situations where it
+   can still be useful for those commands.  For example, in an
+   <command>INSERT</command> with an
+   <link linkend="sql-on-conflict"><literal>ON CONFLICT DO UPDATE</literal></link>
+   clause, the old values will be non-<literal>NULL</literal> for conflicting
+   rows.  Similarly, if a <command>DELETE</command> is turned into an
+   <command>UPDATE</command> by a <link linkend="sql-createrule">rewrite rule</link>,
+   the new values may be non-<literal>NULL</literal>.
+  </para>
+
+  <para>
    If there are triggers (<xref linkend="triggers"/>) on the target table,
    the data available to <literal>RETURNING</literal> is the row as modified by
    the triggers.  Thus, inspecting columns computed by triggers is another
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 7717855bc9e..29649f6afd6 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -25,7 +25,8 @@ PostgreSQL documentation
 DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
     [ USING <replaceable class="parameter">from_item</replaceable> [, ...] ]
     [ WHERE <replaceable class="parameter">condition</replaceable> | WHERE CURRENT OF <replaceable class="parameter">cursor_name</replaceable> ]
-    [ RETURNING { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
+    [ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ]
+                { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -161,6 +162,26 @@ DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ *
    </varlistentry>
 
    <varlistentry>
+    <term><replaceable class="parameter">output_alias</replaceable></term>
+    <listitem>
+     <para>
+      An optional substitute name for <literal>OLD</literal> or
+      <literal>NEW</literal> rows in the <literal>RETURNING</literal> list.
+     </para>
+
+     <para>
+      By default, old values from the target table can be returned by writing
+      <literal>OLD.<replaceable class="parameter">column_name</replaceable></literal>
+      or <literal>OLD.*</literal>, and new values can be returned by writing
+      <literal>NEW.<replaceable class="parameter">column_name</replaceable></literal>
+      or <literal>NEW.*</literal>.  When an alias is provided, these names are
+      hidden and the old or new rows must be referred to using the alias.
+      For example <literal>RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><replaceable class="parameter">output_expression</replaceable></term>
     <listitem>
      <para>
@@ -170,6 +191,23 @@ DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ *
       or table(s) listed in <literal>USING</literal>.
       Write <literal>*</literal> to return all columns.
      </para>
+
+     <para>
+      A column name or <literal>*</literal> may be qualified using
+      <literal>OLD</literal> or <literal>NEW</literal>, or the corresponding
+      <replaceable class="parameter">output_alias</replaceable> for
+      <literal>OLD</literal> or <literal>NEW</literal>, to cause old or new
+      values to be returned.  An unqualified column name, or
+      <literal>*</literal>, or a column name or <literal>*</literal> qualified
+      using the target table name or alias will return old values.
+     </para>
+
+     <para>
+      For a simple <command>DELETE</command>, all new values will be
+      <literal>NULL</literal>.  However, if an <literal>ON DELETE</literal>
+      rule causes an <command>INSERT</command> or <command>UPDATE</command>
+      to be executed instead, the new values may be non-<literal>NULL</literal>.
+     </para>
     </listitem>
    </varlistentry>
 
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 6f0adee1a12..3f139917790 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -26,7 +26,8 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
     [ OVERRIDING { SYSTEM | USER } VALUE ]
     { DEFAULT VALUES | VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="parameter">query</replaceable> }
     [ ON CONFLICT [ <replaceable class="parameter">conflict_target</replaceable> ] <replaceable class="parameter">conflict_action</replaceable> ]
-    [ RETURNING { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
+    [ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ]
+                { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
 
 <phrase>where <replaceable class="parameter">conflict_target</replaceable> can be one of:</phrase>
 
@@ -294,6 +295,26 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
      </varlistentry>
 
      <varlistentry>
+      <term><replaceable class="parameter">output_alias</replaceable></term>
+      <listitem>
+       <para>
+        An optional substitute name for <literal>OLD</literal> or
+        <literal>NEW</literal> rows in the <literal>RETURNING</literal> list.
+       </para>
+
+       <para>
+        By default, old values from the target table can be returned by writing
+        <literal>OLD.<replaceable class="parameter">column_name</replaceable></literal>
+        or <literal>OLD.*</literal>, and new values can be returned by writing
+        <literal>NEW.<replaceable class="parameter">column_name</replaceable></literal>
+        or <literal>NEW.*</literal>.  When an alias is provided, these names are
+        hidden and the old or new rows must be referred to using the alias.
+        For example <literal>RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term><replaceable class="parameter">output_expression</replaceable></term>
       <listitem>
        <para>
@@ -305,6 +326,23 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
         <literal>*</literal> to return all columns of the inserted or updated
         row(s).
        </para>
+
+       <para>
+        A column name or <literal>*</literal> may be qualified using
+        <literal>OLD</literal> or <literal>NEW</literal>, or the corresponding
+        <replaceable class="parameter">output_alias</replaceable> for
+        <literal>OLD</literal> or <literal>NEW</literal>, to cause old or new
+        values to be returned.  An unqualified column name, or
+        <literal>*</literal>, or a column name or <literal>*</literal>
+        qualified using the target table name or alias will return new values.
+       </para>
+
+       <para>
+        For a simple <command>INSERT</command>, all old values will be
+        <literal>NULL</literal>.  However, for an <command>INSERT</command>
+        with an <literal>ON CONFLICT DO UPDATE</literal> clause, the old
+        values may be non-<literal>NULL</literal>.
+       </para>
       </listitem>
      </varlistentry>
 
@@ -714,6 +752,20 @@ INSERT INTO distributors (did, dname)
 </programlisting>
   </para>
   <para>
+   Insert or update new distributors as above, returning information
+   about any existing values that were updated, together with the new data
+   inserted.  Note that the returned values for <literal>old_did</literal>
+   and <literal>old_dname</literal> will be <literal>NULL</literal> for
+   non-conflicting rows:
+<programlisting>
+INSERT INTO distributors (did, dname)
+    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
+    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname
+    RETURNING old.did AS old_did, old.dname AS old_dname,
+              new.did AS new_did, new.dname AS new_dname;
+</programlisting>
+  </para>
+  <para>
    Insert a distributor, or do nothing for rows proposed for insertion
    when an existing, excluded row (a row with a matching constrained
    column or columns after before row insert triggers fire) exists.
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml
index d80a5c5cc9b..ecbcd8345d8 100644
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -25,7 +25,8 @@ PostgreSQL documentation
 MERGE INTO [ ONLY ] <replaceable class="parameter">target_table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ]
 USING <replaceable class="parameter">data_source</replaceable> ON <replaceable class="parameter">join_condition</replaceable>
 <replaceable class="parameter">when_clause</replaceable> [...]
-[ RETURNING { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
+[ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ]
+            { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
 
 <phrase>where <replaceable class="parameter">data_source</replaceable> is:</phrase>
 
@@ -500,6 +501,25 @@ DELETE
    </varlistentry>
 
    <varlistentry>
+    <term><replaceable class="parameter">output_alias</replaceable></term>
+    <listitem>
+     <para>
+      An optional substitute name for <literal>OLD</literal> or
+      <literal>NEW</literal> rows in the <literal>RETURNING</literal> list.
+     </para>
+     <para>
+      By default, old values from the target table can be returned by writing
+      <literal>OLD.<replaceable class="parameter">column_name</replaceable></literal>
+      or <literal>OLD.*</literal>, and new values can be returned by writing
+      <literal>NEW.<replaceable class="parameter">column_name</replaceable></literal>
+      or <literal>NEW.*</literal>.  When an alias is provided, these names are
+      hidden and the old or new rows must be referred to using the alias.
+      For example <literal>RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><replaceable class="parameter">output_expression</replaceable></term>
     <listitem>
      <para>
@@ -517,6 +537,17 @@ DELETE
       qualifying the <literal>*</literal> with the name or alias of the source
       or target table.
      </para>
+     <para>
+      A column name or <literal>*</literal> may also be qualified using
+      <literal>OLD</literal> or <literal>NEW</literal>, or the corresponding
+      <replaceable class="parameter">output_alias</replaceable> for
+      <literal>OLD</literal> or <literal>NEW</literal>, to cause old or new
+      values from the target table to be returned.  An unqualified column
+      name from the target table, or a column name or <literal>*</literal>
+      qualified using the target table name or alias will return new values
+      for <literal>INSERT</literal> and <literal>UPDATE</literal> actions, and
+      old values for <literal>DELETE</literal> actions.
+     </para>
     </listitem>
    </varlistentry>
 
@@ -722,7 +753,7 @@ WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
   UPDATE SET stock = w.stock + s.stock_delta
 WHEN MATCHED THEN
   DELETE
-RETURNING merge_action(), w.*;
+RETURNING merge_action(), w.winename, old.stock AS old_stock, new.stock AS new_stock;
 </programlisting>
 
    The <literal>wine_stock_changes</literal> table might be, for example, a
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 1c433bec2bb..12ec5ba0709 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -29,7 +29,8 @@ UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [
         } [, ...]
     [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
     [ WHERE <replaceable class="parameter">condition</replaceable> | WHERE CURRENT OF <replaceable class="parameter">cursor_name</replaceable> ]
-    [ RETURNING { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
+    [ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ]
+                { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -212,6 +213,26 @@ UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [
    </varlistentry>
 
    <varlistentry>
+    <term><replaceable class="parameter">output_alias</replaceable></term>
+    <listitem>
+     <para>
+      An optional substitute name for <literal>OLD</literal> or
+      <literal>NEW</literal> rows in the <literal>RETURNING</literal> list.
+     </para>
+
+     <para>
+      By default, old values from the target table can be returned by writing
+      <literal>OLD.<replaceable class="parameter">column_name</replaceable></literal>
+      or <literal>OLD.*</literal>, and new values can be returned by writing
+      <literal>NEW.<replaceable class="parameter">column_name</replaceable></literal>
+      or <literal>NEW.*</literal>.  When an alias is provided, these names are
+      hidden and the old or new rows must be referred to using the alias.
+      For example <literal>RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><replaceable class="parameter">output_expression</replaceable></term>
     <listitem>
      <para>
@@ -221,6 +242,16 @@ UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [
       or table(s) listed in <literal>FROM</literal>.
       Write <literal>*</literal> to return all columns.
      </para>
+
+     <para>
+      A column name or <literal>*</literal> may be qualified using
+      <literal>OLD</literal> or <literal>NEW</literal>, or the corresponding
+      <replaceable class="parameter">output_alias</replaceable> for
+      <literal>OLD</literal> or <literal>NEW</literal>, to cause old or new
+      values to be returned.  An unqualified column name, or
+      <literal>*</literal>, or a column name or <literal>*</literal> qualified
+      using the target table name or alias will return new values.
+     </para>
     </listitem>
    </varlistentry>
 
@@ -348,12 +379,13 @@ UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
   </para>
 
   <para>
-   Perform the same operation and return the updated entries:
+   Perform the same operation and return the updated entries, and the old
+   precipitation value:
 
 <programlisting>
 UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
   WHERE city = 'San Francisco' AND date = '2003-07-03'
-  RETURNING temp_lo, temp_hi, prcp;
+  RETURNING temp_lo, temp_hi, prcp, old.prcp AS old_prcp;
 </programlisting>
   </para>
 
diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 7e98f5694b4..9fdf8b1d917 100644
--- a/doc/src/sgml/rules.sgml
+++ b/doc/src/sgml/rules.sgml
@@ -1646,6 +1646,23 @@ CREATE RULE shoelace_ins AS ON INSERT TO shoelace
    </para>
 
    <para>
+    Note that in the <literal>RETURNING</literal> clause of a rule,
+    <literal>OLD</literal> and <literal>NEW</literal> refer to the
+    pseudorelations added as extra range table entries to the rewritten
+    query, rather than old/new rows in the result relation.  Thus, for
+    example, in a rule supporting <command>UPDATE</command> queries on this
+    view, if the <literal>RETURNING</literal> clause contained
+    <literal>old.sl_name</literal>, the old name would always be returned,
+    regardless of whether the <literal>RETURNING</literal> clause in the
+    query on the view specified <literal>OLD</literal> or <literal>NEW</literal>,
+    which might be confusing.  To avoid this confusion, and support returning
+    old and new values in queries on the view, the <literal>RETURNING</literal>
+    clause in the rule definition should refer to entries from the result
+    relation such as <literal>shoelace_data.sl_name</literal>, without
+    specifying <literal>OLD</literal> or <literal>NEW</literal>.
+   </para>
+
+   <para>
     Now assume that once in a while, a pack of shoelaces arrives at
     the shop and a big parts list along with it.  But you don't want
     to manually update the <literal>shoelace</literal> view every