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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
