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

Commit92e3818

Browse files
committed
COPY (INSERT/UPDATE/DELETE .. RETURNING ..)
Attached is a patch for being able to do COPY (query) without a CTE.Author: Marko TiikkajaReview: Michael Paquier
1 parent0da3a9b commit92e3818

File tree

9 files changed

+284
-24
lines changed

9 files changed

+284
-24
lines changed

‎doc/src/sgml/ref/copy.sgml

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -112,10 +112,17 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
112112
<term><replaceable class="parameter">query</replaceable></term>
113113
<listitem>
114114
<para>
115-
A <xref linkend="sql-select"> or
116-
<xref linkend="sql-values"> command
117-
whose results are to be copied.
118-
Note that parentheses are required around the query.
115+
A <xref linkend="sql-select">, <xref linkend="sql-values">,
116+
<xref linkend="sql-insert">, <xref linkend="sql-update"> or
117+
<xref linkend="sql-delete"> command whose results are to be
118+
copied. Note that parentheses are required around the query.
119+
</para>
120+
<para>
121+
For <command>INSERT</>, <command>UPDATE</> and
122+
<command>DELETE</> queries a RETURNING clause must be provided,
123+
and the target relation must not have a conditional rule, nor
124+
an <literal>ALSO</> rule, nor an <literal>INSTEAD</> rule
125+
that expands to multiple statements.
119126
</para>
120127
</listitem>
121128
</varlistentry>

‎src/backend/commands/copy.c

Lines changed: 51 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -201,7 +201,7 @@ typedef struct CopyStateData
201201
intraw_buf_len;/* total # of bytes stored */
202202
}CopyStateData;
203203

204-
/* DestReceiver for COPY (SELECT) TO */
204+
/* DestReceiver for COPY (query) TO */
205205
typedefstruct
206206
{
207207
DestReceiverpub;/* publicly-known function pointers */
@@ -772,7 +772,8 @@ CopyLoadRawBuf(CopyState cstate)
772772
*
773773
* Either unload or reload contents of table <relation>, depending on <from>.
774774
* (<from> = TRUE means we are inserting into the table.) In the "TO" case
775-
* we also support copying the output of an arbitrary SELECT query.
775+
* we also support copying the output of an arbitrary SELECT, INSERT, UPDATE
776+
* or DELETE query.
776777
*
777778
* If <pipe> is false, transfer is between the table and the file named
778779
* <filename>. Otherwise, transfer is between the table and our regular
@@ -1374,11 +1375,11 @@ BeginCopy(bool is_from,
13741375
Assert(!is_from);
13751376
cstate->rel=NULL;
13761377

1377-
/* Don't allow COPY w/ OIDs from aselect */
1378+
/* Don't allow COPY w/ OIDs from aquery */
13781379
if (cstate->oids)
13791380
ereport(ERROR,
13801381
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1381-
errmsg("COPY (SELECT) WITH OIDS is not supported")));
1382+
errmsg("COPY (query) WITH OIDS is not supported")));
13821383

13831384
/*
13841385
* Run parse analysis and rewrite. Note this also acquires sufficient
@@ -1393,9 +1394,36 @@ BeginCopy(bool is_from,
13931394
rewritten=pg_analyze_and_rewrite((Node*)copyObject(raw_query),
13941395
queryString,NULL,0);
13951396

1396-
/* We don't expect more or less than one result query */
1397-
if (list_length(rewritten)!=1)
1398-
elog(ERROR,"unexpected rewrite result");
1397+
/* check that we got back something we can work with */
1398+
if (rewritten==NIL)
1399+
{
1400+
ereport(ERROR,
1401+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1402+
errmsg("DO INSTEAD NOTHING rules are not supported for COPY")));
1403+
}
1404+
elseif (list_length(rewritten)>1)
1405+
{
1406+
ListCell*lc;
1407+
1408+
/* examine queries to determine which error message to issue */
1409+
foreach(lc,rewritten)
1410+
{
1411+
Query*q= (Query*)lfirst(lc);
1412+
1413+
if (q->querySource==QSRC_QUAL_INSTEAD_RULE)
1414+
ereport(ERROR,
1415+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1416+
errmsg("conditional DO INSTEAD rules are not supported for COPY")));
1417+
if (q->querySource==QSRC_NON_INSTEAD_RULE)
1418+
ereport(ERROR,
1419+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1420+
errmsg("DO ALSO rules are not supported for the COPY")));
1421+
}
1422+
1423+
ereport(ERROR,
1424+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1425+
errmsg("multi-statement DO INSTEAD rules are not supported for COPY")));
1426+
}
13991427

