8000 Support "COPY view FROM" for views with INSTEAD OF INSERT triggers. · postgrespro/postgres_cluster@279c439 · GitHub
[go: up one dir, main page]
Skip to content

Commit 279c439

Browse files
tglsfdctglsfdc
committed
Support "COPY view FROM" for views with INSTEAD OF INSERT triggers.
We just pass the data to the INSTEAD trigger. Haribabu Kommi, reviewed by Dilip Kumar Patch: <CAJrrPGcSQkrNkO+4PhLm4B8UQQQmU9YVUuqmtgM=pmzMfxWaWQ@mail.gmail.com>
1 parent e1b449b commit 279c439

File tree

4 files changed

+125
-44
lines changed

4 files changed

+125
-44
lines changed

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

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -395,9 +395,15 @@ COPY <replaceable class="parameter">count</replaceable>
395395
<title>Notes</title>
396396

397397
<para>
398-
<command>COPY</command> can only be used with plain tables, not
398+
<command>COPY TO</command> can only be used with plain tables, not
399399
with views. However, you can write <literal>COPY (SELECT * FROM
400-
<replaceable class="parameter">viewname</replaceable>) TO ...</literal>.
400+
<replaceable class="parameter">viewname</replaceable>) TO ...</literal>
401+
to copy the current contents of a view.
402+
</para>
403+
404+
<para>
405+
<command>COPY FROM</command> can be used with plain tables and with views
406+
that have <literal>INSTEAD OF INSERT</> triggers.
401407
</para>
402408

403409
<para>

‎src/backend/commands/copy.c

