diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index 74ea907..45295d1 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -25,7 +25,7 @@ PostgreSQL documentation DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] [ USING using_list ] [ WHERE condition | WHERE CURRENT OF cursor_name ] - [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] + [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] | PRIMARY KEY ] @@ -182,6 +182,17 @@ DELETE FROM [ ONLY ] table_name [ * + + + PRIMARY KEY + + + Returns the table's primary key column(s) after each row is deleted. + Cannot be combined with an output_expression. + + + + @@ -208,7 +219,9 @@ DELETE count clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) deleted by the - command. + command. PRIMARY KEY can be specified to return the + primary key value(s) for each deleted row. An error will be raised + if the table does not have a primary key. diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index a3cccb9..9fbd859 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -24,7 +24,7 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ ( column_name [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } - [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] + [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] | PRIMARY KEY ] @@ -65,7 +65,9 @@ INSERT INTO table_name [ ( RETURNING list is identical to that of the output list - of SELECT. + of SELECT. Alternatively, PRIMARY KEY will + return the primary key value(s) for each inserted row. An error will + be raised if the table does not have a primary key. @@ -186,6 +188,17 @@ INSERT INTO table_name [ ( + + + PRIMARY KEY + + + Returns the table's primary key column(s) after each row is inserted. + Cannot be combined with an output_expression. + + + + diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 7f565be..e1042ea 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -27,7 +27,7 @@ UPDATE [ ONLY ] table_name [ * ] [ ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] [ FROM from_list ] [ WHERE condition | WHERE CURRENT OF cursor_name ] - [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] + [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] | PRIMARY KEY ] @@ -56,7 +56,9 @@ UPDATE [ ONLY ] table_name [ * ] [ tables mentioned in FROM, can be computed. The new (post-update) values of the table's columns are used. The syntax of the RETURNING list is identical to that of the - output list of SELECT. + output list of SELECT. Alternatively, PRIMARY KEY + will return the primary key value(s) for each updated row. An error will + be raised if the table does not have a primary key. @@ -211,6 +213,17 @@ UPDATE [ ONLY ] table_name [ * ] [ + + + PRIMARY KEY + + + Returns the table's primary key column(s) after each row is updated. + Cannot be combined with an output_expression. + + + + diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 43530aa..08302ca 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -1810,6 +1810,20 @@ _copyFromExpr(const FromExpr *from) return newnode; } +/* + * _copyReturningClause + */ +static ReturningClause * +_copyReturningClause(const ReturningClause *from) +{ + ReturningClause *newnode = makeNode(ReturningClause); + + COPY_NODE_FIELD(returningList); + COPY_SCALAR_FIELD(returningPK); + + return newnode; +} + /* **************************************************************** * relation.h copy functions * @@ -2503,7 +2517,7 @@ _copyInsertStmt(const InsertStmt *from) COPY_NODE_FIELD(relation); COPY_NODE_FIELD(cols); COPY_NODE_FIELD(selectStmt); - COPY_NODE_FIELD(returningList); + COPY_NODE_FIELD(returningClause); COPY_NODE_FIELD(withClause); return newnode; @@ -2517,7 +2531,7 @@ _copyDeleteStmt(const DeleteStmt *from) COPY_NODE_FIELD(relation); COPY_NODE_FIELD(usingClause); COPY_NODE_FIELD(whereClause); - COPY_NODE_FIELD(returningList); + COPY_NODE_FIELD(returningClause); COPY_NODE_FIELD(withClause); return newnode; @@ -2532,7 +2546,7 @@ _copyUpdateStmt(const UpdateStmt *from) COPY_NODE_FIELD(targetList); COPY_NODE_FIELD(whereClause); COPY_NODE_FIELD(fromClause); - COPY_NODE_FIELD(returningList); + COPY_NODE_FIELD(returningClause); COPY_NODE_FIELD(withClause); return newnode; @@ -4174,6 +4188,9 @@ copyObject(const void *from) case T_FromExpr: retval = _copyFromExpr(from); break; + case T_ReturningClause: + retval = _copyReturningClause(from); + break; /* * RELATION NODES diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 2407cb7..b60f432 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -882,7 +882,7 @@ _equalInsertStmt(const InsertStmt *a, const InsertStmt *b) COMPARE_NODE_FIELD(relation); COMPARE_NODE_FIELD(cols); COMPARE_NODE_FIELD(selectStmt); - COMPARE_NODE_FIELD(returningList); + COMPARE_NODE_FIELD(returningClause); COMPARE_NODE_FIELD(withClause); return true; @@ -894,7 +894,7 @@ _equalDeleteStmt(const DeleteStmt *a, const DeleteStmt *b) COMPARE_NODE_FIELD(relation); COMPARE_NODE_FIELD(usingClause); COMPARE_NODE_FIELD(whereClause); - COMPARE_NODE_FIELD(returningList); + COMPARE_NODE_FIELD(returningClause); COMPARE_NODE_FIELD(withClause); return true; @@ -907,7 +907,7 @@ _equalUpdateStmt(const UpdateStmt *a, const UpdateStmt *b) COMPARE_NODE_FIELD(targetList); COMPARE_NODE_FIELD(whereClause); COMPARE_NODE_FIELD(fromClause); - COMPARE_NODE_FIELD(returningList); + COMPARE_NODE_FIELD(returningClause); COMPARE_NODE_FIELD(withClause); return true; diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 5a98bfb..10f6c31 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2958,7 +2958,7 @@ raw_expression_tree_walker(Node *node, return true; if (walker(stmt->selectStmt, context)) return true; - if (walker(stmt->returningList, context)) + if (walker(stmt->returningClause->returningList, context)) return true; if (walker(stmt->withClause, context)) return true; @@ -2974,7 +2974,7 @@ raw_expression_tree_walker(Node *node, return true; if (walker(stmt->whereClause, context)) return true; - if (walker(stmt->returningList, context)) + if (walker(stmt->returningClause->returningList, context)) return true; if (walker(stmt->withClause, context)) return true; @@ -2992,7 +2992,7 @@ raw_expression_tree_walker(Node *node, return true; if (walker(stmt->fromClause, context)) return true; - if (walker(stmt->returningList, context)) + if (walker(stmt->returningClause->returningList, context)) return true; if (walker(stmt->withClause, context)) return true; diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index fb6c44c..369baaa 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -44,6 +44,7 @@ #include "rewrite/rewriteManip.h" #include "utils/rel.h" +#include "rewrite/rewriteHandler.h" /* Hook for plugins to get control at end of parse analysis */ post_parse_analyze_hook_type post_parse_analyze_hook = NULL; @@ -61,7 +62,7 @@ static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt, static void determineRecursiveColTypes(ParseState *pstate, Node *larg, List *nrtargetlist); static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt); -static List *transformReturningList(ParseState *pstate, List *returningList); +static List *transformReturningClause(ParseState *pstate, ReturningClause *retClause); static Query *transformDeclareCursorStmt(ParseState *pstate, DeclareCursorStmt *stmt); static Query *transformExplainStmt(ParseState *pstate, @@ -386,7 +387,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) qual = transformWhereClause(pstate, stmt->whereClause, EXPR_KIND_WHERE, "WHERE"); - qry->returningList = transformReturningList(pstate, stmt->returningList); + qry->returningList = transformReturningClause(pstate, stmt->returningClause); /* done building the range table and jointree */ qry->rtable = pstate->p_rtable; @@ -750,13 +751,14 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) * RETURNING will work. Also, remove any namespace entries added in a * sub-SELECT or VALUES list. */ - if (stmt->returningList) + if (stmt->returningClause->returningList != NIL || stmt->returningClause->returningPK == true) { pstate->p_namespace = NIL; addRTEtoQuery(pstate, pstate->p_target_rangetblentry, false, true, true); - qry->returningList = transformReturningList(pstate, - stmt->returningList); + + qry->returningList = transformReturningClause(pstate, + stmt->returningClause); } /* done building the range table and jointree */ @@ -1947,7 +1949,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) qual = transformWhereClause(pstate, stmt->whereClause, EXPR_KIND_WHERE, "WHERE"); - qry->returningList = transformReturningList(pstate, stmt->returningList); + qry->returningList = transformReturningClause(pstate, stmt->returningClause); qry->rtable = pstate->p_rtable; qry->jointree = makeFromExpr(pstate->p_joinlist, qual); @@ -2020,19 +2022,106 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) } /* - * transformReturningList - + * transformReturningClause - * handle a RETURNING clause in INSERT/UPDATE/DELETE */ static List * -transformReturningList(ParseState *pstate, List *returningList) +transformReturningClause(ParseState *pstate, ReturningClause *retClause) { List *rlist; int save_next_resno; + List *returningList = retClause->returningList; - if (returningList == NIL) + if (returningList == NIL && !retClause->returningPK) return NIL; /* nothing to do */ /* + * RETURNING PRIMARY KEY was specified - generate returning list + * from the constraint info + */ + if (retClause->returningPK) + { + Relation rd = pstate->p_target_relation; + Bitmapset *keyCols; + int i; + TupleDesc tupdesc; + bool closeRel = false; + + Assert(returningList == NIL); + + /* If the relation is an autoupdatable view, resolve the underlying relation + * (which will either be a normal table or a foreign table; we don't care which) + */ + if(rd->rd_rel->relkind == RELKIND_VIEW) + { + Query *viewquery = get_view_query(rd); + + /* No need to have view_query_is_auto_updatable() check for updatable + * columns; we just need to know whether we need to resolve the + * underlying relation to check for primary keys + */ + if (view_query_is_auto_updatable(viewquery, false) == NULL) + { + RangeTblRef *rtr = (RangeTblRef *) linitial(viewquery->jointree->fromlist); + RangeTblEntry *base_rte = rt_fetch(rtr->rtindex, viewquery->rtable); + + while(base_rte->relkind == RELKIND_VIEW) + { + rd = RelationIdGetRelation(base_rte->relid); + viewquery = get_view_query(rd); + rtr = (RangeTblRef *) linitial(viewquery->jointree->fromlist); + base_rte = rt_fetch(rtr->rtindex, viewquery->rtable); + RelationClose(rd); + } + + Assert(base_rte->relkind == RELKIND_RELATION || base_rte->relkind == RELKIND_FOREIGN_TABLE); + + rd = RelationIdGetRelation(base_rte->relid); + closeRel = true; + } + } + + keyCols = RelationGetIndexAttrBitmap(rd, INDEX_ATTR_BITMAP_IDENTITY_KEY); + + if(bms_is_empty(keyCols)) + ereport(ERROR, + /* XXX more appropriate error code? */ + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("relation \"%s\" does not have any primary key(s)", + RelationGetRelationName(rd)))); + + /* + * Now we have the relation locked we can get the constraint + * info and use that as the returning list. + */ + tupdesc = RelationGetDescr(rd); + + /* If the relation was resolved from a view, we'll need to close it */ + if(closeRel == true) + RelationClose(rd); + + while ((i = bms_first_member(keyCols)) >= 0) + { + int attrno = (i + FirstLowInvalidHeapAttributeNumber) - 1; + Form_pg_attribute attr = tupdesc->attrs[attrno]; + + ResTarget *newnode = makeNode(ResTarget); + ColumnRef *cr = makeNode(ColumnRef); + + cr->location = -1; + cr->fields = list_make1(makeString(NameStr(attr->attname))); + + newnode->name = NULL; + newnode->indirection = NIL; + newnode->val = (Node *)cr; + newnode->location = -1; + returningList = lappend(returningList, newnode); + } + + bms_free(keyCols); + } + + /* * We need to assign resnos starting at one in the RETURNING list. Save * and restore the main tlist's value of p_next_resno, just in case * someone looks at it later (probably won't happen). diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 7b9895d..be21e93 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -343,11 +343,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); opclass_purpose opt_opfamily transaction_mode_list_or_empty OptTableFuncElementList TableFuncElementList opt_type_modifiers prep_type_clause - execute_param_clause using_clause returning_clause + execute_param_clause using_clause opt_enum_val_list enum_val_list table_func_column_list create_generic_options alter_generic_options relation_expr_list dostmt_opt_list - +%type returning_clause %type opt_fdw_options fdw_options %type fdw_option @@ -9075,7 +9075,7 @@ InsertStmt: opt_with_clause INSERT INTO qualified_name insert_rest returning_clause { $5->relation = $4; - $5->returningList = $6; + $5->returningClause = (ReturningClause *)$6; $5->withClause = $1; $$ = (Node *) $5; } @@ -9121,8 +9121,27 @@ insert_column_item: ; returning_clause: - RETURNING target_list { $$ = $2; } - | /* EMPTY */ { $$ = NIL; } + RETURNING target_list + { + ReturningClause *n = makeNode(ReturningClause); + n->returningList = $2; + n->returningPK = false; + $$ = (Node *) n; + } + | RETURNING PRIMARY KEY + { + ReturningClause *n = makeNode(ReturningClause); + n->returningList = NIL; + n->returningPK = true; + $$ = (Node *) n; + } + | /* EMPTY */ + { + ReturningClause *n = makeNode(ReturningClause); + n->returningList = NIL; + n->returningPK = false; + $$ = (Node *) n; + } ; @@ -9140,7 +9159,7 @@ DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias n->relation = $4; n->usingClause = $5; n->whereClause = $6; - n->returningList = $7; + n->returningClause = (ReturningClause *)$7; n->withClause = $1; $$ = (Node *)n; } @@ -9207,7 +9226,7 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias n->targetList = $5; n->fromClause = $6; n->whereClause = $7; - n->returningList = $8; + n->returningClause = (ReturningClause *)$8; n->withClause = $1; $$ = (Node *)n; } diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index bc58e16..341ef69 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -406,6 +406,7 @@ typedef enum NodeTag T_XmlSerialize, T_WithClause, T_CommonTableExpr, + T_ReturningClause, /* * TAGS FOR REPLICATION GRAMMAR PARSE NODES (replnodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 7e560a1..12c6274 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1011,6 +1011,21 @@ typedef struct CommonTableExpr ((Query *) (cte)->ctequery)->targetList : \ ((Query *) (cte)->ctequery)->returningList) +/* + * ReturningClause - + * representation of RETURNING clause + * + * One of returningList or returningPK should be set to indicate + * a RETURNING clause. Currently only returningList is propagated + * into the Query representation. + */ +typedef struct ReturningClause +{ + NodeTag type; + List *returningList; /* return-values list (of TargetEntry) */ + bool returningPK; /* indicates RETURNING PRIMARY KEY */ +} ReturningClause; + /***************************************************************************** * Optimizable Statements @@ -1026,12 +1041,12 @@ typedef struct CommonTableExpr */ typedef struct InsertStmt { - NodeTag type; - RangeVar *relation; /* relation to insert into */ - List *cols; /* optional: names of the target columns */ - Node *selectStmt; /* the source SELECT/VALUES, or NULL */ - List *returningList; /* list of expressions to return */ - WithClause *withClause; /* WITH clause */ + NodeTag type; + RangeVar *relation; /* Relation to insert into */ + List *cols; /* optional: names of the target columns */ + Node *selectStmt; /* the source SELECT/VALUES, or NULL */ + ReturningClause *returningClause; /* list of expressions to return */ + WithClause *withClause; /* WITH clause */ } InsertStmt; /* ---------------------- @@ -1040,12 +1055,12 @@ typedef struct InsertStmt */ typedef struct DeleteStmt { - NodeTag type; - RangeVar *relation; /* relation to delete from */ - List *usingClause; /* optional using clause for more tables */ - Node *whereClause; /* qualifications */ - List *returningList; /* list of expressions to return */ - WithClause *withClause; /* WITH clause */ + NodeTag type; + RangeVar *relation; /* relation to delete from */ + List *usingClause; /* optional using clause for more tables */ + Node *whereClause; /* qualifications */ + ReturningClause *returningClause; /* list of expressions to return */ + WithClause *withClause; /* WITH clause */ } DeleteStmt; /* ---------------------- @@ -1054,13 +1069,13 @@ typedef struct DeleteStmt */ typedef struct UpdateStmt { - NodeTag type; - RangeVar *relation; /* relation to update */ - List *targetList; /* the target list (of ResTarget) */ - Node *whereClause; /* qualifications */ - List *fromClause; /* optional from clause for more tables */ - List *returningList; /* list of expressions to return */ - WithClause *withClause; /* WITH clause */ + NodeTag type; + RangeVar *relation; /* relation to update */ + List *targetList; /* the target list (of ResTarget) */ + Node *whereClause; /* qualifications */ + List *fromClause; /* optional from clause for more tables */ + ReturningClause *returningClause; /* list of expressions to return */ + WithClause *withClause; /* WITH clause */ } UpdateStmt; /* ---------------------- diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out index 69bdacc..72466e5 100644 --- a/src/test/regress/expected/returning.out +++ b/src/test/regress/expected/returning.out @@ -331,3 +331,78 @@ SELECT * FROM voo; 17 | zoo2 (2 rows) +-- RETURNING PRIMARY KEY +CREATE TEMP TABLE retpk1 (f1 serial, f2 text, PRIMARY KEY(f1), UNIQUE(f2)); +INSERT INTO retpk1 VALUES(DEFAULT, 'foo') RETURNING PRIMARY KEY; + f1 +---- + 1 +(1 row) + +UPDATE retpk1 SET f2 = 'bar' WHERE f2 = 'foo' RETURNING PRIMARY KEY; + f1 +---- + 1 +(1 row) + +DELETE FROM retpk1 WHERE f2 = 'bar' RETURNING PRIMARY KEY; + f1 +---- + 1 +(1 row) + +CREATE TEMP TABLE retpk2 (f1 INT, f2 INT, f3 TEXT, PRIMARY KEY(f1, f2)); +INSERT INTO retpk2 VALUES(1,1, 'foo'),(1,2,'bar') RETURNING PRIMARY KEY; + f1 | f2 +----+---- + 1 | 1 + 1 | 2 +(2 rows) + +CREATE TEMP VIEW v_retpk1 AS SELECT * FROM retpk1; +INSERT INTO v_retpk1 VALUES(DEFAULT, 'foo') RETURNING PRIMARY KEY; + f1 +---- + 2 +(1 row) + +UPDATE v_retpk1 SET f2 = 'bar' WHERE f2 = 'foo' RETURNING PRIMARY KEY; + f1 +---- + 2 +(1 row) + +DELETE FROM v_retpk1 WHERE f2 = 'bar' RETURNING PRIMARY KEY; + f1 +---- + 2 +(1 row) + +-- Invalid usages +INSERT INTO retpk1 VALUES(DEFAULT, 'baz') RETURNING PRIMARY KEY, *; +ERROR: syntax error at or near "," +LINE 1: ...INTO retpk1 VALUES(DEFAULT, 'baz') RETURNING PRIMARY KEY, *; + ^ +INSERT INTO foo (f1) VALUES(DEFAULT) RETURNING PRIMARY KEY; +ERROR: relation "foo" does not have any primary key(s) +CREATE TEMP VIEW v_retpk2 AS + SELECT pk1.f1, pk1.f2, pk2.f3 FROM retpk1 pk1 JOIN retpk2 pk2 ON pk1.f1=pk2.f1; +CREATE RULE v_retpk2_i AS ON INSERT TO v_retpk2 DO INSTEAD + INSERT INTO retpk1 (f1, f2) VALUES(new.f1, new.f2); +INSERT INTO v_retpk2 VALUES(3, 'foo') RETURNING PRIMARY KEY; +ERROR: relation "v_retpk2" does not have any primary key(s) +CREATE FUNCTION f_retpk1_u() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +BEGIN + UPDATE retpk1 + SET f2 = NEW.f1 + WHERE f1 = OLD.f1; +END; +$$; +CREATE TRIGGER rw_view1_trig + INSTEAD OF UPDATE ON v_retpk2 + FOR EACH ROW EXECUTE PROCEDURE f_retpk1_u(); +UPDATE v_retpk2 SET f2 = 'bar' WHERE f1 = 3 RETURNING PRIMARY KEY; +ERROR: relation "v_retpk2" does not have any primary key(s) diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql index 0ed9a48..eb6bfcb 100644 --- a/src/test/regress/sql/returning.sql +++ b/src/test/regress/sql/returning.sql @@ -154,3 +154,41 @@ UPDATE joinview SET f1 = f1 + 1 WHERE f3 = 57 RETURNING *, other + 1; SELECT * FROM joinview; SELECT * FROM foo; SELECT * FROM voo; + +-- RETURNING PRIMARY KEY + +CREATE TEMP TABLE retpk1 (f1 serial, f2 text, PRIMARY KEY(f1), UNIQUE(f2)); +INSERT INTO retpk1 VALUES(DEFAULT, 'foo') RETURNING PRIMARY KEY; +UPDATE retpk1 SET f2 = 'bar' WHERE f2 = 'foo' RETURNING PRIMARY KEY; +DELETE FROM retpk1 WHERE f2 = 'bar' RETURNING PRIMARY KEY; + +CREATE TEMP TABLE retpk2 (f1 INT, f2 INT, f3 TEXT, PRIMARY KEY(f1, f2)); +INSERT INTO retpk2 VALUES(1,1, 'foo'),(1,2,'bar') RETURNING PRIMARY KEY; + +CREATE TEMP VIEW v_retpk1 AS SELECT * FROM retpk1; +INSERT INTO v_retpk1 VALUES(DEFAULT, 'foo') RETURNING PRIMARY KEY; +UPDATE v_retpk1 SET f2 = 'bar' WHERE f2 = 'foo' RETURNING PRIMARY KEY; +DELETE FROM v_retpk1 WHERE f2 = 'bar' RETURNING PRIMARY KEY; + +-- Invalid usages +INSERT INTO retpk1 VALUES(DEFAULT, 'baz') RETURNING PRIMARY KEY, *; +INSERT INTO foo (f1) VALUES(DEFAULT) RETURNING PRIMARY KEY; +CREATE TEMP VIEW v_retpk2 AS + SELECT pk1.f1, pk1.f2, pk2.f3 FROM retpk1 pk1 JOIN retpk2 pk2 ON pk1.f1=pk2.f1; +CREATE RULE v_retpk2_i AS ON INSERT TO v_retpk2 DO INSTEAD + INSERT INTO retpk1 (f1, f2) VALUES(new.f1, new.f2); +INSERT INTO v_retpk2 VALUES(3, 'foo') RETURNING PRIMARY KEY; +CREATE FUNCTION f_retpk1_u() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +BEGIN + UPDATE retpk1 + SET f2 = NEW.f1 + WHERE f1 = OLD.f1; +END; +$$; +CREATE TRIGGER rw_view1_trig + INSTEAD OF UPDATE ON v_retpk2 + FOR EACH ROW EXECUTE PROCEDURE f_retpk1_u(); +UPDATE v_retpk2 SET f2 = 'bar' WHERE f1 = 3 RETURNING PRIMARY KEY;