Re: MERGE ... RETURNING - Mailing list pgsql-hackers

On Wed, 13 Mar 2024 at 06:44, jian he <jian.universality@gmail.com> wrote:
>
> <synopsis>
> [ WITH <replaceable class="parameter">with_query</replaceable> [, ...] ]
> MERGE INTO [ ONLY ] <replaceable
>
> here the "WITH" part should have "[ RECURSIVE ]"

Actually, no. MERGE doesn't support WITH RECURSIVE.

It's not entirely clear to me why though. I did a quick test, removing
that restriction in the parse analysis code, and it seemed to work
fine. Alvaro, do you remember why that restriction is there?

It's probably worth noting it in the docs, since it's different from
INSERT, UPDATE and DELETE. I think this would suffice:

   <varlistentry>
    <term><replaceable class="parameter">with_query</replaceable></term>
    <listitem>
     <para>
      The <literal>WITH</literal> clause allows you to specify one or more
      subqueries that can be referenced by name in the <command>MERGE</command>
      query. See <xref linkend="queries-with"/> and <xref linkend="sql-select"/>
      for details.  Note that <literal>WITH RECURSIVE</literal> is not supported
      by <command>MERGE</command>.
     </para>
    </listitem>
   </varlistentry>

And then maybe we can remove that restriction in HEAD, if there really
isn't any need for it anymore.

I also noticed that the "UPDATE SET ..." syntax in the synopsis is
missing a couple of options that are supported -- the optional "ROW"
keyword in the multi-column assignment syntax, and the syntax to
assign from a subquery that returns multiple columns. So this should
be updated to match update.sgml:

UPDATE SET { <replaceable class="parameter">column_name</replaceable>
= { <replaceable class="parameter">expression</replaceable> | DEFAULT
} |
             ( <replaceable
class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( {
<replaceable class="parameter">expression</replaceable> | DEFAULT } [,
...] ) |
             ( <replaceable
class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable
class="parameter">sub-SELECT</replaceable> )
           } [, ...]

and then in the parameter section:

   <varlistentry>
    <term><replaceable class="parameter">sub-SELECT</replaceable></term>
    <listitem>
     <para>
      A <literal>SELECT</literal> sub-query that produces as many output columns
      as are listed in the parenthesized column list preceding it.  The
      sub-query must yield no more than one row when executed.  If it
      yields one row, its column values are assigned to the target columns;
      if it yields no rows, NULL values are assigned to the target columns.
      The sub-query can refer to values from the original row in the
target table,
      and values from the <replaceable>data_source</replaceable>.
     </para>
    </listitem>
   </varlistentry>

(basically copied verbatim from update.sgml)

I think I'll go make those doc changes, and back-patch them
separately, since they're not related to this patch.

Regards,
Dean