Skip to content

Commit 12c5c3a

Browse files
dmpgprodmpgpro
committed
Merge branch 'REL9_6_STABLE' into PGPRO9_6
Conflicts: doc/src/sgml/ref/update.sgml
2 parents 0aaa23a + 9b66342 commit 12c5c3a

File tree

29 files changed

+924
-266
lines changed

29 files changed

+924
-266
lines changed

‎contrib/test_decoding/expected/spill.out

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -164,7 +164,7 @@ SAVEPOINT s2;
164164
INSERT INTO spill_test SELECT 'serialize-subsmall-subbig--2:'||g.i FROM generate_series(2, 5001) g(i);
165165
RELEASE SAVEPOINT s2;
166166
COMMIT;
167-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
167+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
168168
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
169169
GROUP BY 1 ORDER BY 1;
170170
regexp_split_to_array | count | array_agg | array_agg
@@ -182,7 +182,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbig--2:'||g.i FROM gen
182182
RELEASE SAVEPOINT s2;
183183
RELEASE SAVEPOINT s1;
184184
COMMIT;
185-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
185+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
186186
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
187187
GROUP BY 1 ORDER BY 1;
188188
regexp_split_to_array | count | array_agg | array_agg
@@ -200,7 +200,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subsmall--2:'||g.i FROM g
200200
RELEASE SAVEPOINT s2;
201201
RELEASE SAVEPOINT s1;
202202
COMMIT;
203-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
203+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
204204
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
205205
GROUP BY 1 ORDER BY 1;
206206
regexp_split_to_array | count | array_agg | array_agg
@@ -218,7 +218,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subsmall-subbig--2:'||g.i FROM g
218218
RELEASE SAVEPOINT s2;
219219
RELEASE SAVEPOINT s1;
220220
COMMIT;
221-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
221+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
222222
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
223223
GROUP BY 1 ORDER BY 1;
224224
regexp_split_to_array | count | array_agg | array_agg
@@ -238,7 +238,7 @@ SAVEPOINT s3;
238238
INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbigabort-subbig-3:'||g.i FROM generate_series(5001, 10000) g(i);
239239
RELEASE SAVEPOINT s1;
240240
COMMIT;
241-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
241+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
242242
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
243243
GROUP BY 1 ORDER BY 1;
244244
regexp_split_to_array | count | array_agg | array_agg

‎contrib/test_decoding/sql/spill.sql

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,7 @@ SAVEPOINT s2;
116116
INSERT INTO spill_test SELECT 'serialize-subsmall-subbig--2:'||g.i FROM generate_series(2, 5001) g(i);
117117
RELEASE SAVEPOINT s2;
118118
COMMIT;
119-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
119+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
120120
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
121121
GROUP BY 1 ORDER BY 1;
122122

@@ -129,7 +129,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbig--2:'||g.i FROM gen
129129
RELEASE SAVEPOINT s2;
130130
RELEASE SAVEPOINT s1;
131131
COMMIT;
132-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
132+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
133133
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
134134
GROUP BY 1 ORDER BY 1;
135135

@@ -142,7 +142,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subsmall--2:'||g.i FROM g
142142
RELEASE SAVEPOINT s2;
143143
RELEASE SAVEPOINT s1;
144144
COMMIT;
145-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
145+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
146146
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
147147
GROUP BY 1 ORDER BY 1;
148148

@@ -155,7 +155,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subsmall-subbig--2:'||g.i FROM g
155155
RELEASE SAVEPOINT s2;
156156
RELEASE SAVEPOINT s1;
157157
COMMIT;
158-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
158+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
159159
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
160160
GROUP BY 1 ORDER BY 1;
161161

@@ -170,7 +170,7 @@ SAVEPOINT s3;
170170
INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbigabort-subbig-3:'||g.i FROM generate_series(5001, 10000) g(i);
171171
RELEASE SAVEPOINT s1;
172172
COMMIT;
173-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
173+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
174174
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
175175
GROUP BY 1 ORDER BY 1;
176176

‎doc/src/sgml/dml.sgml

Lines changed: 99 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -102,6 +102,18 @@ INSERT INTO products (product_no, name, price) VALUES
102102
</programlisting>
103103
</para>
104104

