Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit12c5c3a

Browse files
committed
Merge branch 'REL9_6_STABLE' into PGPRO9_6
Conflicts:doc/src/sgml/ref/update.sgml
2 parents0aaa23a +9b66342 commit12c5c3a

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_testSELECT'serialize-subsmall-subbig--2:'||g.iFROM 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 BY1ORDER BY1;
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 BY1ORDER BY1;
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 BY1ORDER BY1;
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 BY1ORDER BY1;
161161

@@ -170,7 +170,7 @@ SAVEPOINT s3;
170170
INSERT INTO spill_testSELECT'serialize-nested-subbig-subbigabort-subbig-3:'||g.iFROM 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 BY1ORDER BY1;
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 therow-constructor case, but not
427+
specified as <literal>DEFAULT</> in thelist-of-expressions case, but not
428428
inside a sub-<literal>SELECT</>.
429429
</para>
430430
</refsect1>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp