diff options
Diffstat (limited to 'doc/src/sgml/ref/insert.sgml')
| -rw-r--r-- | doc/src/sgml/ref/insert.sgml | 65 |
1 files changed, 59 insertions, 6 deletions
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 4e589b599b6..55eaef08523 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.30 2005/11/17 22:14:51 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.31 2006/08/12 02:52:03 tgl Exp $ PostgreSQL documentation --> @@ -21,7 +21,8 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ] - { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | <replaceable class="PARAMETER">query</replaceable> } + { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> } + [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ] </synopsis> </refsynopsisdiv> @@ -30,8 +31,8 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable <para> <command>INSERT</command> inserts new rows into a table. - One can insert a single row specified by value expressions, - or several rows as a result of a query. + One can insert rows specified by value expressions, + or rows computed as a result of a query. </para> <para> @@ -56,6 +57,16 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable </para> <para> + The optional <literal>RETURNING</> clause causes <command>INSERT</> + to compute and return value(s) based on each row actually inserted. + This is primarily useful for obtaining values that were supplied by + defaults, such as a serial sequence number. However, any expression + using the table's columns is allowed. The syntax of the + <literal>RETURNING</> list is identical to that of the output list + of <command>SELECT</>. + </para> + + <para> You must have <literal>INSERT</literal> privilege to a table in order to insert into it. If you use the <replaceable class="PARAMETER">query</replaceable> clause to insert rows from a @@ -123,11 +134,33 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable <listitem> <para> A query (<command>SELECT</command> statement) that supplies the - rows to be inserted. Refer to the <command>SELECT</command> + rows to be inserted. Refer to the + <xref linkend="sql-select" endterm="sql-select-title"> statement for a description of the syntax. </para> </listitem> </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">output_expression</replaceable></term> + <listitem> + <para> + An expression to be computed and returned by the <command>INSERT</> + command after each row is inserted. The expression may use any + column names of the <replaceable class="PARAMETER">table</replaceable>. + Write <literal>*</> to return all columns of the inserted row(s). + </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> </variablelist> </refsect1> @@ -147,6 +180,14 @@ INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</repl <acronym>OID</acronym> assigned to the inserted row. Otherwise <replaceable class="parameter">oid</replaceable> is zero. </para> + + <para> + If the <command>INSERT</> command contains a <literal>RETURNING</> + clause, the result will be similar to that of a <command>SELECT</> + statement containing the columns and values defined in the + <literal>RETURNING</> list, computed over the row(s) inserted by the + command. + </para> </refsect1> <refsect1> @@ -213,13 +254,25 @@ INSERT INTO tictactoe (game, board) VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}'); </programlisting> </para> + + <para> + Insert a single row into table <literal>distributors</literal>, returning + the sequence number generated by the <literal>DEFAULT</literal> clause: + +<programlisting> +INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') + RETURNING did; +</programlisting> + </para> </refsect1> <refsect1> <title>Compatibility</title> <para> - <command>INSERT</command> conforms to the SQL standard. The case in + <command>INSERT</command> conforms to the SQL standard, except that + the <literal>RETURNING</> clause is a + <productname>PostgreSQL</productname> extension. Also, the case in which a column name list is omitted, but not all the columns are filled from the <literal>VALUES</> clause or <replaceable>query</>, is disallowed by the standard. |