14001428
query= (Query*)linitial(rewritten);
14011429

@@ -1406,9 +1434,24 @@ BeginCopy(bool is_from,
14061434
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
14071435
errmsg("COPY (SELECT INTO) is not supported")));
14081436

1409-
Assert(query->commandType==CMD_SELECT);
14101437
Assert(query->utilityStmt==NULL);
14111438

1439+
/*
1440+
* Similarly the grammar doesn't enforce the presence of a RETURNING
1441+
* clause, but this is required here.
1442+
*/
1443+
if (query->commandType!=CMD_SELECT&&
1444+
query->returningList==NIL)
1445+
{
1446+
Assert(query->commandType==CMD_INSERT||
1447+
query->commandType==CMD_UPDATE||
1448+
query->commandType==CMD_DELETE);
1449+
1450+
ereport(ERROR,
1451+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1452+
errmsg("COPY query must have a RETURNING clause")));
1453+
}
1454+
14121455
/* plan the query */
14131456
plan=pg_plan_query(query,0,NULL);
14141457

‎src/backend/parser/gram.y

Lines changed: 11 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -2561,9 +2561,12 @@ ClosePortalStmt:
25612561
*
25622562
*QUERY :
25632563
*COPY relname [(columnList)] FROM/TO file [WITH] [(options)]
2564-
*COPY (SELECT ... ) TO file[WITH] [(options)]
2564+
*COPY (query ) TO file[WITH] [(options)]
25652565
*
2566-
*where 'file' can be one of:
2566+
*where 'query' can be one of:
2567+
*{ SELECT | UPDATE | INSERT | DELETE }
2568+
*
2569+
*and 'file' can be one of:
25672570
*{ PROGRAM 'command' | STDIN | STDOUT | 'filename' }
25682571
*
25692572
*In the preferred syntax the options are comma-separated
@@ -2574,7 +2577,7 @@ ClosePortalStmt:
25742577
*COPY [ BINARY ] table [ WITH OIDS ] FROM/TO file
25752578
*[ [ USING ] DELIMITERS 'delimiter' ] ]
25762579
*[ WITH NULL AS 'null string' ]
2577-
*This option placement is not supported with COPY (SELECT...).
2580+
*This option placement is not supported with COPY (query...).
25782581
*
25792582
*****************************************************************************/
25802583

