diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/libpq.sgml | 8 | ||||
| -rw-r--r-- | doc/src/sgml/mvcc.sgml | 28 | ||||
| -rw-r--r-- | doc/src/sgml/plpgsql.sgml | 3 | ||||
| -rw-r--r-- | doc/src/sgml/ref/allfiles.sgml | 1 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_policy.sgml | 7 | ||||
| -rw-r--r-- | doc/src/sgml/ref/insert.sgml | 11 | ||||
| -rw-r--r-- | doc/src/sgml/ref/merge.sgml | 617 | ||||
| -rw-r--r-- | doc/src/sgml/reference.sgml | 1 | ||||
| -rw-r--r-- | doc/src/sgml/trigger.sgml | 20 |
9 files changed, 6 insertions, 690 deletions
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index 1626999a701..800e68a19e0 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -3917,11 +3917,9 @@ char *PQcmdTuples(PGresult *res); <structname>PGresult</structname>. This function can only be used following the execution of a <command>SELECT</command>, <command>CREATE TABLE AS</command>, <command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>, - <command>MERGE</command>, <command>MOVE</command>, <command>FETCH</command>, - or <command>COPY</command> statement, or an <command>EXECUTE</command> of a - prepared query that contains an <command>INSERT</command>, - <command>UPDATE</command>, <command>DELETE</command> - or <command>MERGE</command> statement. + <command>MOVE</command>, <command>FETCH</command>, or <command>COPY</command> statement, + or an <command>EXECUTE</command> of a prepared query that contains an + <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command> statement. If the command that generated the <structname>PGresult</structname> was anything else, <function>PQcmdTuples</function> returns an empty string. The caller should not free the return value directly. It will be freed when diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index 0e3e89af560..24613e3c754 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -423,31 +423,6 @@ COMMIT; </para> <para> - The <command>MERGE</command> allows the user to specify various combinations - of <command>INSERT</command>, <command>UPDATE</command> or - <command>DELETE</command> subcommands. A <command>MERGE</command> command - with both <command>INSERT</command> and <command>UPDATE</command> - subcommands looks similar to <command>INSERT</command> with an - <literal>ON CONFLICT DO UPDATE</literal> clause but does not guarantee - that either <command>INSERT</command> and <command>UPDATE</command> will occur. - - If MERGE attempts an UPDATE or DELETE and the row is concurrently updated - but the join condition still passes for the current target and the current - source tuple, then MERGE will behave the same as the UPDATE or DELETE commands - and perform its action on the latest version of the row, using standard - EvalPlanQual. MERGE actions can be conditional, so conditions must be - re-evaluated on the latest row, starting from the first action. - - On the other hand, if the row is concurrently updated or deleted so that - the join condition fails, then MERGE will execute a NOT MATCHED action, if it - exists and the AND WHEN qual evaluates to true. - - If MERGE attempts an INSERT and a unique index is present and a duplicate - row is concurrently inserted then a uniqueness violation is raised. MERGE - does not attempt to avoid the ERROR by attempting an UPDATE. - </para> - - <para> Because Read Committed mode starts each command with a new snapshot that includes all transactions committed up to that instant, subsequent commands in the same transaction will see the effects @@ -925,8 +900,7 @@ ERROR: could not serialize access due to read/write dependencies among transact <para> The commands <command>UPDATE</command>, - <command>DELETE</command>, <command>INSERT</command> and - <command>MERGE</command> + <command>DELETE</command>, and <command>INSERT</command> acquire this lock mode on the target table (in addition to <literal>ACCESS SHARE</literal> locks on any other referenced tables). In general, this lock mode will be acquired by any diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 59f6112b07c..5b2aac618e3 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1246,7 +1246,7 @@ EXECUTE format('SELECT count(*) FROM %I ' </programlisting> Another restriction on parameter symbols is that they only work in <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>, and - <command>DELETE</command> and <command>MERGE</command> commands. In other statement + <command>DELETE</command> commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values. </para> @@ -1529,7 +1529,6 @@ GET DIAGNOSTICS integer_var = ROW_COUNT; <listitem> <para> <command>UPDATE</command>, <command>INSERT</command>, and <command>DELETE</command> - and <command>MERGE</command> statements set <literal>FOUND</literal> true if at least one row is affected, false if no row is affected. </para> diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 7cd6ee85dc9..c81c87ef41c 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -159,7 +159,6 @@ Complete list of usable sgml source files in this directory. <!ENTITY load SYSTEM "load.sgml"> <!ENTITY lock SYSTEM "lock.sgml"> <!ENTITY move SYSTEM "move.sgml"> -<!ENTITY merge SYSTEM "merge.sgml"> <!ENTITY notify SYSTEM "notify.sgml"> <!ENTITY prepare SYSTEM "prepare.sgml"> <!ENTITY prepareTransaction SYSTEM "prepare_transaction.sgml"> diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 32f39a48ba9..0e35b0ef43e 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -94,13 +94,6 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable exist, a <quote>default deny</quote> policy is assumed, so that no rows will be visible or updatable. </para> - - <para> - No separate policy exists for <command>MERGE</command>. Instead policies - defined for <literal>SELECT</literal>, <literal>INSERT</literal>, - <literal>UPDATE</literal> and <literal>DELETE</literal> are applied - while executing MERGE, depending on the actions that are activated. - </para> </refsect1> <refsect1> diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index da294aaa46a..62e142fd8ef 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -579,13 +579,6 @@ INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</repl is a partition, an error will occur if one of the input rows violates the partition constraint. </para> - - <para> - You may also wish to consider using <command>MERGE</command>, since that - allows mixed <command>INSERT</command>, <command>UPDATE</command> and - <command>DELETE</command> within a single statement. - See <xref linkend="sql-merge"/>. - </para> </refsect1> <refsect1> @@ -756,9 +749,7 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International') Also, the case in which a column name list is omitted, but not all the columns are filled from the <literal>VALUES</literal> clause or <replaceable>query</replaceable>, - is disallowed by the standard. If you prefer a more SQL Standard - conforming statement than <literal>ON CONFLICT</literal>, see - <xref linkend="sql-merge"/>. + is disallowed by the standard. </para> <para> diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml deleted file mode 100644 index b2a9f67cfa9..00000000000 --- a/doc/src/sgml/ref/merge.sgml +++ /dev/null @@ -1,617 +0,0 @@ -<!-- -doc/src/sgml/ref/merge.sgml -PostgreSQL documentation ---> - -<refentry id="sql-merge"> - - <refmeta> - <refentrytitle>MERGE</refentrytitle> - <manvolnum>7</manvolnum> - <refmiscinfo>SQL - Language Statements</refmiscinfo> - </refmeta> - - <refnamediv> - <refname>MERGE</refname> - <refpurpose>insert, update, or delete rows of a table based upon source data</refpurpose> - </refnamediv> - - <refsynopsisdiv> -<synopsis> -[ WITH <replaceable class="parameter">with_query</replaceable> [, ...] ] -MERGE INTO <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> [...] - -where <replaceable class="parameter">data_source</replaceable> is - -{ <replaceable class="parameter">source_table_name</replaceable> | - ( source_query ) -} -[ [ AS ] <replaceable class="parameter">source_alias</replaceable> ] - -and <replaceable class="parameter">when_clause</replaceable> is - -{ WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> } | - WHEN NOT MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } -} - -and <replaceable class="parameter">merge_insert</replaceable> is - -INSERT [( <replaceable class="parameter">column_name</replaceable> [, ...] )] -[ OVERRIDING { SYSTEM | USER } VALUE ] -{ VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | DEFAULT VALUES } - -and <replaceable class="parameter">merge_update</replaceable> is - -UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } | - ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) - } [, ...] - -and <replaceable class="parameter">merge_delete</replaceable> is - -DELETE -</synopsis> - </refsynopsisdiv> - - <refsect1> - <title>Description</title> - - <para> - <command>MERGE</command> performs actions that modify rows in the - <replaceable class="parameter">target_table_name</replaceable>, - using the <replaceable class="parameter">data_source</replaceable>. - <command>MERGE</command> provides a single <acronym>SQL</acronym> - statement that can conditionally <command>INSERT</command>, - <command>UPDATE</command> or <command>DELETE</command> rows, a task - that would otherwise require multiple procedural language statements. - </para> - - <para> - First, the <command>MERGE</command> command performs a join - from <replaceable class="parameter">data_source</replaceable> to - <replaceable class="parameter">target_table_name</replaceable> - producing zero or more candidate change rows. For each candidate change - row the status of <literal>MATCHED</literal> or <literal>NOT MATCHED</literal> is set - just once, after which <literal>WHEN</literal> clauses are evaluated - in the order specified. If one of them is activated, the specified - action occurs. No more than one <literal>WHEN</literal> clause can be - activated for any candidate change row. - </para> - - <para> - <command>MERGE</command> actions have the same effect as - regular <command>UPDATE</command>, <command>INSERT</command>, or - <command>DELETE</command> commands of the same names. The syntax of - those commands is different, notably that there is no <literal>WHERE</literal> - clause and no tablename is specified. All actions refer to the - <replaceable class="parameter">target_table_name</replaceable>, - though modifications to other tables may be made using triggers. - </para> - - <para> - When <literal>DO NOTHING</literal> action is specified, the source row is - skipped. Since actions are evaluated in the given order, <literal>DO - NOTHING</literal> can be handy to skip non-interesting source rows before - more fine-grained handling. - </para> - - <para> - There is no MERGE privilege. - You must have the <literal>UPDATE</literal> privilege on the column(s) - of the <replaceable class="parameter">target_table_name</replaceable> - referred to in the <literal>SET</literal> clause - if you specify an update action, the <literal>INSERT</literal> privilege - on the <replaceable class="parameter">target_table_name</replaceable> - if you specify an insert action and/or the <literal>DELETE</literal> - privilege on the <replaceable class="parameter">target_table_name</replaceable> - if you specify a delete action on the - <replaceable class="parameter">target_table_name</replaceable>. - Privileges are tested once at statement start and are checked - whether or not particular <literal>WHEN</literal> clauses are activated - during the subsequent execution. - You will require the <literal>SELECT</literal> privilege on the - <replaceable class="parameter">data_source</replaceable> and any column(s) - of the <replaceable class="parameter">target_table_name</replaceable> - referred to in a <literal>condition</literal>. - </para> - - <para> - MERGE is not supported if the <replaceable - class="parameter">target_table_name</replaceable> has - <literal>RULES</literal> defined on it. - See <xref linkend="rules"/> for more information about <literal>RULES</literal>. - </para> - </refsect1> - - <refsect1> - <title>Parameters</title> - - <variablelist> - <varlistentry> - <term><replaceable class="parameter">target_table_name</replaceable></term> - <listitem> - <para> - The name (optionally schema-qualified) of the target table to merge into. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">target_alias</replaceable></term> - <listitem> - <para> - A substitute name for the target table. When an alias is - provided, it completely hides the actual name of the table. For - example, given <literal>MERGE foo AS f</literal>, the remainder of the - <command>MERGE</command> statement must refer to this table as - <literal>f</literal> not <literal>foo</literal>. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">source_table_name</replaceable></term> - <listitem> - <para> - The name (optionally schema-qualified) of the source table, view or - transition table. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">source_query</replaceable></term> - <listitem> - <para> - A query (<command>SELECT</command> statement or <command>VALUES</command> - statement) that supplies the rows to be merged into the - <replaceable class="parameter">target_table_name</replaceable>. - Refer to the <xref linkend="sql-select"/> - statement or <xref linkend="sql-values"/> - statement for a description of the syntax. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">source_alias</replaceable></term> - <listitem> - <para> - A substitute name for the data source. When an alias is - provided, it completely hides whether table or query was specified. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">join_condition</replaceable></term> - <listitem> - <para> - <replaceable class="parameter">join_condition</replaceable> is - an expression resulting in a value of type - <type>boolean</type> (similar to a <literal>WHERE</literal> - clause) that specifies which rows in the - <replaceable class="parameter">data_source</replaceable> - match rows in the - <replaceable class="parameter">target_table_name</replaceable>. - </para> - <warning> - <para> - Only columns from <replaceable class="parameter">target_table_name</replaceable> - that attempt to match <replaceable class="parameter">data_source</replaceable> - rows should appear in <replaceable class="parameter">join_condition</replaceable>. - <replaceable class="parameter">join_condition</replaceable> subexpressions that - only reference <replaceable class="parameter">target_table_name</replaceable> - columns can only affect which action is taken, often in surprising ways. - </para> - </warning> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">when_clause</replaceable></term> - <listitem> - <para> - At least one <literal>WHEN</literal> clause is required. - </para> - <para> - If the <literal>WHEN</literal> clause specifies <literal>WHEN MATCHED</literal> - and the candidate change row matches a row in the - <replaceable class="parameter">target_table_name</replaceable> - the <literal>WHEN</literal> clause is activated if the - <replaceable class="parameter">condition</replaceable> is - absent or is present and evaluates to <literal>true</literal>. - If the <literal>WHEN</literal> clause specifies <literal>WHEN NOT MATCHED</literal> - and the candidate change row does not match a row in the - <replaceable class="parameter">target_table_name</replaceable> - the <literal>WHEN</literal> clause is activated if the - <replaceable class="parameter">condition</replaceable> is - absent or is present and evaluates to <literal>true</literal>. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">condition</replaceable></term> - <listitem> - <para> - An expression that returns a value of type <type>boolean</type>. - If this expression returns <literal>true</literal> then the <literal>WHEN</literal> - clause will be activated and the corresponding action will occur for - that row. The expression may not contain functions that possibly performs - writes to the database. - </para> - <para> - A condition on a <literal>WHEN MATCHED</literal> clause can refer to columns - in both the source and the target relation. A condition on a - <literal>WHEN NOT MATCHED</literal> clause can only refer to columns from - the source relation, since by definition there is no matching target row. - Only the system attributes from the target table are accessible. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">merge_insert</replaceable></term> - <listitem> - <para> - The specification of an <literal>INSERT</literal> action that inserts - one row into the target table. - The target column names can be listed in any order. If no list of - column names is given at all, the default is all the columns of the - table in their declared order. - </para> - <para> - Each column not present in the explicit or implicit column list will be - filled with a default value, either its declared default value - or null if there is none. - </para> - <para> - If the expression for any column is not of the correct data type, - automatic type conversion will be attempted. - </para> - <para> - If <replaceable class="parameter">target_table_name</replaceable> - is a partitioned table, each row is routed to the appropriate partition - and inserted into it. - If <replaceable class="parameter">target_table_name</replaceable> - is a partition, an error will occur if one of the input rows violates - the partition constraint. - </para> - <para> - Column names may not be specified more than once. - <command>INSERT</command> actions cannot contain sub-selects. - </para> - <para> - Only one <literal>VALUES</literal> clause can be specified. - The <literal>VALUES</literal> clause can only refer to columns from - the source relation, since by definition there is no matching target row. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">merge_update</replaceable></term> - <listitem> - <para> - The specification of an <literal>UPDATE</literal> action that updates - the current row of the <replaceable - class="parameter">target_table_name</replaceable>. - Column names may not be specified more than once. - </para> - <para> - Do not include the table name, as you would normally do with an - <xref linkend="sql-update"/> command. - For example, <literal>UPDATE tab SET col = 1</literal> is invalid. Also, - do not include a <literal>WHERE</literal> clause, since only the current - row can be updated. For example, - <literal>UPDATE SET col = 1 WHERE key = 57</literal> is invalid. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">merge_delete</replaceable></term> - <listitem> - <para> - Specifies a <literal>DELETE</literal> action that deletes the current row - of the <replaceable class="parameter">target_table_name</replaceable>. - Do not include the tablename or any other clauses, as you would normally - do with an <xref linkend="sql-delete"/> command. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">column_name</replaceable></term> - <listitem> - <para> - The name of a column in the <replaceable - class="parameter">target_table_name</replaceable>. The column name - can be qualified with a subfield name or array subscript, if - needed. (Inserting into only some fields of a composite - column leaves the other fields null.) When referencing a - column, do not include the table's name in the specification - of a target column. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>OVERRIDING SYSTEM VALUE</literal></term> - <listitem> - <para> - Without this clause, it is an error to specify an explicit value - (other than <literal>DEFAULT</literal>) for an identity column defined - as <literal>GENERATED ALWAYS</literal>. This clause overrides that - restriction. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>OVERRIDING USER VALUE</literal></term> - <listitem> - <para> - If this clause is specified, then any values supplied for identity - columns defined as <literal>GENERATED BY DEFAULT</literal> are ignored - and the default sequence-generated values are applied. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>DEFAULT VALUES</literal></term> - <listitem> - <para> - All columns will be filled with their default values. - (An <literal>OVERRIDING</literal> clause is not permitted in this - form.) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">expression</replaceable></term> - <listitem> - <para> - An expression to assign to the column. The expression can use the - old values of this and other columns in the table. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>DEFAULT</literal></term> - <listitem> - <para> - Set the column to its default value (which will be NULL if no - specific default expression has been assigned to it). - </para> - </listitem> - </varlistentry> - - <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> - - </variablelist> - </refsect1> - - <refsect1> - <title>Outputs</title> - - <para> - On successful completion, a <command>MERGE</command> command returns a command - tag of the form -<screen> -MERGE <replaceable class="parameter">total-count</replaceable> -</screen> - The <replaceable class="parameter">total-count</replaceable> is the total - number of rows changed (whether inserted, updated, or deleted). - If <replaceable class="parameter">total-count</replaceable> is 0, no rows - were changed in any way. - </para> - - </refsect1> - - <refsect1> - <title>Execution</title> - - <para> - The following steps take place during the execution of - <command>MERGE</command>. - <orderedlist> - <listitem> - <para> - Perform any BEFORE STATEMENT triggers for all actions specified, whether or - not their <literal>WHEN</literal> clauses are activated during execution. - </para> - </listitem> - <listitem> - <para> - Perform a join from source to target table. - The resulting query will be optimized normally and will produce - a set of candidate change row. For each candidate change row - <orderedlist> - <listitem> - <para> - Evaluate whether each row is MATCHED or NOT MATCHED. - </para> - </listitem> - <listitem> - <para> - Test each WHEN condition in the order specified until one activates. - </para> - </listitem> - <listitem> - <para> - When activated, perform the following actions - <orderedlist> - <listitem> - <para> - Perform any BEFORE ROW triggers that fire for the action's event type. - </para> - </listitem> - <listitem> - <para> - Apply the action specified, invoking any check constraints on the - target table. - However, it will not invoke rules. - </para> - </listitem> - <listitem> - <para> - Perform any AFTER ROW triggers that fire for the action's event type. - </para> - </listitem> - </orderedlist> - </para> - </listitem> - </orderedlist> - </para> - </listitem> - <listitem> - <para> - Perform any AFTER STATEMENT triggers for actions specified, whether or - not they actually occur. This is similar to the behavior of an - <command>UPDATE</command> statement that modifies no rows. - </para> - </listitem> - </orderedlist> - In summary, statement triggers for an event type (say, INSERT) will - be fired whenever we <emphasis>specify</emphasis> an action of that kind. Row-level - triggers will fire only for the one event type <emphasis>activated</emphasis>. - So a <command>MERGE</command> might fire statement triggers for both - <command>UPDATE</command> and <command>INSERT</command>, even though only - <command>UPDATE</command> row triggers were fired. - </para> - - <para> - You should ensure that the join produces at most one candidate change row - for each target row. In other words, a target row shouldn't join to more - than one data source row. If it does, then only one of the candidate change - rows will be used to modify the target row, later attempts to modify will - cause an error. This can also occur if row triggers make changes to the - target table which are then subsequently modified by <command>MERGE</command>. - If the repeated action is an <command>INSERT</command> this will - cause a uniqueness violation while a repeated <command>UPDATE</command> or - <command>DELETE</command> will cause a cardinality violation; the latter behavior - is required by the <acronym>SQL</acronym> Standard. This differs from - historical <productname>PostgreSQL</productname> behavior of joins in - <command>UPDATE</command> and <command>DELETE</command> statements where second and - subsequent attempts to modify are simply ignored. - </para> - - <para> - If a <literal>WHEN</literal> clause omits an <literal>AND</literal> clause it becomes - the final reachable clause of that kind (<literal>MATCHED</literal> or - <literal>NOT MATCHED</literal>). If a later <literal>WHEN</literal> clause of that kind - is specified it would be provably unreachable and an error is raised. - If a final reachable clause is omitted it is possible that no action - will be taken for a candidate change row. - </para> - - </refsect1> - <refsect1> - <title>Notes</title> - - <para> - The order in which rows are generated from the data source is indeterminate - by default. A <replaceable class="parameter">source_query</replaceable> - can be used to specify a consistent ordering, if required, which might be - needed to avoid deadlocks between concurrent transactions. - </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> - - <tip> - <para> - You may also wish to consider using <command>INSERT ... ON CONFLICT</command> as an - alternative statement which offers the ability to run an <command>UPDATE</command> - if a concurrent <command>INSERT</command> occurs. There are a variety of - differences and restrictions between the two statement types and they are not - interchangeable. - </para> - </tip> - </refsect1> - - <refsect1> - <title>Examples</title> - - <para> - Perform maintenance on CustomerAccounts based upon new Transactions. - -<programlisting> -MERGE CustomerAccount CA -USING RecentTransactions T -ON T.CustomerId = CA.CustomerId -WHEN MATCHED THEN - UPDATE SET Balance = Balance + TransactionValue -WHEN NOT MATCHED THEN - INSERT (CustomerId, Balance) - VALUES (T.CustomerId, T.TransactionValue); -</programlisting> - - notice that this would be exactly equivalent to the following - statement because the <literal>MATCHED</literal> result does not change - during execution - -<programlisting> -MERGE CustomerAccount CA -USING (Select CustomerId, TransactionValue From RecentTransactions) AS T -ON CA.CustomerId = T.CustomerId -WHEN NOT MATCHED THEN - INSERT (CustomerId, Balance) - VALUES (T.CustomerId, T.TransactionValue) -WHEN MATCHED THEN - UPDATE SET Balance = Balance + TransactionValue; -</programlisting> - </para> - - <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. -<programlisting> -MERGE INTO wines w -USING wine_stock_changes s -ON s.winename = w.winename -WHEN NOT MATCHED AND s.stock_delta > 0 THEN - INSERT VALUES(s.winename, s.stock_delta) -WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN - UPDATE SET stock = w.stock + s.stock_delta; -WHEN MATCHED THEN - DELETE; -</programlisting> - - The wine_stock_changes table might be, for example, a temporary table - recently loaded into the database. - </para> - - </refsect1> - - <refsect1> - <title>Compatibility</title> - <para> - This command conforms to the <acronym>SQL</acronym> standard. - </para> - <para> - The WITH clause and DO NOTHING action are extensions to the <acronym>SQL</acronym> standard. - </para> - </refsect1> -</refentry> diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index ef2270c4673..d27fb414f7c 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -186,7 +186,6 @@ &listen; &load; &lock; - &merge; &move; ¬ify; &prepare; diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index cce58fbf1d0..c43dbc9786e 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -183,26 +183,6 @@ </para> <para> - No separate triggers are defined for <command>MERGE</command>. Instead, - statement-level or row-level <command>UPDATE</command>, - <command>DELETE</command> and <command>INSERT</command> triggers are fired - depending on what actions are specified in the <command>MERGE</command> query - and what actions are activated. - </para> - - <para> - While running a <command>MERGE</command> command, statement-level - <literal>BEFORE</literal> and <literal>AFTER</literal> triggers are fired for - events specified in the actions of the <command>MERGE</command> command, - irrespective of whether the action is finally activated or not. This is same as - an <command>UPDATE</command> statement that updates no rows, yet - statement-level triggers are fired. The row-level triggers are fired only - when a row is actually updated, inserted or deleted. So it's perfectly legal - that while statement-level triggers are fired for certain type of action, no - row-level triggers are fired for the same kind of action. - </para> - - <para> Trigger functions invoked by per-statement triggers should always return <symbol>NULL</symbol>. Trigger functions invoked by per-row triggers can return a table row (a value of |