Lines changed: 62 additions & 42 deletions
Original file line numberDiff line numberDiff line change
@@ -864,8 +864,8 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, uint64 *processed)
864864
* statement.
865865
*
866866
* In the case that columns are specified in the attribute list,
867-
* create a ColumnRef and ResTarget for each column and add them to
868-
* the target list for the resulting SELECT statement.
867+
* create a ColumnRef and ResTarget for each column and add them
868+
* to the target list for the resulting SELECT statement.
869869
*/
870870
if (!stmt->attlist)
871871
{
@@ -2269,13 +2269,21 @@ CopyFrom(CopyState cstate)
22692269

22702270
Assert(cstate->rel);
22712271

2272-
if (cstate->rel->rd_rel->relkind != RELKIND_RELATION)
2272+
/*
2273+
* The target must be a plain relation or have an INSTEAD OF INSERT row
2274+
* trigger. (Currently, such triggers are only allowed on views, so we
2275+
* only hint about them in the view case.)
2276+
*/
2277+
if (cstate->rel->rd_rel->relkind != RELKIND_RELATION &&
2278+
!(cstate->rel->trigdesc &&
2279+
cstate->rel->trigdesc->trig_insert_instead_row))
22732280
{
22742281
if (cstate->rel->rd_rel->relkind == RELKIND_VIEW)
22752282
ereport(ERROR,
22762283
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
22772284
errmsg("cannot copy to view \"%s\"",
2278-
RelationGetRelationName(cstate->rel))));
2285+
RelationGetRelationName(cstate->rel)),
2286+
errhint("To enable copying to a view, provide an INSTEAD OF INSERT trigger.")));
22792287
else if (cstate->rel->rd_rel->relkind == RELKIND_MATVIEW)
22802288
ereport(ERROR,
22812289
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -2496,52 +2504,64 @@ CopyFrom(CopyState cstate)
24962504

24972505
if (!skip_tuple)
24982506
{
2499-
/* Check the constraints of the tuple */
2500-
if (cstate->rel->rd_att->constr)
2501-
ExecConstraints(resultRelInfo, slot, estate);
2502-
2503-
if (useHeapMultiInsert)
2507+
if (resultRelInfo->ri_TrigDesc &&
2508+
resultRelInfo->ri_TrigDesc->trig_insert_instead_row)
25042509
{
2505-
/* Add this tuple to the tuple buffer */
2506-
if (nBufferedTuples == 0)
2507-
firstBufferedLineNo = cstate->cur_lineno;
2508-
bufferedTuples[nBufferedTuples++] = tuple;
2509-
bufferedTuplesSize += tuple->t_len;
2510-
2511-
/*
2512-
* If the buffer filled up, flush it. Also flush if the total
2513-
* size of all the tuples in the buffer becomes large, to
2514-
* avoid using large amounts of memory for the buffers when
2515-
* the tuples are exceptionally wide.
2516-
*/
2517-
if (nBufferedTuples == MAX_BUFFERED_TUPLES ||
2518-
bufferedTuplesSize > 65535)
2519-
{
2520-
CopyFromInsertBatch(cstate, estate, mycid, hi_options,
2521-
resultRelInfo, myslot, bistate,
2522-
nBufferedTuples, bufferedTuples,
2523-
firstBufferedLineNo);
2524-
nBufferedTuples = 0;
2525-
bufferedTuplesSize = 0;
2526-
}
2510+
/* Pass the data to the INSTEAD ROW INSERT trigger */
2511+
ExecIRInsertTriggers(estate, resultRelInfo, slot);
25272512
}
25282513
else
25292514
{
2530-
List *recheckIndexes = NIL;
2515+
/* Check the constraints of the tuple */
2516+
if (cstate->rel->rd_att->constr)
2517+
ExecConstraints(resultRelInfo, slot, estate);
2518+
2519+
if (useHeapMultiInsert)
2520+
{
2521+
/* Add this tuple to the tuple buffer */
2522+
if (nBufferedTuples == 0)
2523+
firstBufferedLineNo = cstate->cur_lineno;
2524+
bufferedTuples[nBufferedTuples++] = tuple;
2525+
bufferedTuplesSize += tuple->t_len;
2526+
2527+
/*
2528+
* If the buffer filled up, flush it. Also flush if the
2529+
* total size of all the tuples in the buffer becomes
2530+
* large, to avoid using large amounts of memory for the
2531+
* buffer when the tuples are exceptionally wide.
2532+
*/
2533+
if (nBufferedTuples == MAX_BUFFERED_TUPLES ||
2534+
bufferedTuplesSize > 65535)
2535+
{
2536+
CopyFromInsertBatch(cstate, estate, mycid, hi_options,
2537+
resultRelInfo, myslot, bistate,
2538+
nBufferedTuples, bufferedTuples,
2539+
firstBufferedLineNo);
2540+
nBufferedTuples = 0;
2541+
bufferedTuplesSize = 0;
2542+
}
2543+
}
2544+
else
2545+
{
2546+
List *recheckIndexes = NIL;
25312547

2532-
/* OK, store the tuple and create index entries for it */
2533-
heap_insert(cstate->rel, tuple, mycid, hi_options, bistate);
2548+
/* OK, store the tuple and create index entries for it */
2549+
heap_insert(cstate->rel, tuple, mycid, hi_options, bistate);
25342550

2535-
if (resultRelInfo->ri_NumIndices > 0)
2536-
recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self),
2537-
estate, false, NULL,
2538-
NIL);
2551+
if (resultRelInfo->ri_NumIndices > 0)
2552+
recheckIndexes = ExecInsertIndexTuples(slot,
2553+
&(tuple->t_self),
2554+
estate,
2555+
false,
2556+
NULL,
2557+
NIL);
25392558

2540-
/* AFTER ROW INSERT Triggers */
2541-
ExecARInsertTriggers(estate, resultRelInfo, tuple,
2542-
recheckIndexes);
2559+
/* AFTER ROW INSERT Triggers */
2560+
ExecARInsertTriggers(estate, resultRelInfo, tuple,
2561+
recheckIndexes);
25432562

2544-
list_free(recheckIndexes);
2563+
list_free(recheckIndexes);
2564+
}
25452565
}
25462566

