begriffs open source - ai-pg/blob - full-docs/src/sgml/html/functions-merge-support.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.23. Merge Support Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-window.html" title="9.22. Window Functions" /><link rel="next" href="functions-subquery.html" title="9.24. Subquery Expressions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.23. Merge Support Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-window.html" title="9.22. Window Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-subquery.html" title="9.24. Subquery Expressions">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-MERGE-SUPPORT"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.23. Merge Support Functions <a href="#FUNCTIONS-MERGE-SUPPORT" class="id_link">#</a></h2></div></div></div><a id="id-1.5.8.29.2" class="indexterm"></a><p>
   3    <span class="productname">PostgreSQL</span> includes one merge support function
   4    that may be used in the <code class="literal">RETURNING</code> list of a
   5    <a class="xref" href="sql-merge.html" title="MERGE"><span class="refentrytitle">MERGE</span></a> command to identify the action taken for each
   6    row; see <a class="xref" href="functions-merge-support.html#FUNCTIONS-MERGE-SUPPORT-TABLE" title="Table 9.68. Merge Support Functions">Table 9.68</a>.
   7   </p><div class="table" id="FUNCTIONS-MERGE-SUPPORT-TABLE"><p class="title"><strong>Table 9.68. Merge Support Functions</strong></p><div class="table-contents"><table class="table" summary="Merge Support Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
   8        Function
   9       </p>
  10       <p>
  11        Description
  12       </p></th></tr></thead><tbody><tr><td id="MERGE-ACTION" class="func_table_entry"><p class="func_signature">
  13        <a id="id-1.5.8.29.4.2.2.1.1.1.1" class="indexterm"></a>
  14        <code class="function">merge_action</code> ( )
  15        → <code class="returnvalue">text</code>
  16       </p>
  17       <p>
  18        Returns the merge action command executed for the current row.  This
  19        will be <code class="literal">'INSERT'</code>, <code class="literal">'UPDATE'</code>, or
  20        <code class="literal">'DELETE'</code>.
  21       </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
  22    Example:
  23 </p><pre class="screen">
  24 MERGE INTO products p
  25   USING stock s ON p.product_id = s.product_id
  26   WHEN MATCHED AND s.quantity &gt; 0 THEN
  27     UPDATE SET in_stock = true, quantity = s.quantity
  28   WHEN MATCHED THEN
  29     UPDATE SET in_stock = false, quantity = 0
  30   WHEN NOT MATCHED THEN
  31     INSERT (product_id, in_stock, quantity)
  32       VALUES (s.product_id, true, s.quantity)
  33   RETURNING merge_action(), p.*;
  34
  35  merge_action | product_id | in_stock | quantity
  36 --------------+------------+----------+----------
  37  UPDATE       |       1001 | t        |       50
  38  UPDATE       |       1002 | f        |        0
  39  INSERT       |       1003 | t        |       10
  40 </pre><p>
  41   </p><p>
  42    Note that this function can only be used in the <code class="literal">RETURNING</code>
  43    list of a <code class="command">MERGE</code> command.  It is an error to use it in any
  44    other part of a query.
  45   </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-window.html" title="9.22. Window Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-subquery.html" title="9.24. Subquery Expressions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.22. Window Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.24. Subquery Expressions</td></tr></table></div></body></html>