It has been a fact that the RETURNING clause on an INSERT will return
multiple rows with the same order as multiple VALUES have been fed.
eg: INSERT INTO tbl1(code) VALUES ('abc'), ('def'), ('agh') RETURNING id, code;
is expected to yield: id | code ----------- 1 | abc 2 | def 3 | agh
Clarify that in the documentation, and also write a test case that will
prevent us from breaking the rule in the future.
---doc/src/sgml/ref/insert.sgml | 17 +++++++++++++++++src/test/regress/expected/insert.out | 9
+++++++++src/test/regress/sql/insert.sql | 4 ++++3 files changed, 30 insertions(+), 0 deletions(-)
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index a3930be..64cb41b 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -213,6 +213,11 @@ INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</repl
<literal>RETURNING</>list, computed over the row(s) inserted by the command. </para>
+ <para>
+ If multiple rows are inserted by an <literal>INSERT ... RETURNING</> commmand,
+ the order of the <literal>RETURNING</> rows is the same as that of the inputs
+ to the <command>INSERT</> command.
+ </para> </refsect1> <refsect1>
@@ -268,6 +273,18 @@ INSERT INTO films (code, title, did, date_prod, kind) VALUES </para> <para>
+ This example inserts multiple rows and returns the corresponding ids
+ at the same order:
+
+<programlisting>
+INSERT INTO films(code, title) VALUES
+ ('B6717', 'Tampopo'),
+ ('HG120', 'The Dinner Game')
+ RETURNING id, code;
+</programlisting>
+ </para>
+
+ <para> This example inserts some rows into table <literal>films</literal> from a table
<literal>tmp_films</literal> with the same column layout as <literal>films</literal>:
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 96c7f9e..081e4b9 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -80,4 +80,13 @@ select col1, col2, char_length(col3) from inserttest; 30 | 50 | 10000(8 rows)
+--- RETURNING order
+insert into inserttest(col1, col2) values(50, 10), (60, 8), (70, 23) RETURNING col2;
+ col2
+------
+ 10
+ 8
+ 23
+(3 rows)
+drop table inserttest;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index a0ae850..c7815dd 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -35,4 +35,8 @@ insert into inserttest values(30, 50, repeat('x', 10000));select col1, col2, char_length(col3) from
inserttest;
+--- RETURNING order
+
+insert into inserttest(col1, col2) values(50, 10), (60, 8), (70, 23) RETURNING col2;
+drop table inserttest;
--
1.7.4.4
