Skip to content

Commit 92e3818

Browse files
feodorfeodor
committed
COPY (INSERT/UPDATE/DELETE .. RETURNING ..)
Attached is a patch for being able to do COPY (query) without a CTE. Author: Marko Tiikkaja Review: Michael Paquier
1 parent 0da3a9b commit 92e3818

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
int raw_buf_len; /* total # of bytes stored */
202202
} CopyStateData;
203203

204-
/* DestReceiver for COPY (SELECT) TO */
204+
/* DestReceiver for COPY (query) TO */
205205
typedef struct
206206
{
207207
DestReceiver pub; /* 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 a select */
1378+
/* Don't allow COPY w/ OIDs from a query */
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+
else if (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-
| COPY select_with_parens TO opt_program copy_file_name opt_with copy_options
2613+
| COPY '(' PreparableStmt ')' TO opt_program copy_file_name opt_with copy_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
goto error;
119121
}
120122

121-
/* Handle COPY (SELECT) case */
123+
/* Handle COPY (query) case */
122124
if (token[0] == '(')
123125
{
124126
int parens = 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
NodeTag type;
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
bool is_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)