@@ -2607,16 +2610,16 @@ CopyStmt:COPY opt_binary qualified_name opt_column_list opt_oids
26072610
n->options = list_concat(n->options,$11);
26082611
$$ = (Node *)n;
26092612
}
2610-
|COPYselect_with_parensTOopt_programcopy_file_nameopt_withcopy_options
2613+
|COPY'('PreparableStmt')'TOopt_programcopy_file_nameopt_withcopy_options
26112614
{
26122615
CopyStmt *n = makeNode(CopyStmt);
26132616
n->relation =NULL;
2614-
n->query =$2;
2617+
n->query =$3;
26152618
n->attlist = NIL;
26162619
n->is_from =false;
2617-
n->is_program =$4;
2618-
n->filename =$5;
2619-
n->options =$7;
2620+
n->is_program =$6;
2621+
n->filename =$7;
2622+
n->options =$9;
26202623

26212624
if (n->is_program && n->filename ==NULL)
26222625
ereport(ERROR,

‎src/bin/psql/copy.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -32,10 +32,12 @@
3232
*
3333
* The documented syntax is:
3434
*\copy tablename [(columnlist)] from|to filename [options]
35-
*\copy (select stmt ) to filename [options]
35+
*\copy (query stmt ) to filename [options]
3636
*
3737
* where 'filename' can be one of the following:
3838
*'<file path>' | PROGRAM '<command>' | stdin | stdout | pstdout | pstdout
39+
* and 'query' can be one of the following:
40+
* SELECT | UPDATE | INSERT | DELETE
3941
*
4042
* An undocumented fact is that you can still write BINARY before the
4143
* tablename; this is a hangover from the pre-7.3 syntax. The options
@@ -118,7 +120,7 @@ parse_slash_copy(const char *args)
118120
gotoerror;
119121
}
120122

121-
/* Handle COPY (SELECT) case */
123+
/* Handle COPY (query) case */
122124
if (token[0]=='(')
123125
{
124126
intparens=1;

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1680,7 +1680,8 @@ typedef struct CopyStmt
16801680
{
16811681
NodeTagtype;
16821682
RangeVar*relation;/* the relation to copy */
1683-
Node*query;/* the SELECT query to copy */
1683+
Node*query;/* the query (SELECT or DML statement with
1684+
* RETURNING) to copy */
16841685
List*attlist;/* List of column names (as Strings), or NIL
16851686
* for all columns */
16861687
boolis_from;/* TO or FROM */

‎src/test/regress/expected/copydml.out

Lines changed: 112 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,112 @@
1+
--
2+
-- Test cases for COPY (INSERT/UPDATE/DELETE) TO
3+
--
4+
create table copydml_test (id serial, t text);
5+
insert into copydml_test (t) values ('a');
6+
insert into copydml_test (t) values ('b');
7+
insert into copydml_test (t) values ('c');
8+
insert into copydml_test (t) values ('d');
9+
insert into copydml_test (t) values ('e');
10+
--
11+
-- Test COPY (insert/update/delete ...)
12+
--
13+
copy (insert into copydml_test (t) values ('f') returning id) to stdout;
14+
6
15+
copy (update copydml_test set t = 'g' where t = 'f' returning id) to stdout;
16+
6
17+
copy (delete from copydml_test where t = 'g' returning id) to stdout;
18+
6
19+
--
20+
-- Test \copy (insert/update/delete ...)
21+
--
22+
\copy (insert into copydml_test (t) values ('f') returning id) to stdout;
23+
7
24+
\copy (update copydml_test set t = 'g' where t = 'f' returning id) to stdout;
25+
7
26+
\copy (delete from copydml_test where t = 'g' returning id) to stdout;
27+
7
28+
-- Error cases
29+
copy (insert into copydml_test default values) to stdout;
30+
ERROR: COPY query must have a RETURNING clause
31+
copy (update copydml_test set t = 'g') to stdout;
32+
ERROR: COPY query must have a RETURNING clause
33+
copy (delete from copydml_test) to stdout;
34+
ERROR: COPY query must have a RETURNING clause
35+
create rule qqq as on insert to copydml_test do instead nothing;
36+
copy (insert into copydml_test default values) to stdout;
37+
ERROR: DO INSTEAD NOTHING rules are not supported for COPY
38+
drop rule qqq on copydml_test;
39+
create rule qqq as on insert to copydml_test do also delete from copydml_test;
40+
copy (insert into copydml_test default values) to stdout;
41+
ERROR: DO ALSO rules are not supported for the COPY
42+
drop rule qqq on copydml_test;
43+
create rule qqq as on insert to copydml_test do instead (delete from copydml_test; delete from copydml_test);
44+
copy (insert into copydml_test default values) to stdout;
45+
ERROR: multi-statement DO INSTEAD rules are not supported for COPY
46+
drop rule qqq on copydml_test;
47+
create rule qqq as on insert to copydml_test where new.t <> 'f' do instead delete from copydml_test;
48+
copy (insert into copydml_test default values) to stdout;
49+
ERROR: conditional DO INSTEAD rules are not supported for COPY
50+
drop rule qqq on copydml_test;
51+
create rule qqq as on update to copydml_test do instead nothing;
52+
copy (update copydml_test set t = 'f') to stdout;
53+
ERROR: DO INSTEAD NOTHING rules are not supported for COPY
54+
drop rule qqq on copydml_test;
55+
create rule qqq as on update to copydml_test do also delete from copydml_test;
56+
copy (update copydml_test set t = 'f') to stdout;
57+
ERROR: DO ALSO rules are not supported for the COPY
58+
drop rule qqq on copydml_test;
59+
create rule qqq as on update to copydml_test do instead (delete from copydml_test; delete from copydml_test);
60+
copy (update copydml_test set t = 'f') to stdout;
61+
ERROR: multi-statement DO INSTEAD rules are not supported for COPY
62+
drop rule qqq on copydml_test;
63+
create rule qqq as on update to copydml_test where new.t <> 'f' do instead delete from copydml_test;
64+
copy (update copydml_test set t = 'f') to stdout;
65+
ERROR: conditional DO INSTEAD rules are not supported for COPY
66+
drop rule qqq on copydml_test;
67+
create rule qqq as on delete to copydml_test do instead nothing;
68+
copy (delete from copydml_test) to stdout;
69+
ERROR: DO INSTEAD NOTHING rules are not supported for COPY
70+
drop rule qqq on copydml_test;
71+
create rule qqq as on delete to copydml_test do also insert into copydml_test default values;
72+
copy (delete from copydml_test) to stdout;
73+
ERROR: DO ALSO rules are not supported for the COPY
74+
drop rule qqq on copydml_test;
75+
create rule qqq as on delete to copydml_test do instead (insert into copydml_test default values; insert into copydml_test default values);
76+
copy (delete from copydml_test) to stdout;
77+
ERROR: multi-statement DO INSTEAD rules are not supported for COPY
78+
drop rule qqq on copydml_test;
79+
create rule qqq as on delete to copydml_test where old.t <> 'f' do instead insert into copydml_test default values;
80+
copy (delete from copydml_test) to stdout;
81+
ERROR: conditional DO INSTEAD rules are not supported for COPY
82+
drop rule qqq on copydml_test;
83+
-- triggers
84+
create function qqq_trig() returns trigger as $$
85+
begin
86+
if tg_op in ('INSERT', 'UPDATE') then
87+
raise notice '% %', tg_op, new.id;
88+
return new;
89+
else
90+
raise notice '% %', tg_op, old.id;
91+
return old;
92+
end if;
93+
end
94+
$$ language plpgsql;
95+
create trigger qqqbef before insert or update or delete on copydml_test
96+
for each row execute procedure qqq_trig();
97+
create trigger qqqaf after insert or update or delete on copydml_test
98+
for each row execute procedure qqq_trig();
99+
copy (insert into copydml_test (t) values ('f') returning id) to stdout;
100+
NOTICE: INSERT 8
101+
8
102+
NOTICE: INSERT 8
103+
copy (update copydml_test set t = 'g' where t = 'f' returning id) to stdout;
104+
NOTICE: UPDATE 8
105+
8
106+
NOTICE: UPDATE 8
107+
copy (delete from copydml_test where t = 'g' returning id) to stdout;
108+
NOTICE: DELETE 8
109+
8
110+
NOTICE: DELETE 8
111+
drop table copydml_test;
112+
drop function qqq_trig();

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,7 @@ test: create_function_2
4848
# execute two copy tests parallel, to check that copy itself
4949
# is concurrent safe.
5050
# ----------
51-
test: copy copyselect
51+
test: copy copyselect copydml
5252

5353
# ----------
5454
# More groups of parallel tests

‎src/test/regress/serial_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,7 @@ test: create_table
5757
test: create_function_2
5858
test: copy
5959
test: copyselect
60+
test: copydml
6061
test: create_misc
6162
test: create_operator
6263
test: create_index

0 commit comments

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