diff options
Diffstat (limited to 'doc/src/sgml/ref/merge.sgml')
| -rw-r--r-- | doc/src/sgml/ref/merge.sgml | 86 |
1 files changed, 54 insertions, 32 deletions
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml index f643e62..815b998 100644 --- a/doc/src/sgml/ref/merge.sgml +++ b/doc/src/sgml/ref/merge.sgml @@ -44,7 +44,9 @@ INSERT [( <replaceable class="parameter">column_name</replaceable> [, ...] )] <phrase>and <replaceable class="parameter">merge_update</replaceable> is:</phrase> 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 } [, ...] ) } [, ...] + ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | + ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> ) + } [, ...] <phrase>and <replaceable class="parameter">merge_delete</replaceable> is:</phrase> @@ -57,7 +59,7 @@ DELETE <para> <command>MERGE</command> performs actions that modify rows in the - <replaceable class="parameter">target_table_name</replaceable>, + target table identified as <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>, @@ -68,7 +70,7 @@ DELETE <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> + the target table 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 @@ -83,7 +85,7 @@ DELETE <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 table name is specified. All actions refer to the - <replaceable class="parameter">target_table_name</replaceable>, + target table, though modifications to other tables may be made using triggers. </para> @@ -98,23 +100,27 @@ DELETE 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) - of the <replaceable class="parameter">target_table_name</replaceable> + of the target table that are referred to in the <literal>SET</literal> clause. If you specify an insert action, you must have the <literal>INSERT</literal> - privilege on the <replaceable class="parameter">target_table_name</replaceable>. + privilege on the target table. If you specify a delete action, you must have the <literal>DELETE</literal> - privilege on the <replaceable class="parameter">target_table_name</replaceable>. + privilege on the target table. + If you specify a <literal>DO NOTHING</literal> action, you must have + the <literal>SELECT</literal> privilege on at least one column + of the target table. + You will also need <literal>SELECT</literal> privilege on any column(s) + of the <replaceable class="parameter">data_source</replaceable> and + of the target table referred to + in any <literal>condition</literal> (including <literal>join_condition</literal>) + or <literal>expression</literal>. Privileges are tested once at statement start and are checked whether or not particular <literal>WHEN</literal> clauses are executed. - You will require the <literal>SELECT</literal> privilege on any column(s) - of the <replaceable class="parameter">data_source</replaceable> and - <replaceable class="parameter">target_table_name</replaceable> referred to - in any <literal>condition</literal> or <literal>expression</literal>. </para> <para> <command>MERGE</command> is not supported if the - <replaceable class="parameter">target_table_name</replaceable> is a + target table is a materialized view, foreign table, or if it has any rules defined on it. </para> @@ -125,6 +131,19 @@ 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. Note that <literal>WITH RECURSIVE</literal> is not supported + by <command>MERGE</command>. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><replaceable class="parameter">target_table_name</replaceable></term> <listitem> <para> @@ -175,7 +194,7 @@ DELETE <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>. + target table. Refer to the <xref linkend="sql-select"/> statement or <xref linkend="sql-values"/> statement for a description of the syntax. @@ -203,16 +222,15 @@ DELETE <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>. + match rows in the target table. </para> <warning> <para> - Only columns from <replaceable class="parameter">target_table_name</replaceable> + Only columns from the target table 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> + only reference the target table's columns can affect which action is taken, often in surprising ways. </para> </warning> @@ -228,7 +246,7 @@ DELETE <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>, + target table, the <literal>WHEN</literal> clause is executed if the <replaceable class="parameter">condition</replaceable> is absent or it evaluates to <literal>true</literal>. @@ -237,7 +255,7 @@ DELETE Conversely, 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>, + target table, the <literal>WHEN</literal> clause is executed if the <replaceable class="parameter">condition</replaceable> is absent or it evaluates to <literal>true</literal>. @@ -280,10 +298,10 @@ DELETE or null if there is none. </para> <para> - If <replaceable class="parameter">target_table_name</replaceable> + If the target table is a partitioned table, each row is routed to the appropriate partition and inserted into it. - If <replaceable class="parameter">target_table_name</replaceable> + If the target table is a partition, an error will occur if any input row violates the partition constraint. </para> @@ -304,7 +322,7 @@ DELETE <listitem> <para> The specification of an <literal>UPDATE</literal> action that updates - the current row of the <replaceable class="parameter">target_table_name</replaceable>. + the current row of the target table. Column names may not be specified more than once. </para> <para> @@ -318,7 +336,7 @@ DELETE <listitem> <para> Specifies a <literal>DELETE</literal> action that deletes the current row - of the <replaceable class="parameter">target_table_name</replaceable>. + of the target table. Do not include the table name or any other clauses, as you would normally do with a <xref linkend="sql-delete"/> command. </para> @@ -329,8 +347,7 @@ DELETE <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 + The name of a column in the target table. 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.) @@ -381,9 +398,10 @@ DELETE An expression to assign to the column. If used in a <literal>WHEN MATCHED</literal> clause, the expression can use values from the original row in the target table, and values from the - <literal>data_source</literal> row. + <replaceable class="parameter">data_source</replaceable> row. If used in a <literal>WHEN NOT MATCHED</literal> clause, the - expression can use values from the <literal>data_source</literal>. + expression can use values from the + <replaceable class="parameter">data_source</replaceable> row. </para> </listitem> </varlistentry> @@ -399,13 +417,17 @@ DELETE </varlistentry> <varlistentry> - <term><replaceable class="parameter">with_query</replaceable></term> + <term><replaceable class="parameter">sub-SELECT</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. + A <literal>SELECT</literal> sub-query that produces as many output columns + as are listed in the parenthesized column list preceding it. The + sub-query must yield no more than one row when executed. If it + yields one row, its column values are assigned to the target columns; + if it yields no rows, NULL values are assigned to the target columns. + The sub-query can refer to values from the original row in the target table, + and values from the <replaceable class="parameter">data_source</replaceable> + row. </para> </listitem> </varlistentry> |