On Thu, 6 Jul 2023 at 06:13, Gurjeet Singh <gurj...@singh.im> wrote:
>
> Most of the review was done with the v6 of the patch, minus the doc
> build step. The additional changes in v7 of the patch were eyeballed,
> and tested with `make check`.
>
Thanks for the review!
> > One minor annoyance is that, due to the way that pg_merge_action() and
> > pg_merge_when_clause() require access to the MergeActionState, they
> > only work if they appear directly in the RETURNING list. They can't,
> > for example, appear in a subquery in the RETURNING list, and I don't
> > see an easy way round that limitation.
>
> I believe that's a serious limitation, and would be a blocker for the feature.
>
Yes, that was bugging me for quite a while.
Attached is a new version that removes that restriction, allowing the
merge support functions to appear anywhere. This requires a bit of
planner support, to deal with merge support functions in subqueries,
in a similar way to how aggregates and GROUPING() expressions are
handled. But a number of the changes from the previous patch are no
longer necessary, so overall, this version of the patch is slightly
smaller.
> I think the name of function pg_merge_when_clause() can be improved.
> How about pg_merge_when_clause_ordinal().
>
That's a bit of a mouthful, but I don't have a better idea right now.
I've left the names alone for now, in case something better occurs to
anyone.
> In the doc page of MERGE, you've moved the 'with_query' from the
> bottom of Parameters section to the top of that section. Any reason
> for this? Since the Parameters section is quite large, for a moment I
> thought the patch was _adding_ the description of 'with_query'.
>
Ah yes, I was just making the order consistent with the
INSERT/UPDATE/DELETE pages. That could probably be committed
separately.
> + /*
> + * Merge support functions should only be called directly from a MERGE
> + * command, and need access to the parent ModifyTableState. The parser
> + * should have checked that such functions only appear in the RETURNING
> + * list of a MERGE, so this should never fail.
> + */
> + if (IsMergeSupportFunction(funcid))
> + {
> + if (!state->parent ||
> + !IsA(state->parent, ModifyTableState) ||
> + ((ModifyTableState *) state->parent)->operation != CMD_MERGE)
> + elog(ERROR, "merge support function called in non-merge
> context");
>
> As the comment says, this is an unexpected condition, and should have
> been caught and reported by the parser. So I think it'd be better to
> use an Assert() here. Moreover, there's ERROR being raised by the
> pg_merge_action() and pg_merge_when_clause() functions, when they
> detect the function context is not appropriate.
>
> I found it very innovative to allow these functions to be called only
> in a certain part of certain SQL command. I don't think there's a
> precedence for this in Postgres; I'd be glad to learn if there are
> other functions that Postgres exposes this way.
>
> - EXPR_KIND_RETURNING, /* RETURNING */
> + EXPR_KIND_RETURNING, /* RETURNING in INSERT/UPDATE/DELETE */
> + EXPR_KIND_MERGE_RETURNING, /* RETURNING in MERGE */
>
> Having to invent a whole new ParseExprKind enum, feels like a bit of
> an overkill. My only objection is that this is exactly like
> EXPR_KIND_RETURNING, hence EXPR_KIND_MERGE_RETURNING needs to be dealt
> with exactly in as many places. But I don't have any alternative
> proposals.
>
That's all gone now from the new patch, since there is no longer any
restriction on where these functions can appear.
> --- a/src/include/catalog/pg_proc.h
> +++ b/src/include/catalog/pg_proc.h
> +/* Is this a merge support function? (Requires fmgroids.h) */
> +#define IsMergeSupportFunction(funcid) \
> + ((funcid) == F_PG_MERGE_ACTION || \
> + (funcid) == F_PG_MERGE_WHEN_CLAUSE)
>
> If it doesn't cause recursion or other complications, I think we
> should simply include the fmgroids.h in pg_proc.h. I understand that
> not all .c files/consumers that include pg_proc.h may need fmgroids.h,
> but #include'ing it will eliminate the need to keep the "Requires..."
> note above, and avoid confusion, too.
>
There's now only one place that uses this macro, whereas there are a
lot of places that include pg_proc.h and not fmgroids.h, so I don't
think forcing them all to include fmgroids.h is a good idea. (BTW,
this approach and comment is borrowed from IsTrueArrayType() in
pg_type.h)
> --- a/src/test/regress/expected/merge.out
> +++ b/src/test/regress/expected/merge.out
>
> -WHEN MATCHED AND tid > 2 THEN
> +WHEN MATCHED AND tid >= 2 THEN
>
> This change can be treated as a bug fix :-)
>
> +-- COPY (MERGE ... RETURNING) TO ...
> +BEGIN;
> +COPY (
> + MERGE INTO sq_target t
> + USING v
> + ON tid = sid
> + WHEN MATCHED AND tid > 2 THEN
>
> For consistency, the check should be tid >= 2, like you've fixed in
> command referenced above.
>
> +CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int,
> + OUT action text, OUT tid int,
> OUT new_balance int)
> +LANGUAGE sql AS
> +$$
> + MERGE INTO sq_target t
> + USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta)
> + ON tid = v.sid
> + WHEN MATCHED AND tid > 2 THEN
>
> Again, for consistency, the comparison operator should be >=. There
> are a few more occurrences of this comparison in the rest of the file,
> that need the same treatment.
>
I changed the new tests to use ">= 2" (and the COPY test now returns 3
rows, with an action of each type, which is easier to read), but I
don't think it's really necessary to change all the existing tests
from "> 2". There's nothing wrong with the "= 2" case having no
action, as long as the tests give decent coverage.
Thanks again for all the feedback.
Regards,
Dean
diff --git a/doc/src/sgml/dml.sgml b/doc/src/sgml/dml.sgml
new file mode 100644
index cbbc5e2..ff2a827
--- a/doc/src/sgml/dml.sgml
+++ b/doc/src/sgml/dml.sgml
@@ -283,10 +283,15 @@ DELETE FROM products;
<secondary>RETURNING</secondary>
</indexterm>
+ <indexterm zone="dml-returning">
+ <primary>MERGE</primary>
+ <secondary>RETURNING</secondary>
+ </indexterm>
+
<para>
Sometimes it is useful to obtain data from modified rows while they are
being manipulated. The <command>INSERT</command>, <command>UPDATE</command>,
- and <command>DELETE</command> commands all have an
+ <command>DELETE</command>, and <command>MERGE</command> commands all have an
optional <literal>RETURNING</literal> clause that supports this. Use
of <literal>RETURNING</literal> avoids performing an extra database query to
collect the data, and is especially valuable when it would otherwise be
@@ -339,6 +344,24 @@ DELETE FROM products
</programlisting>
</para>
+ <para>
+ In a <command>MERGE</command>, the data available to <literal>RETURNING</literal> is
+ the content of the source row plus the content of the inserted, updated, or
+ deleted target row. In addition, the merge support functions
+ <xref linkend="pg-merge-action"/> and <xref linkend="pg-merge-when-clause"/>
+ may be used to return information about which merge action was executed for
+ each row. Since it is quite common for the source and target to have many
+ of the same columns, specifying <literal>RETURNING *</literal> can lead to a
+ lot of duplicated columns, so it is often more useful to just return the
+ target row. For example:
+<programlisting>
+MERGE INTO products p USING new_products n ON p.product_no = n.product_no
+ WHEN NOT MATCHED THEN INSERT VALUES (n.product_no, n.name, n.price)
+ WHEN MATCHED THEN UPDATE SET name = n.name, price = n.price
+ RETURNING pg_merge_action(), p.*;
+</programlisting>
+ </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
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 5a47ce4..1406c18
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -21705,6 +21705,94 @@ SELECT count(*) FROM sometable;
</sect1>
+ <sect1 id="functions-merge-support">
+ <title>Merge Support Functions</title>
+
+ <indexterm>
+ <primary>MERGE</primary>
+ <secondary>RETURNING</secondary>
+ </indexterm>
+
+ <para>
+ The merge support functions shown in
+ <xref linkend="functions-merge-support-table"/> may be used in the
+ <literal>RETURNING</literal> list of a <xref linkend="sql-merge"/> command
+ to return additional information about the action taken for each row.
+ </para>
+
+ <table id="functions-merge-support-table">
+ <title>Merge Support Functions</title>
+
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm id="pg-merge-action">
+ <primary>pg_merge_action</primary>
+ </indexterm>
+ <function>pg_merge_action</function> ( )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Returns the action performed on the current row (<literal>'INSERT'</literal>,
+ <literal>'UPDATE'</literal>, or <literal>'DELETE'</literal>).
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm id="pg-merge-when-clause">
+ <primary>pg_merge_when_clause</primary>
+ </indexterm>
+ <function>pg_merge_when_clause</function> ( )
+ <returnvalue>integer</returnvalue>
+ </para>
+ <para>
+ Returns the 1-based index of the <literal>WHEN</literal> clause executed
+ for the current row.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ Example:
+<screen><![CDATA[
+MERGE INTO products p
+ USING stock s ON p.product_id = s.product_id
+ WHEN MATCHED AND s.quantity > 0 THEN
+ UPDATE SET in_stock = true, quantity = s.quantity
+ WHEN MATCHED THEN
+ UPDATE SET in_stock = false, quantity = 0
+ WHEN NOT MATCHED THEN
+ INSERT (product_id, in_stock, quantity)
+ VALUES (s.product_id, true, s.quantity)
+ RETURNING pg_merge_action() AS action,
+ pg_merge_when_clause() AS when_clause,
+ p.*;
+
+ action | when_clause | product_id | in_stock | quantity
+--------+-------------+------------+----------+----------
+ UPDATE | 1 | 1001 | t | 50
+ UPDATE | 2 | 1002 | f | 0
+ INSERT | 3 | 1003 | t | 10
+]]></screen>
+ </para>
+
+ </sect1>
+
<sect1 id="functions-subquery">
<title>Subquery Expressions</title>
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
new file mode 100644
index fe8def4..51a15ca
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -1387,9 +1387,9 @@
to a <glossterm linkend="glossary-client">client</glossterm> upon the
completion of an <acronym>SQL</acronym> command, usually a
<command>SELECT</command> but it can be an
- <command>INSERT</command>, <command>UPDATE</command>, or
- <command>DELETE</command> command if the <literal>RETURNING</literal>
- clause is specified.
+ <command>INSERT</command>, <command>UPDATE</command>,
+ <command>DELETE</command>, or <command>MERGE</command> command if the
+ <literal>RETURNING</literal> clause is specified.
</para>
<para>
The fact that a result set is a relation means that a query can be used
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index f55e901..6803240
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1020,8 +1020,8 @@ INSERT INTO mytable VALUES (1,'one'), (2
</para>
<para>
- If the command does return rows (for example <command>SELECT</command>,
- or <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>
+ If the command does return rows (for example <command>SELECT</command>, or
+ <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>/<command>MERGE</command>
with <literal>RETURNING</literal>), there are two ways to proceed.
When the command will return at most one row, or you only care about
the first row of output, write the command as usual but add
@@ -1149,6 +1149,7 @@ SELECT <replaceable>select_expressions</
INSERT ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
UPDATE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
DELETE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
+MERGE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
</synopsis>
where <replaceable>target</replaceable> can be a record variable, a row
@@ -1159,8 +1160,8 @@ DELETE ... RETURNING <replaceable>expres
<literal>INTO</literal> clause) just as described above,
and the plan is cached in the same way.
This works for <command>SELECT</command>,
- <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command> with
- <literal>RETURNING</literal>, and certain utility commands
+ <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>/<command>MERGE</command>
+ with <literal>RETURNING</literal>, and certain utility commands
that return row sets, such as <command>EXPLAIN</command>.
Except for the <literal>INTO</literal> clause, the SQL command is the same
as it would be written outside <application>PL/pgSQL</application>.
@@ -1236,7 +1237,7 @@ END;
</para>
<para>
- For <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command> with
+ For <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>/<command>MERGE</command> with
<literal>RETURNING</literal>, <application>PL/pgSQL</application> reports
an error for more than one returned row, even when
<literal>STRICT</literal> is not specified. This is because there
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
new file mode 100644
index 3f95849..9928373
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -2062,9 +2062,10 @@ SELECT <replaceable>select_list</replace
Table Expressions or <acronym>CTE</acronym>s, can be thought of as defining
temporary tables that exist just for one query. Each auxiliary statement
in a <literal>WITH</literal> clause can be a <command>SELECT</command>,
- <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command>; and the
+ <command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>,
+ or <command>MERGE</command>; and the
<literal>WITH</literal> clause itself is attached to a primary statement that can
- be a <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
+ also be a <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
<command>DELETE</command>, or <command>MERGE</command>.
</para>
@@ -2598,8 +2599,8 @@ SELECT * FROM w AS w1 JOIN w AS w2 ON w1
<title>Data-Modifying Statements in <literal>WITH</literal></title>
<para>
- You can use most data-modifying statements (<command>INSERT</command>,
- <command>UPDATE</command>, or <command>DELETE</command>, but not
+ You can use data-modifying statements (<command>INSERT</command>,
+ <command>UPDATE</command>, <command>DELETE</command>, or
<command>MERGE</command>) in <literal>WITH</literal>. This
allows you to perform several different operations in the same query.
An example is:
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
new file mode 100644
index 5e591ed..dbc76df
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -122,13 +122,16 @@ COPY { <replaceable class="parameter">ta
A <link linkend="sql-select"><command>SELECT</command></link>,
<link linkend="sql-values"><command>VALUES</command></link>,
<link linkend="sql-insert"><command>INSERT</command></link>,
- <link linkend="sql-update"><command>UPDATE</command></link>, or
- <link linkend="sql-delete"><command>DELETE</command></link> command whose results are to be
- copied. Note that parentheses are required around the query.
+ <link linkend="sql-update"><command>UPDATE</command></link>,
+ <link linkend="sql-delete"><command>DELETE</command></link>, or
+ <link linkend="sql-merge"><command>MERGE</command></link> command
+ whose results are to be copied. Note that parentheses are required
+ around the query.
</para>
<para>
- For <command>INSERT</command>, <command>UPDATE</command> and
- <command>DELETE</command> queries a RETURNING clause must be provided,
+ For <command>INSERT</command>, <command>UPDATE</command>,
+ <command>DELETE</command>, and <command>MERGE</command> queries a
+ RETURNING clause must be provided,
and the target relation must not have a conditional rule, nor
an <literal>ALSO</literal> rule, nor an <literal>INSTEAD</literal> rule
that expands to multiple statements.
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml
new file mode 100644
index 0995fe0..7eb641a
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -25,6 +25,7 @@ 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> ] [, ...] ]
<phrase>where <replaceable class="parameter">data_source</replaceable> is:</phrase>
@@ -95,6 +96,20 @@ DELETE
</para>
<para>
+ The optional <literal>RETURNING</literal> clause causes <command>MERGE</command>
+ to compute and return value(s) based on each row inserted, updated, or
+ deleted. Any expression using the source or target table's columns, or the
+ merge support functions <xref linkend="pg-merge-action"/> and
+ <xref linkend="pg-merge-when-clause"/> can be computed. When an
+ <command>INSERT</command> or <command>UPDATE</command> action is performed,
+ the new values of the target table's columns are used. When a
+ <command>DELETE</command> is performed, the old values of the target table's
+ columns are used. The syntax of the <literal>RETURNING</literal> list is
+ identical to that of the output list of <command>SELECT</command>.
+
+ </para>
+
+ <para>
There is no separate <literal>MERGE</literal> privilege.
If you specify an update action, you must have the
<literal>UPDATE</literal> privilege on the column(s)
@@ -125,6 +140,18 @@ DELETE
<variablelist>
<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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">target_table_name</replaceable></term>
<listitem>
<para>
@@ -399,13 +426,30 @@ DELETE
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">with_query</replaceable></term>
+ <term><replaceable class="parameter">output_expression</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.
+ An expression to be computed and returned by the <command>MERGE</command>
+ command after each row is merged. The expression can use any columns of
+ the source or target tables, or the merge support functions
+ <xref linkend="pg-merge-action"/> and <xref linkend="pg-merge-when-clause"/>.
+ </para>
+ <para>
+ Writing <literal>*</literal> will return all columns from the source
+ table, followed by all columns from the target table. Often this will
+ lead to a lot of duplication, since it is common for the source and
+ target tables to have a lot of the same columns. This can be avoided by
+ qualifying the <literal>*</literal> with the name or alias of the source
+ or target table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">output_name</replaceable></term>
+ <listitem>
+ <para>
+ A name to use for a returned column.
</para>
</listitem>
</varlistentry>
@@ -428,6 +472,13 @@ MERGE <replaceable class="parameter">tot
were changed in any way.
</para>
+ <para>
+ If the <command>MERGE</command> command contains a <literal>RETURNING</literal>
+ clause, the result will be similar to that of a <command>SELECT</command>
+ statement containing the columns and values defined in the
+ <literal>RETURNING</literal> list, computed over the row(s) inserted, updated,
+ or deleted by the command.
+ </para>
</refsect1>
<refsect1>
@@ -544,13 +595,6 @@ MERGE <replaceable class="parameter">tot
</para>
<para>
- There is no <literal>RETURNING</literal> clause with
- <command>MERGE</command>. Actions of <command>INSERT</command>,
- <command>UPDATE</command> and <command>DELETE</command> cannot contain
- <literal>RETURNING</literal> or <literal>WITH</literal> clauses.
- </para>
-
- <para>
When <command>MERGE</command> is run concurrently with other commands
that modify the target table, the usual transaction isolation rules
apply; see <xref linkend="transaction-iso"/> for an explanation
@@ -602,7 +646,8 @@ WHEN NOT MATCHED THEN
<para>
Attempt to insert a new stock item along with the quantity of stock. If
the item already exists, instead update the stock count of the existing
- item. Don't allow entries that have zero stock.
+ item. Don't allow entries that have zero stock. Return details of all
+ changes made.
<programlisting>
MERGE INTO wines w
USING wine_stock_changes s
@@ -612,7 +657,8 @@ WHEN NOT MATCHED AND s.stock_delta > 0 T
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
- DELETE;
+ DELETE
+RETURNING pg_merge_action(), w.*;
</programlisting>
The <literal>wine_stock_changes</literal> table might be, for example, a
@@ -627,7 +673,8 @@ WHEN MATCHED THEN
This command conforms to the <acronym>SQL</acronym> standard.
</para>
<para>
- The WITH clause and <literal>DO NOTHING</literal> action are extensions to
+ The WITH clause, <literal>DO NOTHING</literal> action, and
+ <literal>RETURNING</literal> clause are extensions to
the <acronym>SQL</acronym> standard.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
new file mode 100644
index 0ee0cc7..f81bdea
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -74,7 +74,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replacea
<phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
- <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( <replaceable class="parameter">select</replaceable> | <replaceable class="parameter">values</replaceable> | <replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> )
+ <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( <replaceable class="parameter">select</replaceable> | <replaceable class="parameter">values</replaceable> | <replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> | <replaceable class="parameter">merge</replaceable> )
[ SEARCH { BREADTH | DEPTH } FIRST BY <replaceable>column_name</replaceable> [, ...] SET <replaceable>search_seq_col_name</replaceable> ]
[ CYCLE <replaceable>column_name</replaceable> [, ...] SET <replaceable>cycle_mark_col_name</replaceable> [ TO <replaceable>cycle_mark_value</replaceable> DEFAULT <replaceable>cycle_mark_default</replaceable> ] USING <replaceable>cycle_path_col_name</replaceable> ]
@@ -227,10 +227,10 @@ TABLE [ ONLY ] <replaceable class="param
The subqueries effectively act as temporary tables or views
for the duration of the primary query.
Each subquery can be a <command>SELECT</command>, <command>TABLE</command>, <command>VALUES</command>,
- <command>INSERT</command>, <command>UPDATE</command> or
- <command>DELETE</command> statement.
+ <command>INSERT</command>, <command>UPDATE</command>,
+ <command>DELETE</command>, or <command>MERGE</command> statement.
When writing a data-modifying statement (<command>INSERT</command>,
- <command>UPDATE</command> or <command>DELETE</command>) in
+ <command>UPDATE</command>, <command>DELETE</command>, or <command>MERGE</command>) in
<literal>WITH</literal>, it is usual to include a <literal>RETURNING</literal> clause.
It is the output of <literal>RETURNING</literal>, <emphasis>not</emphasis> the underlying
table that the statement modifies, that forms the temporary table that is
@@ -2179,7 +2179,8 @@ SELECT 2+2;
<para>
<productname>PostgreSQL</productname> allows <command>INSERT</command>,
- <command>UPDATE</command>, and <command>DELETE</command> to be used as <literal>WITH</literal>
+ <command>UPDATE</command>, <command>DELETE</command>, and
+ <command>MERGE</command> to be used as <literal>WITH</literal>
queries. This is not found in the SQL standard.
</para>
</refsect2>
diff --git a/doc/src/sgml/rowtypes.sgml b/doc/src/sgml/rowtypes.sgml
new file mode 100644
index 4d86f97..bbeac84
--- a/doc/src/sgml/rowtypes.sgml
+++ b/doc/src/sgml/rowtypes.sgml
@@ -348,7 +348,7 @@ SELECT m.* FROM some_table, LATERAL myfu
column expansion of this kind when it appears at the top level of
a <link linkend="queries-select-lists"><command>SELECT</command> output
list</link>, a <link linkend="dml-returning"><literal>RETURNING</literal>
- list</link> in <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>,
+ list</link> in <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>/<command>MERGE</command>,
a <link linkend="queries-values"><literal>VALUES</literal> clause</link>, or
a <link linkend="sql-syntax-row-constructors">row constructor</link>.
In all other contexts (including when nested inside one of those
diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml
new file mode 100644
index 651930a..ad0a9b1
--- a/doc/src/sgml/spi.sgml
+++ b/doc/src/sgml/spi.sgml
@@ -301,8 +301,9 @@ SPI_execute("INSERT INTO foo SELECT * FR
is returned in the global variable <varname>SPI_processed</varname>.
If the return value of the function is <symbol>SPI_OK_SELECT</symbol>,
<symbol>SPI_OK_INSERT_RETURNING</symbol>,
- <symbol>SPI_OK_DELETE_RETURNING</symbol>, or
- <symbol>SPI_OK_UPDATE_RETURNING</symbol>,
+ <symbol>SPI_OK_DELETE_RETURNING</symbol>,
+ <symbol>SPI_OK_UPDATE_RETURNING</symbol>, or
+ <symbol>SPI_OK_MERGE_RETURNING</symbol>
then you can use the
global pointer <literal>SPITupleTable *SPI_tuptable</literal> to
access the result rows. Some utility commands (such as
@@ -472,6 +473,15 @@ typedef struct SPITupleTable
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_OK_MERGE_RETURNING</symbol></term>
+ <listitem>
+ <para>
+ if a <command>MERGE RETURNING</command> was executed
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><symbol>SPI_OK_UTILITY</symbol></term>
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
new file mode 100644
index 9620ea9..4f68efb
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -177,7 +177,8 @@
statements separated by semicolons. A semicolon after the last
statement is optional. Unless the function is declared to return
<type>void</type>, the last statement must be a <command>SELECT</command>,
- or an <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command>
+ or an <command>INSERT</command>, <command>UPDATE</command>,
+ <command>DELETE</command>, or <command>MERGE</command>
that has a <literal>RETURNING</literal> clause.
</para>
@@ -1236,8 +1237,9 @@ SELECT x, CASE WHEN x > 0 THEN genera
<note>
<para>
- If a function's last command is <command>INSERT</command>, <command>UPDATE</command>,
- or <command>DELETE</command> with <literal>RETURNING</literal>, that command will
+ If a function's last command is <command>INSERT</command>,
+ <command>UPDATE</command>, <command>DELETE</command>, or
+ <command>MERGE</command> with <literal>RETURNING</literal>, that command will
always be executed to completion, even if the function is not declared
with <literal>SETOF</literal> or the calling query does not fetch all the
result rows. Any extra rows produced by the <literal>RETURNING</literal>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
new file mode 100644
index f14fae3..05f2e5b
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -283,12 +283,6 @@ DoCopy(ParseState *pstate, const CopyStm
{
Assert(stmt->query);
- /* MERGE is allowed by parser, but unimplemented. Reject for now */
- if (IsA(stmt->query, MergeStmt))
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("MERGE not supported in COPY"));
-
query = makeNode(RawStmt);
query->stmt = stmt->query;
query->stmt_location = stmt_location;
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
new file mode 100644
index 9e4b243..cf56f9f
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -510,7 +510,8 @@ BeginCopyTo(ParseState *pstate,
{
Assert(query->commandType == CMD_INSERT ||
query->commandType == CMD_UPDATE ||
- query->commandType == CMD_DELETE);
+ query->commandType == CMD_DELETE ||
+ query->commandType == CMD_MERGE);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
new file mode 100644
index eb8a87f..b9bd03c
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -612,8 +612,8 @@ ExecInitPartitionInfo(ModifyTableState *
* Build the RETURNING projection for the partition. Note that we didn't
* build the returningList for partitions within the planner, but simple
* translation of varattnos will suffice. This only occurs for the INSERT
- * case or in the case of UPDATE tuple routing where we didn't find a
- * result rel to reuse.
+ * case or in the case of UPDATE/MERGE tuple routing where we didn't find
+ * a result rel to reuse.
*/
if (node && node->returningLists != NIL)
{
@@ -622,12 +622,14 @@ ExecInitPartitionInfo(ModifyTableState *
List *returningList;
/* See the comment above for WCO lists. */
- /* (except no RETURNING support for MERGE yet) */
Assert((node->operation == CMD_INSERT &&
list_length(node->returningLists) == 1 &&
list_length(node->resultRelations) == 1) ||
(node->operation == CMD_UPDATE &&
list_length(node->returningLists) ==
+ list_length(node->resultRelations)) ||
+ (node->operation == CMD_MERGE &&
+ list_length(node->returningLists) ==
list_length(node->resultRelations)));
/*
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
new file mode 100644
index f55424e..f99f15f
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -1660,8 +1660,8 @@ check_sql_fn_retval(List *queryTreeLists
/*
* If it's a plain SELECT, it returns whatever the targetlist says.
- * Otherwise, if it's INSERT/UPDATE/DELETE with RETURNING, it returns
- * that. Otherwise, the function return type must be VOID.
+ * Otherwise, if it's INSERT/UPDATE/DELETE/MERGE with RETURNING, it
+ * returns that. Otherwise, the function return type must be VOID.
*
* Note: eventually replace this test with QueryReturnsTuples? We'd need
* a more general method of determining the output type, though. Also, it
@@ -1679,7 +1679,8 @@ check_sql_fn_retval(List *queryTreeLists
else if (parse &&
(parse->commandType == CMD_INSERT ||
parse->commandType == CMD_UPDATE ||
- parse->commandType == CMD_DELETE) &&
+ parse->commandType == CMD_DELETE ||
+ parse->commandType == CMD_MERGE) &&
parse->returningList)
{
tlist = parse->returningList;
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 2a5fec8..3f6f495
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -36,8 +36,7 @@
* RETURNING tuple after completing each row insert, update, or delete.
* It must be called again to continue the operation. Without RETURNING,
* we just loop within the node until all the work is done, then
- * return NULL. This avoids useless call/return overhead. (MERGE does
- * not support RETURNING.)
+ * return NULL. This avoids useless call/return overhead.
*/
#include "postgres.h"
@@ -64,6 +63,10 @@
#include "utils/rel.h"
+/* State available for merge support functions */
+static CmdType mergeActionCmdType = CMD_NOTHING;
+static int32 mergeActionIdx = 0;
+
typedef struct MTTargetRelLookup
{
Oid relationOid; /* hash key, must be first */
@@ -153,13 +156,14 @@ static TupleTableSlot *ExecMerge(ModifyT
ItemPointer tupleid,
bool canSetTag);
static void ExecInitMerge(ModifyTableState *mtstate, EState *estate);
-static bool ExecMergeMatched(ModifyTableContext *context,
- ResultRelInfo *resultRelInfo,
- ItemPointer tupleid,
- bool canSetTag);
-static void ExecMergeNotMatched(ModifyTableContext *context,
- ResultRelInfo *resultRelInfo,
- bool canSetTag);
+static TupleTableSlot *ExecMergeMatched(ModifyTableContext *context,
+ ResultRelInfo *resultRelInfo,
+ ItemPointer tupleid,
+ bool canSetTag,
+ bool *matched);
+static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context,
+ ResultRelInfo *resultRelInfo,
+ bool canSetTag);
/*
@@ -237,6 +241,7 @@ ExecCheckPlanOutput(Relation resultRel,
/*
* ExecProcessReturning --- evaluate a RETURNING list
*
+ * context: state of current ModifyTable operation
* resultRelInfo: current result rel
* tupleSlot: slot holding tuple actually inserted/updated/deleted
* planSlot: slot holding tuple returned by top subplan node
@@ -247,12 +252,14 @@ ExecCheckPlanOutput(Relation resultRel,
* Returns a slot holding the result tuple
*/
static TupleTableSlot *
-ExecProcessReturning(ResultRelInfo *resultRelInfo,
+ExecProcessReturning(ModifyTableContext *context,
+ ResultRelInfo *resultRelInfo,
TupleTableSlot *tupleSlot,
TupleTableSlot *planSlot)
{
ProjectionInfo *projectReturning = resultRelInfo->ri_projectReturning;
ExprContext *econtext = projectReturning->pi_exprContext;
+ TupleTableSlot *rslot;
/* Make tuple and any needed join variables available to ExecProject */
if (tupleSlot)
@@ -266,8 +273,32 @@ ExecProcessReturning(ResultRelInfo *resu
econtext->ecxt_scantuple->tts_tableOid =
RelationGetRelid(resultRelInfo->ri_RelationDesc);
- /* Compute the RETURNING expressions */
- return ExecProject(projectReturning);
+ /*
+ * Compute the RETURNING expressions. If this is a MERGE, additional state
+ * is made available to any merge support functions in the RETURNING list.
+ */
+ if (context->relaction)
+ {
+ CmdType saved_mergeActionCmdType = mergeActionCmdType;
+ int32 saved_mergeActionIdx = mergeActionIdx;
+
+ mergeActionCmdType = context->relaction->mas_action->commandType;
+ mergeActionIdx = context->relaction->mas_action->index;
+ PG_TRY();
+ {
+ rslot = ExecProject(projectReturning);
+ }
+ PG_FINALLY();
+ {
+ mergeActionCmdType = saved_mergeActionCmdType;
+ mergeActionIdx = saved_mergeActionIdx;
+ }
+ PG_END_TRY();
+ }
+ else
+ rslot = ExecProject(projectReturning);
+
+ return rslot;
}
/*
@@ -1195,7 +1226,7 @@ ExecInsert(ModifyTableContext *context,
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
- result = ExecProcessReturning(resultRelInfo, slot, planSlot);
+ result = ExecProcessReturning(context, resultRelInfo, slot, planSlot);
if (inserted_tuple)
*inserted_tuple = slot;
@@ -1692,7 +1723,8 @@ ldelete:
}
}
- rslot = ExecProcessReturning(resultRelInfo, slot, context->planSlot);
+ rslot = ExecProcessReturning(context, resultRelInfo, slot,
+ context->planSlot);
/*
* Before releasing the target tuple again, make sure rslot has a
@@ -2480,7 +2512,8 @@ redo_act:
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
- return ExecProcessReturning(resultRelInfo, slot, context->planSlot);
+ return ExecProcessReturning(context, resultRelInfo, slot,
+ context->planSlot);
return NULL;
}
@@ -2712,6 +2745,7 @@ static TupleTableSlot *
ExecMerge(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ItemPointer tupleid, bool canSetTag)
{
+ TupleTableSlot *rslot = NULL;
bool matched;
/*-----
@@ -2759,18 +2793,18 @@ ExecMerge(ModifyTableContext *context, R
*/
matched = tupleid != NULL;
if (matched)
- matched = ExecMergeMatched(context, resultRelInfo, tupleid, canSetTag);
+ rslot = ExecMergeMatched(context, resultRelInfo, tupleid, canSetTag,
+ &matched);
/*
- * Either we were dealing with a NOT MATCHED tuple or ExecMergeMatched()
- * returned "false", indicating the previously MATCHED tuple no longer
- * matches.
+ * Deal with the NOT MATCHED case (either a NOT MATCHED tuple from the
+ * join, or a previously MATCHED tuple for which ExecMergeMatched() set
+ * "matched" to false, indicating that it no longer matches).
*/
if (!matched)
- ExecMergeNotMatched(context, resultRelInfo, canSetTag);
+ rslot = ExecMergeNotMatched(context, resultRelInfo, canSetTag);
- /* No RETURNING support yet */
- return NULL;
+ return rslot;
}
/*
@@ -2780,8 +2814,8 @@ ExecMerge(ModifyTableContext *context, R
* We start from the first WHEN MATCHED action and check if the WHEN quals
* pass, if any. If the WHEN quals for the first action do not pass, we
* check the second, then the third and so on. If we reach to the end, no
- * action is taken and we return true, indicating that no further action is
- * required for this tuple.
+ * action is taken and "matched" is set to true, indicating that no further
+ * action is required for this tuple.
*
* If we do find a qualifying action, then we attempt to execute the action.
*
@@ -2790,15 +2824,16 @@ ExecMerge(ModifyTableContext *context, R
* with individual actions are evaluated by this routine via ExecQual, while
* EvalPlanQual checks for the join quals. If EvalPlanQual tells us that the
* updated tuple still passes the join quals, then we restart from the first
- * action to look for a qualifying action. Otherwise, we return false --
- * meaning that a NOT MATCHED action must now be executed for the current
- * source tuple.
+ * action to look for a qualifying action. Otherwise, "matched" is set to
+ * false -- meaning that a NOT MATCHED action must now be executed for the
+ * current source tuple.
*/
-static bool
+static TupleTableSlot *
ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
- ItemPointer tupleid, bool canSetTag)
+ ItemPointer tupleid, bool canSetTag, bool *matched)
{
ModifyTableState *mtstate = context->mtstate;
+ TupleTableSlot *rslot = NULL;
TupleTableSlot *newslot;
EState *estate = context->estate;
ExprContext *econtext = mtstate->ps.ps_ExprContext;
@@ -2810,7 +2845,10 @@ ExecMergeMatched(ModifyTableContext *con
* If there are no WHEN MATCHED actions, we are done.
*/
if (resultRelInfo->ri_matchedMergeAction == NIL)
- return true;
+ {
+ *matched = true;
+ return NULL;
+ }
/*
* Make tuple and any needed join variables available to ExecQual and
@@ -2893,7 +2931,10 @@ lmerge_matched:
tupleid, NULL, newslot, &result))
{
if (result == TM_Ok)
- return true; /* "do nothing" */
+ {
+ *matched = true;
+ return NULL; /* "do nothing" */
+ }
break; /* concurrent update/delete */
}
result = ExecUpdateAct(context, resultRelInfo, tupleid, NULL,
@@ -2912,7 +2953,10 @@ lmerge_matched:
NULL, NULL, &result))
{
if (result == TM_Ok)
- return true; /* "do nothing" */
+ {
+ *matched = true;
+ return NULL; /* "do nothing" */
+ }
break; /* concurrent update/delete */
}
result = ExecDeleteAct(context, resultRelInfo, tupleid, false);
@@ -2968,7 +3012,8 @@ lmerge_matched:
* If the tuple was already deleted, return to let caller
* handle it under NOT MATCHED clauses.
*/
- return false;
+ *matched = false;
+ return NULL;
case TM_Updated:
{
@@ -3014,13 +3059,19 @@ lmerge_matched:
* NOT MATCHED actions.
*/
if (TupIsNull(epqslot))
- return false;
+ {
+ *matched = false;
+ return NULL;
+ }
(void) ExecGetJunkAttribute(epqslot,
resultRelInfo->ri_RowIdAttNo,
&isNull);
if (isNull)
- return false;
+ {
+ *matched = false;
+ return NULL;
+ }
/*
* When a tuple was updated and migrated to
@@ -3055,7 +3106,8 @@ lmerge_matched:
* tuple already deleted; tell caller to run NOT
* MATCHED actions
*/
- return false;
+ *matched = false;
+ return NULL;
case TM_SelfModified:
@@ -3075,13 +3127,14 @@ lmerge_matched:
(errcode(ERRCODE_TRIGGERED_DATA_CHANGE_VIOLATION),
errmsg("tuple to be updated or deleted was already modified by an operation triggered by the current command"),
errhint("Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.")));
- return false;
+ *matched = false;
+ return NULL;
default:
/* see table_tuple_lock call in ExecDelete() */
elog(ERROR, "unexpected table_tuple_lock status: %u",
result);
- return false;
+ return NULL;
}
}
@@ -3093,6 +3146,32 @@ lmerge_matched:
break;
}
+ /* Process RETURNING if present */
+ if (resultRelInfo->ri_projectReturning)
+ {
+ switch (commandType)
+ {
+ case CMD_UPDATE:
+ rslot = ExecProcessReturning(context,
+ resultRelInfo, newslot,
+ context->planSlot);
+ break;
+
+ case CMD_DELETE:
+ rslot = ExecProcessReturning(context,
+ resultRelInfo,
+ resultRelInfo->ri_oldTupleSlot,
+ context->planSlot);
+ break;
+
+ case CMD_NOTHING:
+ break;
+
+ default:
+ elog(ERROR, "unknown action in MERGE WHEN MATCHED clause");
+ }
+ }
+
/*
* We've activated one of the WHEN clauses, so we don't search
* further. This is required behaviour, not an optimization.
@@ -3103,19 +3182,22 @@ lmerge_matched:
/*
* Successfully executed an action or no qualifying action was found.
*/
- return true;
+ *matched = true;
+
+ return rslot;
}
/*
* Execute the first qualifying NOT MATCHED action.
*/
-static void
+static TupleTableSlot *
ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
bool canSetTag)
{
ModifyTableState *mtstate = context->mtstate;
ExprContext *econtext = mtstate->ps.ps_ExprContext;
List *actionStates = NIL;
+ TupleTableSlot *rslot = NULL;
ListCell *l;
/*
@@ -3167,8 +3249,8 @@ ExecMergeNotMatched(ModifyTableContext *
newslot = ExecProject(action->mas_proj);
context->relaction = action;
- (void) ExecInsert(context, mtstate->rootResultRelInfo, newslot,
- canSetTag, NULL, NULL);
+ rslot = ExecInsert(context, mtstate->rootResultRelInfo,
+ newslot, canSetTag, NULL, NULL);
mtstate->mt_merge_inserted += 1;
break;
case CMD_NOTHING:
@@ -3184,6 +3266,8 @@ ExecMergeNotMatched(ModifyTableContext *
*/
break;
}
+
+ return rslot;
}
/*
@@ -3361,6 +3445,44 @@ ExecInitMergeTupleSlots(ModifyTableState
}
/*
+ * pg_merge_action() -
+ * SQL merge support function to retrieve the currently executing merge
+ * action command string ("INSERT", "UPDATE", or "DELETE").
+ */
+Datum
+pg_merge_action(PG_FUNCTION_ARGS)
+{
+ switch (mergeActionCmdType)
+ {
+ case CMD_INSERT:
+ PG_RETURN_TEXT_P(cstring_to_text("INSERT"));
+ case CMD_UPDATE:
+ PG_RETURN_TEXT_P(cstring_to_text("UPDATE"));
+ case CMD_DELETE:
+ PG_RETURN_TEXT_P(cstring_to_text("DELETE"));
+ case CMD_NOTHING:
+ PG_RETURN_NULL();
+ default:
+ elog(ERROR, "unrecognized commandType: %d", (int) mergeActionCmdType);
+ }
+ PG_RETURN_NULL();
+}
+
+/*
+ * pg_merge_when_clause() -
+ * SQL merge support function to retrieve the 1-based index of the
+ * currently executing merge WHEN clause.
+ */
+Datum
+pg_merge_when_clause(PG_FUNCTION_ARGS)
+{
+ if (mergeActionIdx > 0)
+ PG_RETURN_INT32(mergeActionIdx);
+
+ PG_RETURN_NULL();
+}
+
+/*
* Process BEFORE EACH STATEMENT triggers
*/
static void
@@ -3615,6 +3737,9 @@ ExecModifyTable(PlanState *pstate)
context.planSlot = ExecProcNode(subplanstate);
+ /* Reset current MERGE action */
+ context.relaction = NULL;
+
/* No more tuples to process? */
if (TupIsNull(context.planSlot))
break;
@@ -3646,8 +3771,17 @@ ExecModifyTable(PlanState *pstate)
{
EvalPlanQualSetSlot(&node->mt_epqstate, context.planSlot);
- ExecMerge(&context, node->resultRelInfo, NULL, node->canSetTag);
- continue; /* no RETURNING support yet */
+ slot = ExecMerge(&context, node->resultRelInfo, NULL,
+ node->canSetTag);
+
+ /*
+ * If we got a RETURNING result, return it to the caller.
+ * We'll continue the work on next call.
+ */
+ if (slot)
+ return slot;
+
+ continue; /* continue with the next tuple */
}
elog(ERROR, "tableoid is NULL");
@@ -3674,7 +3808,8 @@ ExecModifyTable(PlanState *pstate)
* ExecProcessReturning by IterateDirectModify, so no need to
* provide it here.
*/
- slot = ExecProcessReturning(resultRelInfo, NULL, context.planSlot);
+ slot = ExecProcessReturning(&context, resultRelInfo, NULL,
+ context.planSlot);
return slot;
}
@@ -3724,8 +3859,17 @@ ExecModifyTable(PlanState *pstate)
{
EvalPlanQualSetSlot(&node->mt_epqstate, context.planSlot);
- ExecMerge(&context, node->resultRelInfo, NULL, node->canSetTag);
- continue; /* no RETURNING support yet */
+ slot = ExecMerge(&context, node->resultRelInfo, NULL,
+ node->canSetTag);
+
+ /*
+ * If we got a RETURNING result, return it to the
+ * caller. We'll continue the work on next call.
+ */
+ if (slot)
+ return slot;
+
+ continue; /* continue with the next tuple */
}
elog(ERROR, "ctid is NULL");
@@ -3817,7 +3961,6 @@ ExecModifyTable(PlanState *pstate)
}
slot = ExecGetUpdateNewTuple(resultRelInfo, context.planSlot,
oldSlot);
- context.relaction = NULL;
/* Now apply the update. */
slot = ExecUpdate(&context, resultRelInfo, tupleid, oldtuple,
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
new file mode 100644
index 3397568..b9f443c
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -2033,6 +2033,8 @@ SPI_result_code_string(int code)
return "SPI_OK_TD_REGISTER";
case SPI_OK_MERGE:
return "SPI_OK_MERGE";
+ case SPI_OK_MERGE_RETURNING:
+ return "SPI_OK_MERGE_RETURNING";
}
/* Unrecognized code ... return something useful ... */
sprintf(buf, "Unrecognized SPI code %d", code);
@@ -2886,7 +2888,10 @@ _SPI_pquery(QueryDesc *queryDesc, bool f
res = SPI_OK_UPDATE;
break;
case CMD_MERGE:
- res = SPI_OK_MERGE;
+ if (queryDesc->plannedstmt->hasReturning)
+ res = SPI_OK_MERGE_RETURNING;
+ else
+ res = SPI_OK_MERGE;
break;
default:
return SPI_ERROR_OPUNKNOWN;
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
new file mode 100644
index da2d8ab..2b898bf
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -18,6 +18,7 @@
#include "access/htup_details.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "executor/executor.h"
#include "miscadmin.h"
@@ -35,6 +36,7 @@
#include "parser/parse_relation.h"
#include "rewrite/rewriteManip.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
@@ -1847,7 +1849,8 @@ convert_EXISTS_to_ANY(PlannerInfo *root,
/*
* Replace correlation vars (uplevel vars) with Params.
*
- * Uplevel PlaceHolderVars and aggregates are replaced, too.
+ * Uplevel PlaceHolderVars, aggregates, GROUPING() expressions and merge
+ * support functions are replaced, too.
*
* Note: it is critical that this runs immediately after SS_process_sublinks.
* Since we do not recurse into the arguments of uplevel PHVs and aggregates,
@@ -1901,6 +1904,22 @@ replace_correlation_vars_mutator(Node *n
if (((GroupingFunc *) node)->agglevelsup > 0)
return (Node *) replace_outer_grouping(root, (GroupingFunc *) node);
}
+ if (IsA(node, FuncExpr))
+ {
+ if (IsMergeSupportFunction(((FuncExpr *) node)->funcid))
+ {
+ Param *param;
+
+ /*
+ * Replace with a Param, if it belongs to an upper-level MERGE
+ * query. Otherwise, leave it be.
+ */
+ param = replace_outer_merge_support_function(root,
+ (FuncExpr *) node);
+ if (param)
+ return (Node *) param;
+ }
+ }
return expression_tree_mutator(node,
replace_correlation_vars_mutator,
(void *) root);
diff --git a/src/backend/optimizer/util/paramassign.c b/src/backend/optimizer/util/paramassign.c
new file mode 100644
index d6a923b..2b7b8d1
--- a/src/backend/optimizer/util/paramassign.c
+++ b/src/backend/optimizer/util/paramassign.c
@@ -307,6 +307,63 @@ replace_outer_grouping(PlannerInfo *root
}
/*
+ * Generate a Param node to replace the given FuncExpr expression, which is
+ * expected to be a merge support function, if it belongs to an upper-level
+ * MERGE query. Otherwise NULL will be returned.
+ *
+ * A NULL return value indicates either that the merge support function
+ * belongs to a MERGE query at this query level, or that there is no MERGE
+ * query at all. In both cases, the caller is expected to leave the FuncExpr
+ * node unaltered, so that it is executed at this query level (in the latter
+ * case, it is up to the merge support function to decide how to handle being
+ * called outside of a MERGE query).
+ */
+Param *
+replace_outer_merge_support_function(PlannerInfo *root, FuncExpr *func)
+{
+ Param *retval;
+ PlannerParamItem *pitem;
+ Index levelsup;
+ Oid ptype = exprType((Node *) func);
+
+ /* Find the upper-level MERGE query the function belongs to */
+ levelsup = 0;
+ while (root->parse->commandType != CMD_MERGE)
+ {
+ root = root->parent_root;
+ if (root == NULL)
+ return NULL; /* Not in a MERGE query */
+ levelsup++;
+ }
+ if (levelsup == 0)
+ return NULL; /* Local MERGE query at this level */
+
+ /*
+ * It does not seem worthwhile to try to de-duplicate references to outer
+ * merge support functions. Just make a new slot every time.
+ */
+ func = copyObject(func);
+
+ pitem = makeNode(PlannerParamItem);
+ pitem->item = (Node *) func;
+ pitem->paramId = list_length(root->glob->paramExecTypes);
+ root->glob->paramExecTypes = lappend_oid(root->glob->paramExecTypes,
+ ptype);
+
+ root->plan_params = lappend(root->plan_params, pitem);
+
+ retval = makeNode(Param);
+ retval->paramkind = PARAM_EXEC;
+ retval->paramid = pitem->paramId;
+ retval->paramtype = ptype;
+ retval->paramtypmod = -1;
+ retval->paramcollid = InvalidOid;
+ retval->location = func->location;
+
+ return retval;
+}
+
+/*
* Generate a Param node to replace the given Var,
* which is expected to come from some upper NestLoop plan node.
* Record the need for the Var in root->curOuterParams.
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
new file mode 100644
index 4006632..d6eafab
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -74,7 +74,6 @@ static void determineRecursiveColTypes(P
Node *larg, List *nrtargetlist);
static Query *transformReturnStmt(ParseState *pstate, ReturnStmt *stmt);
static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
-static List *transformReturningList(ParseState *pstate, List *returningList);
static Query *transformPLAssignStmt(ParseState *pstate,
PLAssignStmt *stmt);
static Query *transformDeclareCursorStmt(ParseState *pstate,
@@ -2501,9 +2500,9 @@ transformUpdateTargetList(ParseState *ps
/*
* transformReturningList -
- * handle a RETURNING clause in INSERT/UPDATE/DELETE
+ * handle a RETURNING clause in INSERT/UPDATE/DELETE/MERGE
*/
-static List *
+List *
transformReturningList(ParseState *pstate, List *returningList)
{
List *rlist;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index 39ab7ea..5455607
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -12249,6 +12249,7 @@ MergeStmt:
USING table_ref
ON a_expr
merge_when_list
+ returning_clause
{
MergeStmt *m = makeNode(MergeStmt);
@@ -12257,6 +12258,7 @@ MergeStmt:
m->sourceRelation = $6;
m->joinCondition = $8;
m->mergeWhenClauses = $9;
+ m->returningList = $10;
$$ = (Node *) m;
}
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
new file mode 100644
index c5b1a49..ad3c525
--- a/src/backend/parser/parse_cte.c
+++ b/src/backend/parser/parse_cte.c
@@ -126,13 +126,6 @@ transformWithClause(ParseState *pstate,
CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
ListCell *rest;
- /* MERGE is allowed by parser, but unimplemented. Reject for now */
- if (IsA(cte->ctequery, MergeStmt))
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("MERGE not supported in WITH query"),
- parser_errposition(pstate, cte->location));
-
for_each_cell(rest, withClause->ctes, lnext(withClause->ctes, lc))
{
CommonTableExpr *cte2 = (CommonTableExpr *) lfirst(rest);
@@ -153,7 +146,8 @@ transformWithClause(ParseState *pstate,
/* must be a data-modifying statement */
Assert(IsA(cte->ctequery, InsertStmt) ||
IsA(cte->ctequery, UpdateStmt) ||
- IsA(cte->ctequery, DeleteStmt));
+ IsA(cte->ctequery, DeleteStmt) ||
+ IsA(cte->ctequery, MergeStmt));
pstate->p_hasModifyingCTE = true;
}
diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c
new file mode 100644
index 91b1156..4427f75
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -233,6 +233,9 @@ transformMergeStmt(ParseState *pstate, M
*/
qry->jointree = makeFromExpr(pstate->p_joinlist, joinExpr);
+ /* Transform the RETURNING list, if any */
+ qry->returningList = transformReturningList(pstate, stmt->returningList);
+
/*
* We now have a good query shape, so now look at the WHEN conditions and
* action targetlists.
@@ -254,6 +257,7 @@ transformMergeStmt(ParseState *pstate, M
MergeAction *action;
action = makeNode(MergeAction);
+ action->index = foreach_current_index(l) + 1;
action->commandType = mergeWhenClause->commandType;
action->matched = mergeWhenClause->matched;
@@ -390,9 +394,6 @@ transformMergeStmt(ParseState *pstate, M
qry->mergeActionList = mergeActionList;
- /* RETURNING could potentially be added in the future, but not in SQL std */
- qry->returningList = NULL;
-
qry->hasTargetSRFs = false;
qry->hasSubLinks = pstate->p_hasSubLinks;
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
new file mode 100644
index 41d6049..794530b
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -2345,9 +2345,10 @@ addRangeTableEntryForCTE(ParseState *pst
cte->cterefcount++;
/*
- * We throw error if the CTE is INSERT/UPDATE/DELETE without RETURNING.
- * This won't get checked in case of a self-reference, but that's OK
- * because data-modifying CTEs aren't allowed to be recursive anyhow.
+ * We throw error if the CTE is INSERT/UPDATE/DELETE/MERGE without
+ * RETURNING. This won't get checked in case of a self-reference, but
+ * that's OK because data-modifying CTEs aren't allowed to be recursive
+ * anyhow.
*/
if (IsA(cte->ctequery, Query))
{
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
new file mode 100644
index b486ab5..1bd0ce2
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3629,9 +3629,9 @@ RewriteQuery(Query *parsetree, List *rew
ListCell *lc1;
/*
- * First, recursively process any insert/update/delete statements in WITH
- * clauses. (We have to do this first because the WITH clauses may get
- * copied into rule actions below.)
+ * First, recursively process any insert/update/delete/merge statements in
+ * WITH clauses. (We have to do this first because the WITH clauses may
+ * get copied into rule actions below.)
*/
foreach(lc1, parsetree->cteList)
{
@@ -3656,7 +3656,8 @@ RewriteQuery(Query *parsetree, List *rew
if (!(ctequery->commandType == CMD_SELECT ||
ctequery->commandType == CMD_UPDATE ||
ctequery->commandType == CMD_INSERT ||
- ctequery->commandType == CMD_DELETE))
+ ctequery->commandType == CMD_DELETE ||
+ ctequery->commandType == CMD_MERGE))
{
/*
* Currently it could only be NOTIFY; this error message will
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
new file mode 100644
index 30b51bf..0ae3890
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -2141,11 +2141,10 @@ QueryReturnsTuples(Query *parsetree)
case CMD_SELECT:
/* returns tuples */
return true;
- case CMD_MERGE:
- return false;
case CMD_INSERT:
case CMD_UPDATE:
case CMD_DELETE:
+ case CMD_MERGE:
/* the forms with RETURNING return tuples */
if (parsetree->returningList)
return true;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
new file mode 100644
index d3a973d..f097d98
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -7183,8 +7183,13 @@ get_merge_query_def(Query *query, depars
appendStringInfoString(buf, "DO NOTHING");
}
- /* No RETURNING support in MERGE yet */
- Assert(query->returningList == NIL);
+ /* Add RETURNING if present */
+ if (query->returningList)
+ {
+ appendContextKeyword(context, " RETURNING",
+ -PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
+ get_target_list(query->returningList, context, NULL, colNamesVisible);
+ }
}
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
new file mode 100644
index 5973df2..a1380f8
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -982,13 +982,17 @@ PrintQueryResult(PGresult *result, bool
else
success = true;
- /* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
+ /*
+ * If it's INSERT/UPDATE/DELETE/MERGE RETURNING, also print
+ * status.
+ */
if (last || pset.show_all_results)
{
cmdstatus = PQcmdStatus(result);
if (strncmp(cmdstatus, "INSERT", 6) == 0 ||
strncmp(cmdstatus, "UPDATE", 6) == 0 ||
- strncmp(cmdstatus, "DELETE", 6) == 0)
+ strncmp(cmdstatus, "DELETE", 6) == 0 ||
+ strncmp(cmdstatus, "MERGE", 5) == 0)
PrintQueryStatus(result, printStatusFout);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
new file mode 100644
index 6996073..46c36af
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12043,4 +12043,14 @@
proname => 'any_value_transfn', prorettype => 'anyelement',
proargtypes => 'anyelement anyelement', prosrc => 'any_value_transfn' },
+# MERGE support functions
+{ oid => '9499', descr => 'command type of current MERGE action',
+ proname => 'pg_merge_action', provolatile => 'v', proparallel => 'r',
+ prorettype => 'text', proargtypes => '',
+ prosrc => 'pg_merge_action' },
+{ oid => '9500', descr => 'index of current MERGE WHEN clause',
+ proname => 'pg_merge_when_clause', provolatile => 'v', proparallel => 'r',
+ prorettype => 'int4', proargtypes => '',
+ prosrc => 'pg_merge_when_clause' },
+
]
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
new file mode 100644
index e7abe0b..463add8
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -182,6 +182,11 @@ DECLARE_UNIQUE_INDEX(pg_proc_proname_arg
#define PROARGMODE_VARIADIC 'v'
#define PROARGMODE_TABLE 't'
+/* Is this a merge support function? (Requires fmgroids.h) */
+#define IsMergeSupportFunction(funcid) \
+ ((funcid) == F_PG_MERGE_ACTION || \
+ (funcid) == F_PG_MERGE_WHEN_CLAUSE)
+
#endif /* EXPOSE_TO_CLIENT_CODE */
diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h
new file mode 100644
index d1de139..ed8787d
--- a/src/include/executor/spi.h
+++ b/src/include/executor/spi.h
@@ -97,6 +97,7 @@ typedef struct _SPI_plan *SPIPlanPtr;
#define SPI_OK_REL_UNREGISTER 16
#define SPI_OK_TD_REGISTER 17
#define SPI_OK_MERGE 18
+#define SPI_OK_MERGE_RETURNING 19
#define SPI_OPT_NONATOMIC (1 << 0)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
new file mode 100644
index 88b03cc..79511e9
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1689,6 +1689,7 @@ typedef struct MergeWhenClause
typedef struct MergeAction
{
NodeTag type;
+ int index; /* 1-based index of the clause */
bool matched; /* true=MATCHED, false=NOT MATCHED */
CmdType commandType; /* INSERT/UPDATE/DELETE/DO NOTHING */
/* OVERRIDING clause */
@@ -1915,6 +1916,7 @@ typedef struct MergeStmt
Node *sourceRelation; /* source relation */
Node *joinCondition; /* join condition between source and target */
List *mergeWhenClauses; /* list of MergeWhenClause(es) */
+ List *returningList; /* list of expressions to return */
WithClause *withClause; /* WITH clause */
} MergeStmt;
diff --git a/src/include/optimizer/paramassign.h b/src/include/optimizer/paramassign.h
new file mode 100644
index 55c27a6..6f70c78
--- a/src/include/optimizer/paramassign.h
+++ b/src/include/optimizer/paramassign.h
@@ -20,6 +20,8 @@ extern Param *replace_outer_placeholderv
PlaceHolderVar *phv);
extern Param *replace_outer_agg(PlannerInfo *root, Aggref *agg);
extern Param *replace_outer_grouping(PlannerInfo *root, GroupingFunc *grp);
+extern Param *replace_outer_merge_support_function(PlannerInfo *root,
+ FuncExpr *func);
extern Param *replace_nestloop_param_var(PlannerInfo *root, Var *var);
extern Param *replace_nestloop_param_placeholdervar(PlannerInfo *root,
PlaceHolderVar *phv);
diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h
new file mode 100644
index 1cef183..fd5848d
--- a/src/include/parser/analyze.h
+++ b/src/include/parser/analyze.h
@@ -44,6 +44,7 @@ extern List *transformInsertRow(ParseSta
bool strip_indirection);
extern List *transformUpdateTargetList(ParseState *pstate,
List *origTlist);
+extern List *transformReturningList(ParseState *pstate, List *returningList);
extern Query *transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree);
extern Query *transformStmt(ParseState *pstate, Node *parseTree);
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
new file mode 100644
index 4b76f76..fb6289c
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -4267,9 +4267,9 @@ exec_stmt_execsql(PLpgSQL_execstate *est
/*
* If we have INTO, then we only need one row back ... but if we have INTO
* STRICT or extra check too_many_rows, ask for two rows, so that we can
- * verify the statement returns only one. INSERT/UPDATE/DELETE are always
- * treated strictly. Without INTO, just run the statement to completion
- * (tcount = 0).
+ * verify the statement returns only one. INSERT/UPDATE/DELETE/MERGE are
+ * always treated strictly. Without INTO, just run the statement to
+ * completion (tcount = 0).
*
* We could just ask for two rows always when using INTO, but there are
* some cases where demanding the extra row costs significant time, eg by
@@ -4307,10 +4307,11 @@ exec_stmt_execsql(PLpgSQL_execstate *est
case SPI_OK_INSERT:
case SPI_OK_UPDATE:
case SPI_OK_DELETE:
+ case SPI_OK_MERGE:
case SPI_OK_INSERT_RETURNING:
case SPI_OK_UPDATE_RETURNING:
case SPI_OK_DELETE_RETURNING:
- case SPI_OK_MERGE:
+ case SPI_OK_MERGE_RETURNING:
Assert(stmt->mod_stmt);
exec_set_found(estate, (SPI_processed != 0));
break;
@@ -4489,10 +4490,11 @@ exec_stmt_dynexecute(PLpgSQL_execstate *
case SPI_OK_INSERT:
case SPI_OK_UPDATE:
case SPI_OK_DELETE:
+ case SPI_OK_MERGE:
case SPI_OK_INSERT_RETURNING:
case SPI_OK_UPDATE_RETURNING:
case SPI_OK_DELETE_RETURNING:
- case SPI_OK_MERGE:
+ case SPI_OK_MERGE_RETURNING:
case SPI_OK_UTILITY:
case SPI_OK_REWRITTEN:
break;
diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c
new file mode 100644
index e8f9d7b..0c9f81e
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -2456,6 +2456,7 @@ pltcl_process_SPI_result(Tcl_Interp *int
case SPI_OK_INSERT_RETURNING:
case SPI_OK_DELETE_RETURNING:
case SPI_OK_UPDATE_RETURNING:
+ case SPI_OK_MERGE_RETURNING:
/*
* Process the tuples we got
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
new file mode 100644
index 133d421..e2cab70
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -123,20 +123,20 @@ ON tid = tid
WHEN MATCHED THEN DO NOTHING;
ERROR: name "target" specified more than once
DETAIL: The name is used both as MERGE target table and data source.
--- used in a CTE
+-- used in a CTE without RETURNING
WITH foo AS (
MERGE INTO target USING source ON (true)
WHEN MATCHED THEN DELETE
) SELECT * FROM foo;
-ERROR: MERGE not supported in WITH query
-LINE 1: WITH foo AS (
- ^
--- used in COPY
+ERROR: WITH query "foo" does not have a RETURNING clause
+LINE 4: ) SELECT * FROM foo;
+ ^
+-- used in COPY without RETURNING
COPY (
MERGE INTO target USING source ON (true)
WHEN MATCHED THEN DELETE
) TO stdout;
-ERROR: MERGE not supported in COPY
+ERROR: COPY query must have a RETURNING clause
-- unsupported relation types
-- view
CREATE VIEW tv AS SELECT * FROM target;
@@ -1304,20 +1304,184 @@ WHEN MATCHED AND tid < 2 THEN
ROLLBACK;
-- RETURNING
BEGIN;
-INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+CREATE TABLE merge_actions(action text, abbrev text);
+INSERT INTO merge_actions VALUES ('INSERT', 'ins'), ('UPDATE', 'upd'), ('DELETE', 'del');
MERGE INTO sq_target t
-USING v
+USING sq_source s
ON tid = sid
-WHEN MATCHED AND tid > 2 THEN
+WHEN MATCHED AND tid >= 2 THEN
UPDATE SET balance = t.balance + delta
WHEN NOT MATCHED THEN
- INSERT (balance, tid) VALUES (balance + delta, sid)
+ INSERT (balance, tid) VALUES (balance + delta, sid)
WHEN MATCHED AND tid < 2 THEN
- DELETE
-RETURNING *;
-ERROR: syntax error at or near "RETURNING"
-LINE 10: RETURNING *;
- ^
+ DELETE
+RETURNING pg_merge_when_clause() AS when_clause,
+ (SELECT abbrev FROM merge_actions
+ WHERE action = pg_merge_action()) AS merge_action,
+ t.*,
+ CASE pg_merge_action()
+ WHEN 'INSERT' THEN 'Inserted '||t
+ WHEN 'UPDATE' THEN 'Added '||delta||' to balance'
+ WHEN 'DELETE' THEN 'Removed '||t
+ END AS description;
+ when_clause | merge_action | tid | balance | description
+-------------+--------------+-----+---------+---------------------
+ 3 | del | 1 | 100 | Removed (1,100)
+ 1 | upd | 2 | 220 | Added 20 to balance
+ 2 | ins | 4 | 40 | Inserted (4,40)
+(3 rows)
+
+ROLLBACK;
+-- RETURNING in CTEs
+CREATE TABLE sq_target_merge_log (tid integer NOT NULL, last_change text);
+INSERT INTO sq_target_merge_log VALUES (1, 'Original value');
+BEGIN;
+WITH m AS (
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING pg_merge_when_clause() AS when_clause,
+ pg_merge_action() AS merge_action,
+ t.*,
+ CASE pg_merge_action()
+ WHEN 'INSERT' THEN 'Inserted '||t
+ WHEN 'UPDATE' THEN 'Added '||delta||' to balance'
+ WHEN 'DELETE' THEN 'Removed '||t
+ END AS description
+), m2 AS (
+ MERGE INTO sq_target_merge_log l
+ USING m
+ ON l.tid = m.tid
+ WHEN MATCHED THEN
+ UPDATE SET last_change = description
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (m.tid, description)
+ RETURNING merge_action, pg_merge_action() AS merge_log_action, l.*
+)
+SELECT * FROM m2;
+ merge_action | merge_log_action | tid | last_change
+--------------+------------------+-----+---------------------
+ DELETE | UPDATE | 1 | Removed (1,100)
+ UPDATE | INSERT | 2 | Added 20 to balance
+ INSERT | INSERT | 4 | Inserted (4,40)
+(3 rows)
+
+SELECT * FROM sq_target_merge_log ORDER BY tid;
+ tid | last_change
+-----+---------------------
+ 1 | Removed (1,100)
+ 2 | Added 20 to balance
+ 4 | Inserted (4,40)
+(3 rows)
+
+ROLLBACK;
+-- COPY (MERGE ... RETURNING) TO ...
+BEGIN;
+COPY (
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING pg_merge_action(), t.*
+) TO stdout;
+DELETE 1 100
+UPDATE 2 220
+INSERT 4 40
+ROLLBACK;
+-- SQL function with MERGE ... RETURNING
+BEGIN;
+CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int,
+ OUT action text, OUT tid int, OUT new_balance int)
+LANGUAGE sql AS
+$$
+ MERGE INTO sq_target t
+ USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta)
+ ON tid = v.sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + v.delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING pg_merge_action(), t.*;
+$$;
+SELECT m.*
+FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta),
+LATERAL (SELECT action, tid, new_balance FROM merge_into_sq_target(sid, balance, delta)) m;
+ action | tid | new_balance
+--------+-----+-------------
+ DELETE | 1 | 100
+ UPDATE | 3 | 320
+ INSERT | 4 | 110
+(3 rows)
+
+ROLLBACK;
+-- SQL SRF with MERGE ... RETURNING
+BEGIN;
+CREATE FUNCTION merge_sq_source_into_sq_target()
+RETURNS TABLE (action text, tid int, balance int)
+LANGUAGE sql AS
+$$
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING pg_merge_action(), t.*;
+$$;
+SELECT * FROM merge_sq_source_into_sq_target();
+ action | tid | balance
+--------+-----+---------
+ DELETE | 1 | 100
+ UPDATE | 2 | 220
+ INSERT | 4 | 40
+(3 rows)
+
+ROLLBACK;
+-- PL/pgSQL function with MERGE ... RETURNING ... INTO
+BEGIN;
+CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int,
+ OUT r_action text, OUT r_tid int, OUT r_balance int)
+LANGUAGE plpgsql AS
+$$
+BEGIN
+ MERGE INTO sq_target t
+ USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta)
+ ON tid = v.sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + v.delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING pg_merge_action(), t.* INTO r_action, r_tid, r_balance;
+END;
+$$;
+SELECT m.*
+FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta),
+LATERAL (SELECT r_action, r_tid, r_balance FROM merge_into_sq_target(sid, balance, delta)) m;
+ r_action | r_tid | r_balance
+----------+-------+-----------
+ DELETE | 1 | 100
+ UPDATE | 3 | 320
+ INSERT | 4 | 110
+(3 rows)
+
ROLLBACK;
-- EXPLAIN
CREATE TABLE ex_mtarget (a int, b int)
@@ -1514,7 +1678,7 @@ SELECT * FROM sq_target WHERE tid = 1;
(1 row)
ROLLBACK;
-DROP TABLE sq_target, sq_source CASCADE;
+DROP TABLE sq_target, sq_target_merge_log, sq_source CASCADE;
NOTICE: drop cascades to view v
CREATE TABLE pa_target (tid integer, balance float, val text)
PARTITION BY LIST (tid);
@@ -1640,6 +1804,32 @@ SELECT * FROM pa_target ORDER BY tid;
(14 rows)
ROLLBACK;
+-- update partition key to partition not initially scanned
+BEGIN;
+MERGE INTO pa_target t
+ USING pa_source s
+ ON t.tid = s.sid AND t.tid = 1
+ WHEN MATCHED THEN
+ UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
+ RETURNING pg_merge_action(), pg_merge_when_clause(), t.*;
+ pg_merge_action | pg_merge_when_clause | tid | balance | val
+-----------------+----------------------+-----+---------+--------------------------
+ UPDATE | 1 | 2 | 110 | initial updated by merge
+(1 row)
+
+SELECT * FROM pa_target ORDER BY tid;
+ tid | balance | val
+-----+---------+--------------------------
+ 2 | 110 | initial updated by merge
+ 3 | 300 | initial
+ 5 | 500 | initial
+ 7 | 700 | initial
+ 9 | 900 | initial
+ 11 | 1100 | initial
+ 13 | 1300 | initial
+(7 rows)
+
+ROLLBACK;
DROP TABLE pa_target CASCADE;
-- The target table is partitioned in the same way, but this time by attaching
-- partitions which have columns in different order, dropped columns etc.
@@ -1731,7 +1921,26 @@ MERGE INTO pa_target t
WHEN MATCHED THEN
UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
WHEN NOT MATCHED THEN
- INSERT VALUES (sid, delta, 'inserted by merge');
+ INSERT VALUES (sid, delta, 'inserted by merge')
+ RETURNING pg_merge_action(), pg_merge_when_clause(), t.*;
+ pg_merge_action | pg_merge_when_clause | tid | balance | val
+-----------------+----------------------+-----+---------+--------------------------
+ UPDATE | 1 | 2 | 110 | initial updated by merge
+ INSERT | 2 | 2 | 20 | inserted by merge
+ UPDATE | 1 | 4 | 330 | initial updated by merge
+ INSERT | 2 | 4 | 40 | inserted by merge
+ UPDATE | 1 | 6 | 550 | initial updated by merge
+ INSERT | 2 | 6 | 60 | inserted by merge
+ UPDATE | 1 | 8 | 770 | initial updated by merge
+ INSERT | 2 | 8 | 80 | inserted by merge
+ UPDATE | 1 | 10 | 990 | initial updated by merge
+ INSERT | 2 | 10 | 100 | inserted by merge
+ UPDATE | 1 | 12 | 1210 | initial updated by merge
+ INSERT | 2 | 12 | 120 | inserted by merge
+ UPDATE | 1 | 14 | 1430 | initial updated by merge
+ INSERT | 2 | 14 | 140 | inserted by merge
+(14 rows)
+
SELECT * FROM pa_target ORDER BY tid;
tid | balance | val
-----+---------+--------------------------
@@ -1798,7 +2007,21 @@ MERGE INTO pa_target t
WHEN MATCHED THEN
UPDATE SET balance = balance + delta, val = val || ' updated by merge'
WHEN NOT MATCHED THEN
- INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge');
+ INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge')
+ RETURNING pg_merge_action(), pg_merge_when_clause(), t.*;
+ pg_merge_action | pg_merge_when_clause | logts | tid | balance | val
+-----------------+----------------------+--------------------------+-----+---------+--------------------------
+ UPDATE | 1 | Tue Jan 31 00:00:00 2017 | 1 | 110 | initial updated by merge
+ UPDATE | 1 | Tue Feb 28 00:00:00 2017 | 2 | 220 | initial updated by merge
+ INSERT | 2 | Sun Jan 15 00:00:00 2017 | 3 | 30 | inserted by merge
+ UPDATE | 1 | Tue Jan 31 00:00:00 2017 | 4 | 440 | initial updated by merge
+ UPDATE | 1 | Tue Feb 28 00:00:00 2017 | 5 | 550 | initial updated by merge
+ INSERT | 2 | Sun Jan 15 00:00:00 2017 | 6 | 60 | inserted by merge
+ UPDATE | 1 | Tue Jan 31 00:00:00 2017 | 7 | 770 | initial updated by merge
+ UPDATE | 1 | Tue Feb 28 00:00:00 2017 | 8 | 880 | initial updated by merge
+ INSERT | 2 | Sun Jan 15 00:00:00 2017 | 9 | 90 | inserted by merge
+(9 rows)
+
SELECT * FROM pa_target ORDER BY tid;
logts | tid | balance | val
--------------------------+-----+---------+--------------------------
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
new file mode 100644
index 7fd81e6..87a3686
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3590,7 +3590,8 @@ MERGE INTO rule_merge2 t USING (SELECT 1
-- test deparsing
CREATE TABLE sf_target(id int, data text, filling int[]);
CREATE FUNCTION merge_sf_test()
- RETURNS void
+ RETURNS TABLE(pg_merge_action text, when_clause int,
+ a int, b text, id int, data text, filling int[])
LANGUAGE sql
BEGIN ATOMIC
MERGE INTO sf_target t
@@ -3627,11 +3628,13 @@ WHEN NOT MATCHED
VALUES (s.a, s.b, DEFAULT)
WHEN NOT MATCHED
THEN INSERT (filling[1], id)
- VALUES (s.a, s.a);
+ VALUES (s.a, s.a)
+RETURNING
+ pg_merge_action(), pg_merge_when_clause() AS when_clause, *;
END;
\sf merge_sf_test
CREATE OR REPLACE FUNCTION public.merge_sf_test()
- RETURNS void
+ RETURNS TABLE(pg_merge_action text, when_clause integer, a integer, b text, id integer, data text, filling integer[])
LANGUAGE sql
BEGIN ATOMIC
MERGE INTO sf_target t
@@ -3668,7 +3671,14 @@ BEGIN ATOMIC
VALUES (s.a, s.b, DEFAULT)
WHEN NOT MATCHED
THEN INSERT (filling[1], id)
- VALUES (s.a, s.a);
+ VALUES (s.a, s.a)
+ RETURNING pg_merge_action() AS pg_merge_action,
+ pg_merge_when_clause() AS when_clause,
+ s.a,
+ s.b,
+ t.id,
+ t.data,
+ t.filling;
END
DROP FUNCTION merge_sf_test;
DROP TABLE sf_target;
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
new file mode 100644
index 4cf6db9..6dfe4da
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -86,12 +86,12 @@ MERGE INTO target
USING target
ON tid = tid
WHEN MATCHED THEN DO NOTHING;
--- used in a CTE
+-- used in a CTE without RETURNING
WITH foo AS (
MERGE INTO target USING source ON (true)
WHEN MATCHED THEN DELETE
) SELECT * FROM foo;
--- used in COPY
+-- used in COPY without RETURNING
COPY (
MERGE INTO target USING source ON (true)
WHEN MATCHED THEN DELETE
@@ -857,17 +857,144 @@ ROLLBACK;
-- RETURNING
BEGIN;
-INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+CREATE TABLE merge_actions(action text, abbrev text);
+INSERT INTO merge_actions VALUES ('INSERT', 'ins'), ('UPDATE', 'upd'), ('DELETE', 'del');
MERGE INTO sq_target t
-USING v
+USING sq_source s
ON tid = sid
-WHEN MATCHED AND tid > 2 THEN
+WHEN MATCHED AND tid >= 2 THEN
UPDATE SET balance = t.balance + delta
WHEN NOT MATCHED THEN
- INSERT (balance, tid) VALUES (balance + delta, sid)
+ INSERT (balance, tid) VALUES (balance + delta, sid)
WHEN MATCHED AND tid < 2 THEN
- DELETE
-RETURNING *;
+ DELETE
+RETURNING pg_merge_when_clause() AS when_clause,
+ (SELECT abbrev FROM merge_actions
+ WHERE action = pg_merge_action()) AS merge_action,
+ t.*,
+ CASE pg_merge_action()
+ WHEN 'INSERT' THEN 'Inserted '||t
+ WHEN 'UPDATE' THEN 'Added '||delta||' to balance'
+ WHEN 'DELETE' THEN 'Removed '||t
+ END AS description;
+ROLLBACK;
+
+-- RETURNING in CTEs
+CREATE TABLE sq_target_merge_log (tid integer NOT NULL, last_change text);
+INSERT INTO sq_target_merge_log VALUES (1, 'Original value');
+BEGIN;
+WITH m AS (
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING pg_merge_when_clause() AS when_clause,
+ pg_merge_action() AS merge_action,
+ t.*,
+ CASE pg_merge_action()
+ WHEN 'INSERT' THEN 'Inserted '||t
+ WHEN 'UPDATE' THEN 'Added '||delta||' to balance'
+ WHEN 'DELETE' THEN 'Removed '||t
+ END AS description
+), m2 AS (
+ MERGE INTO sq_target_merge_log l
+ USING m
+ ON l.tid = m.tid
+ WHEN MATCHED THEN
+ UPDATE SET last_change = description
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (m.tid, description)
+ RETURNING merge_action, pg_merge_action() AS merge_log_action, l.*
+)
+SELECT * FROM m2;
+SELECT * FROM sq_target_merge_log ORDER BY tid;
+ROLLBACK;
+
+-- COPY (MERGE ... RETURNING) TO ...
+BEGIN;
+COPY (
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING pg_merge_action(), t.*
+) TO stdout;
+ROLLBACK;
+
+-- SQL function with MERGE ... RETURNING
+BEGIN;
+CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int,
+ OUT action text, OUT tid int, OUT new_balance int)
+LANGUAGE sql AS
+$$
+ MERGE INTO sq_target t
+ USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta)
+ ON tid = v.sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + v.delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING pg_merge_action(), t.*;
+$$;
+SELECT m.*
+FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta),
+LATERAL (SELECT action, tid, new_balance FROM merge_into_sq_target(sid, balance, delta)) m;
+ROLLBACK;
+
+-- SQL SRF with MERGE ... RETURNING
+BEGIN;
+CREATE FUNCTION merge_sq_source_into_sq_target()
+RETURNS TABLE (action text, tid int, balance int)
+LANGUAGE sql AS
+$$
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING pg_merge_action(), t.*;
+$$;
+SELECT * FROM merge_sq_source_into_sq_target();
+ROLLBACK;
+
+-- PL/pgSQL function with MERGE ... RETURNING ... INTO
+BEGIN;
+CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int,
+ OUT r_action text, OUT r_tid int, OUT r_balance int)
+LANGUAGE plpgsql AS
+$$
+BEGIN
+ MERGE INTO sq_target t
+ USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta)
+ ON tid = v.sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + v.delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING pg_merge_action(), t.* INTO r_action, r_tid, r_balance;
+END;
+$$;
+SELECT m.*
+FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta),
+LATERAL (SELECT r_action, r_tid, r_balance FROM merge_into_sq_target(sid, balance, delta)) m;
ROLLBACK;
-- EXPLAIN
@@ -966,7 +1093,7 @@ WHEN MATCHED THEN
SELECT * FROM sq_target WHERE tid = 1;
ROLLBACK;
-DROP TABLE sq_target, sq_source CASCADE;
+DROP TABLE sq_target, sq_target_merge_log, sq_source CASCADE;
CREATE TABLE pa_target (tid integer, balance float, val text)
PARTITION BY LIST (tid);
@@ -1033,6 +1160,17 @@ SELECT merge_func();
SELECT * FROM pa_target ORDER BY tid;
ROLLBACK;
+-- update partition key to partition not initially scanned
+BEGIN;
+MERGE INTO pa_target t
+ USING pa_source s
+ ON t.tid = s.sid AND t.tid = 1
+ WHEN MATCHED THEN
+ UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
+ RETURNING pg_merge_action(), pg_merge_when_clause(), t.*;
+SELECT * FROM pa_target ORDER BY tid;
+ROLLBACK;
+
DROP TABLE pa_target CASCADE;
-- The target table is partitioned in the same way, but this time by attaching
@@ -1091,7 +1229,8 @@ MERGE INTO pa_target t
WHEN MATCHED THEN
UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
WHEN NOT MATCHED THEN
- INSERT VALUES (sid, delta, 'inserted by merge');
+ INSERT VALUES (sid, delta, 'inserted by merge')
+ RETURNING pg_merge_action(), pg_merge_when_clause(), t.*;
SELECT * FROM pa_target ORDER BY tid;
ROLLBACK;
@@ -1145,7 +1284,8 @@ MERGE INTO pa_target t
WHEN MATCHED THEN
UPDATE SET balance = balance + delta, val = val || ' updated by merge'
WHEN NOT MATCHED THEN
- INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge');
+ INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge')
+ RETURNING pg_merge_action(), pg_merge_when_clause(), t.*;
SELECT * FROM pa_target ORDER BY tid;
ROLLBACK;
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
new file mode 100644
index 8b7e255..a33515b
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1281,7 +1281,8 @@ MERGE INTO rule_merge2 t USING (SELECT 1
CREATE TABLE sf_target(id int, data text, filling int[]);
CREATE FUNCTION merge_sf_test()
- RETURNS void
+ RETURNS TABLE(pg_merge_action text, when_clause int,
+ a int, b text, id int, data text, filling int[])
LANGUAGE sql
BEGIN ATOMIC
MERGE INTO sf_target t
@@ -1318,7 +1319,9 @@ WHEN NOT MATCHED
VALUES (s.a, s.b, DEFAULT)
WHEN NOT MATCHED
THEN INSERT (filling[1], id)
- VALUES (s.a, s.a);
+ VALUES (s.a, s.a)
+RETURNING
+ pg_merge_action(), pg_merge_when_clause() AS when_clause, *;
END;
\sf merge_sf_test