25472567
/*

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

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -535,6 +535,29 @@ COPY rls_t1 (a, b) TO stdout;
535535
2 3
536536
4 1
537537
RESET SESSION AUTHORIZATION;
538+
-- test with INSTEAD OF INSERT trigger on a view
539+
CREATE TABLE instead_of_insert_tbl(id serial, name text);
540+
CREATE VIEW instead_of_insert_tbl_view AS SELECT ''::text AS str;
541+
COPY instead_of_insert_tbl_view FROM stdin; -- fail
542+
ERROR: cannot copy to view "instead_of_insert_tbl_view"
543+
HINT: To enable copying to a view, provide an INSTEAD OF INSERT trigger.
544+
CREATE FUNCTION fun_instead_of_insert_tbl() RETURNS trigger AS $$
545+
BEGIN
546+
INSERT INTO instead_of_insert_tbl (name) VALUES (NEW.str);
547+
RETURN NULL;
548+
END;
549+
$$ LANGUAGE plpgsql;
550+
CREATE TRIGGER trig_instead_of_insert_tbl_view
551+
INSTEAD OF INSERT ON instead_of_insert_tbl_view
552+
FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl();
553+
COPY instead_of_insert_tbl_view FROM stdin;
554+
SELECT * FROM instead_of_insert_tbl;
555+
id | name
556+
----+-------
557+
1 | test1
558+
(1 row)
559+
560+
-- clean up
538561
DROP TABLE forcetest;
539562
DROP TABLE vistest;
540563
DROP FUNCTION truncate_in_subxact();
@@ -544,3 +567,6 @@ DROP ROLE regress_rls_copy_user;
544567
DROP ROLE regress_rls_copy_user_colperms;
545568
DROP FUNCTION fn_x_before();
546569
DROP FUNCTION fn_x_after();
570+
DROP TABLE instead_of_insert_tbl;
571+
DROP VIEW instead_of_insert_tbl_view;
572+
DROP FUNCTION fun_instead_of_insert_tbl();

‎src/test/regress/sql/copy2.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -387,6 +387,32 @@ COPY rls_t1 (a, b) TO stdout;
387387

388388
RESET SESSION AUTHORIZATION;
389389

390+
-- test with INSTEAD OF INSERT trigger on a view
391+
CREATE TABLE instead_of_insert_tbl(id serial, name text);
392+
CREATE VIEW instead_of_insert_tbl_view AS SELECT ''::text AS str;
393+
394+
COPY instead_of_insert_tbl_view FROM stdin; -- fail
395+
test1
396+
\.
397+
398+
CREATE FUNCTION fun_instead_of_insert_tbl() RETURNS trigger AS $$
399+
BEGIN
400+
INSERT INTO instead_of_insert_tbl (name) VALUES (NEW.str);
401+
RETURN NULL;
402+
END;
403+
$$ LANGUAGE plpgsql;
404+
CREATE TRIGGER trig_instead_of_insert_tbl_view
405+
INSTEAD OF INSERT ON instead_of_insert_tbl_view
406+
FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl();
407+
408+
COPY instead_of_insert_tbl_view FROM stdin;
409+
test1
410+
\.
411+
412+
SELECT * FROM instead_of_insert_tbl;
413+
414+
415+
-- clean up
390416
DROP TABLE forcetest;
391417
DROP TABLE vistest;
392418
DROP FUNCTION truncate_in_subxact();
@@ -396,3 +422,6 @@ DROP ROLE regress_rls_copy_user;
396422
DROP ROLE regress_rls_copy_user_colperms;
397423
DROP FUNCTION fn_x_before();
398424
DROP FUNCTION fn_x_after();
425+
DROP TABLE instead_of_insert_tbl;
426+
DROP VIEW instead_of_insert_tbl_view;
427+
DROP FUNCTION fun_instead_of_insert_tbl();

0 commit comments

Comments
 (0)
0