105+
<para>
106+
It is also possible to insert the result of a query (which might be no
107+
rows, one row, or many rows):
108+
<programlisting>
109+
INSERT INTO products (product_no, name, price)
110+
SELECT product_no, name, price FROM new_products
111+
WHERE release_date = 'today';
112+
</programlisting>
113+
This provides the full power of the SQL query mechanism (<xref
114+
linkend="queries">) for computing the rows to be inserted.
115+
</para>
116+
105117
<tip>
106118
<para>
107119
When inserting a lot of data at the same time, considering using
@@ -252,4 +264,91 @@ DELETE FROM products;
252264
then all rows in the table will be deleted! Caveat programmer.
253265
</para>
254266
</sect1>
267+
268+
<sect1 id="dml-returning">
269+
<title>Returning Data From Modified Rows</title>
270+
271+
<indexterm zone="dml-returning">
272+
<primary>RETURNING</primary>
273+
</indexterm>
274+
275+
<indexterm zone="dml-returning">
276+
<primary>INSERT</primary>
277+
<secondary>RETURNING</secondary>
278+
</indexterm>
279+
280+
<indexterm zone="dml-returning">
281+
<primary>UPDATE</primary>
282+
<secondary>RETURNING</secondary>
283+
</indexterm>
284+
285+
<indexterm zone="dml-returning">
286+
<primary>DELETE</primary>
287+
<secondary>RETURNING</secondary>
288+
</indexterm>
289+
290+
<para>
291+
Sometimes it is useful to obtain data from modified rows while they are
292+
being manipulated. The <command>INSERT</>, <command>UPDATE</>,
293+
and <command>DELETE</> commands all have an
294+
optional <literal>RETURNING</> clause that supports this. Use
295+
of <literal>RETURNING</> avoids performing an extra database query to
296+
collect the data, and is especially valuable when it would otherwise be
297+
difficult to identify the modified rows reliably.
298+
</para>
299+
300+
<para>
301+
The allowed contents of a <literal>RETURNING</> clause are the same as
302+
a <command>SELECT</> command's output list
303+
(see <xref linkend="queries-select-lists">). It can contain column
304+
names of the command's target table, or value expressions using those
305+
columns. A common shorthand is <literal>RETURNING *</>, which selects
306+
all columns of the target table in order.
307+
</para>
308+
309+
<para>
310+
In an <command>INSERT</>, the data available to <literal>RETURNING</> is
311+
the row as it was inserted. This is not so useful in trivial inserts,
312+
since it would just repeat the data provided by the client. But it can
313+
be very handy when relying on computed default values. For example,
314+
when using a <link linkend="datatype-serial"><type>serial</></link>
315+
column to provide unique identifiers, <literal>RETURNING</> can return
316+
the ID assigned to a new row:
317+
<programlisting>
318+
CREATE TABLE users (firstname text, lastname text, id serial primary key);
319+
320+
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
321+
</programlisting>
322+
The <literal>RETURNING</> clause is also very useful
323+
with <literal>INSERT ... SELECT</>.
324+
</para>
325+
326+
<para>
327+
In an <command>UPDATE</>, the data available to <literal>RETURNING</> is
328+
the new content of the modified row. For example:
329+
<programlisting>
330+
UPDATE products SET price = price * 1.10
331+
WHERE price &lt;= 99.99
332+
RETURNING name, price AS new_price;
333+
</programlisting>
334+
</para>
335+
336+
<para>
337+
In a <command>DELETE</>, the data available to <literal>RETURNING</> is
338+
the content of the deleted row. For example:
339+
<programlisting>
340+
DELETE FROM products
341+
WHERE obsoletion_date = 'today'
342+
RETURNING *;
343+
</programlisting>
344+
</para>
345+
346+
<para>
347+
If there are triggers (<xref linkend="triggers">) on the target table,
348+
the data available to <literal>RETURNING</> is the row as modified by
349+
the triggers. Thus, inspecting columns computed by triggers is another
350+
common use-case for <literal>RETURNING</>.
351+
</para>
352+
353+
</sect1>
255354
</chapter>

‎doc/src/sgml/queries.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1457,7 +1457,8 @@ SELECT tbl1.a, tbl2.a, tbl1.b FROM ...
14571457
<programlisting>
14581458
SELECT tbl1.*, tbl2.a FROM ...
14591459
</programlisting>
1460-
(See also <xref linkend="queries-where">.)
1460+
See <xref linkend="rowtypes-usage"> for more about
1461+
the <replaceable>table_name</><literal>.*</> notation.
14611462
</para>
14621463

14631464
<para>
@@ -2262,7 +2263,8 @@ SELECT * FROM moved_rows;
22622263

22632264
<para>
22642265
Data-modifying statements in <literal>WITH</> usually have
2265-
<literal>RETURNING</> clauses, as seen in the example above.
2266+
<literal>RETURNING</> clauses (see <xref linkend="dml-returning">),
2267+
as shown in the example above.
22662268
It is the output of the <literal>RETURNING</> clause, <emphasis>not</> the
22672269
target table of the data-modifying statement, that forms the temporary
22682270
table that can be referred to by the rest of the query. If a

‎doc/src/sgml/ref/alter_materialized_view.sgml

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,6 @@ ALTER MATERIALIZED VIEW ALL IN TABLESPACE <replaceable class="parameter">name</r
4545
SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
4646
RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
4747
OWNER TO { <replaceable class="PARAMETER">new_owner</replaceable> | CURRENT_USER | SESSION_USER }
48-
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
4948
</synopsis>
5049
</refsynopsisdiv>
5150

‎doc/src/sgml/ref/update.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -422,9 +422,9 @@ UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
422422
According to the standard, the source value for a parenthesized sub-list of
423423
column names can be any row-valued expression yielding the correct number
424424
of columns. <productname>&productname;</productname> only allows the source
425-
value to be a parenthesized list of expressions (a row constructor) or a
425+
value to be a parenthesized list of expressions or a
426426
sub-<literal>SELECT</>. An individual column's updated value can be
427-
specified as <literal>DEFAULT</> in the row-constructor case, but not
427+
specified as <literal>DEFAULT</> in the list-of-expressions case, but not
428428
inside a sub-<literal>SELECT</>.
429429
</para>
430430
</refsect1>

0 commit comments

Comments
 (0)