On 25/06/14 16:04, Ian Barwick wrote:
Hi
On 14/06/25 15:13, Rushabh Lathia wrote:
Hello All,
I assigned my self as reviewer of the patch. I gone through the
mail chain discussion and in that question has been raised about
the feature and its implementation, so would like to know what is
the current status of this project/patch.
Regards,
I'll be submitting a revised version of this patch very shortly.
Revised version of the patch attached, which implements the expansion
of "primary key" in the rewrite phase per Tom Lane's suggestion upthread [*]
[*] http://www.postgresql.org/message-id/28583.1402325...@sss.pgh.pa.us
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
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 ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
[ USING <replaceable class="PARAMETER">using_list</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
- [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
+ [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] | PRIMARY KEY ]
</synopsis>
</refsynopsisdiv>
@@ -182,6 +182,17 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ *
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>PRIMARY KEY</literal></term>
+ <listitem>
+ <para>
+ Returns the table's primary key column(s) after each row is deleted.
+ Cannot be combined with an <replaceable class="PARAMETER">output_expression</replaceable>.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
@@ -208,7 +219,9 @@ DELETE <replaceable class="parameter">count</replaceable>
clause, the result will be similar to that of a <command>SELECT</>
statement containing the columns and values defined in the
<literal>RETURNING</> list, computed over the row(s) deleted by the
- command.
+ command. <literal>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.
</para>
</refsect1>
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 ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
- [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
+ [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] | PRIMARY KEY ]
</synopsis>
</refsynopsisdiv>
@@ -65,7 +65,9 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace
defaults, such as a serial sequence number. However, any expression
using the table's columns is allowed. The syntax of the
<literal>RETURNING</> list is identical to that of the output list
- of <command>SELECT</>.
+ of <command>SELECT</>. Alternatively, <literal>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.
</para>
<para>
@@ -186,6 +188,17 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>PRIMARY KEY</literal></term>
+ <listitem>
+ <para>
+ Returns the table's primary key column(s) after each row is inserted.
+ Cannot be combined with an <replaceable class="PARAMETER">output_expression</replaceable>.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 35b0699..27c49c4 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -29,7 +29,7 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [
} [, ...]
[ FROM <replaceable class="PARAMETER">from_list</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
- [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
+ [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] | PRIMARY KEY ]
</synopsis>
</refsynopsisdiv>
@@ -58,7 +58,9 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [
tables mentioned in <literal>FROM</literal>, can be computed.
The new (post-update) values of the table's columns are used.
The syntax of the <literal>RETURNING</> list is identical to that of the
- output list of <command>SELECT</>.
+ output list of <command>SELECT</>. Alternatively, <literal>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.
</para>
<para>
@@ -228,6 +230,17 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>PRIMARY KEY</literal></term>
+ <listitem>
+ <para>
+ Returns the table's primary key column(s) after each row is updated.
+ Cannot be combined with an <replaceable class="PARAMETER">output_expression</replaceable>.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 8d3d5a7..ae604e7 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2517,6 +2517,7 @@ _copyInsertStmt(const InsertStmt *from)
COPY_NODE_FIELD(cols);
COPY_NODE_FIELD(selectStmt);
COPY_NODE_FIELD(returningList);
+ COPY_SCALAR_FIELD(returningPK);
COPY_NODE_FIELD(withClause);
return newnode;
@@ -2531,6 +2532,7 @@ _copyDeleteStmt(const DeleteStmt *from)
COPY_NODE_FIELD(usingClause);
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(returningList);
+ COPY_SCALAR_FIELD(returningPK);
COPY_NODE_FIELD(withClause);
return newnode;
@@ -2546,6 +2548,7 @@ _copyUpdateStmt(const UpdateStmt *from)
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(fromClause);
COPY_NODE_FIELD(returningList);
+ COPY_SCALAR_FIELD(returningPK);
COPY_NODE_FIELD(withClause);
return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index e7b49f6..6f43dc7 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -884,6 +884,7 @@ _equalInsertStmt(const InsertStmt *a, const InsertStmt *b)
COMPARE_NODE_FIELD(cols);
COMPARE_NODE_FIELD(selectStmt);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_SCALAR_FIELD(returningPK);
COMPARE_NODE_FIELD(withClause);
return true;
@@ -896,6 +897,7 @@ _equalDeleteStmt(const DeleteStmt *a, const DeleteStmt *b)
COMPARE_NODE_FIELD(usingClause);
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_SCALAR_FIELD(returningPK);
COMPARE_NODE_FIELD(withClause);
return true;
@@ -909,6 +911,7 @@ _equalUpdateStmt(const UpdateStmt *a, const UpdateStmt *b)
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(fromClause);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_SCALAR_FIELD(returningPK);
COMPARE_NODE_FIELD(withClause);
return true;
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index fb6c44c..9984339 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -387,6 +387,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
EXPR_KIND_WHERE, "WHERE");
qry->returningList = transformReturningList(pstate, stmt->returningList);
+ qry->returningPK = stmt->returningPK;
/* done building the range table and jointree */
qry->rtable = pstate->p_rtable;
@@ -758,6 +759,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
qry->returningList = transformReturningList(pstate,
stmt->returningList);
}
+ qry->returningPK = stmt->returningPK;
/* done building the range table and jointree */
qry->rtable = pstate->p_rtable;
@@ -1948,6 +1950,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
EXPR_KIND_WHERE, "WHERE");
qry->returningList = transformReturningList(pstate, stmt->returningList);
+ qry->returningPK = stmt->returningPK;
qry->rtable = pstate->p_rtable;
qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 605c9b4..d40ad75 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -9075,10 +9075,19 @@ DeallocateStmt: DEALLOCATE name
*****************************************************************************/
InsertStmt:
- opt_with_clause INSERT INTO qualified_name insert_rest returning_clause
+ opt_with_clause INSERT INTO qualified_name insert_rest RETURNING PRIMARY KEY
+ {
+ $5->relation = $4;
+ $5->returningList = NIL;
+ $5->returningPK = true;
+ $5->withClause = $1;
+ $$ = (Node *) $5;
+ }
+ | opt_with_clause INSERT INTO qualified_name insert_rest returning_clause
{
$5->relation = $4;
$5->returningList = $6;
+ $5->returningPK = false;
$5->withClause = $1;
$$ = (Node *) $5;
}
@@ -9137,6 +9146,19 @@ returning_clause:
*****************************************************************************/
DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
+ using_clause where_or_current_clause RETURNING PRIMARY KEY
+ {
+ DeleteStmt *n = makeNode(DeleteStmt);
+ n->relation = $4;
+ n->usingClause = $5;
+ n->whereClause = $6;
+ n->returningList = NIL;
+ n->returningPK = true;
+ n->withClause = $1;
+ $$ = (Node *)n;
+ }
+
+ | opt_with_clause DELETE_P FROM relation_expr_opt_alias
using_clause where_or_current_clause returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
@@ -9144,6 +9166,7 @@ DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
n->usingClause = $5;
n->whereClause = $6;
n->returningList = $7;
+ n->returningPK = false;
n->withClause = $1;
$$ = (Node *)n;
}
@@ -9203,6 +9226,23 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
SET set_clause_list
from_clause
where_or_current_clause
+ RETURNING PRIMARY KEY
+ {
+ UpdateStmt *n = makeNode(UpdateStmt);
+ n->relation = $3;
+ n->targetList = $5;
+ n->fromClause = $6;
+ n->whereClause = $7;
+ n->returningList = NIL;
+ n->returningPK = true;
+ n->withClause = $1;
+ $$ = (Node *)n;
+ }
+
+ | opt_with_clause UPDATE relation_expr_opt_alias
+ SET set_clause_list
+ from_clause
+ where_or_current_clause
returning_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
@@ -9211,6 +9251,7 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
n->fromClause = $6;
n->whereClause = $7;
n->returningList = $8;
+ n->returningPK = false;
n->withClause = $1;
$$ = (Node *)n;
}
diff --git a/src/backend/rewrite/rewriteDefine.c b/src/backend/rewrite/rewriteDefine.c
index 50ecf7e..2f6c65c 100644
--- a/src/backend/rewrite/rewriteDefine.c
+++ b/src/backend/rewrite/rewriteDefine.c
@@ -469,7 +469,7 @@ DefineQueryRewrite(char *rulename,
{
query = (Query *) lfirst(l);
- if (!query->returningList)
+ if (!query->returningList && !query->returningPK)
continue;
if (haveReturning)
ereport(ERROR,
@@ -484,6 +484,16 @@ DefineQueryRewrite(char *rulename,
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("RETURNING lists are not supported in non-INSTEAD rules")));
+
+ /* Handle RETURNING PRIMARY KEY */
+ if(query->returningPK)
+ {
+ RangeTblEntry *rte = (RangeTblEntry *) list_nth(query->rtable, query->resultRelation - 1);
+ Relation rel = heap_open(rte->relid, RowExclusiveLock);
+ query->returningList = map_primary_key_to_list(rel, query);
+ heap_close(rel, NoLock);
+ }
+
checkRuleResultList(query->returningList,
RelationGetDescr(event_relation),
false, false);
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index e6c5530..f5c6ddf 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3003,6 +3003,10 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
*/
rt_entry_relation = heap_open(rt_entry->relid, NoLock);
+ /* Handle RETURNING PRIMARY KEY */
+ if(parsetree->returningPK)
+ parsetree->returningList = map_primary_key_to_list(rt_entry_relation, parsetree);
+
/*
* Rewrite the targetlist as needed for the command type.
*/
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index fb20314..f350284 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -13,6 +13,8 @@
*/
#include "postgres.h"
+#include "access/sysattr.h"
+#include "catalog/catalog.h"
#include "catalog/pg_type.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -21,7 +23,9 @@
#include "parser/parse_coerce.h"
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
+#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
+#include "utils/rel.h"
typedef struct
@@ -904,7 +908,6 @@ getInsertSelectQuery(Query *parsetree, Query ***subquery_ptr)
return parsetree;
if (parsetree->commandType != CMD_INSERT)
return parsetree;
-
/*
* Currently, this is ONLY applied to rule-action queries, and so we
* expect to find the OLD and NEW placeholder entries in the given query.
@@ -917,6 +920,7 @@ getInsertSelectQuery(Query *parsetree, Query ***subquery_ptr)
strcmp(rt_fetch(PRS2_NEW_VARNO, parsetree->rtable)->eref->aliasname,
"new") == 0)
return parsetree;
+
Assert(parsetree->jointree && IsA(parsetree->jointree, FromExpr));
if (list_length(parsetree->jointree->fromlist) != 1)
elog(ERROR, "expected to find SELECT subquery");
@@ -1430,3 +1434,133 @@ ReplaceVarsFromTargetList(Node *node,
(void *) &context,
outer_hasSubLinks);
}
+
+
+/* map_primary_key_to_list() - returns the relation's primary key column(s)
+ * as a list of target entrys suitable for populating a query's returningList.
+ * An error is raised if the relation (or the base relation in the case of
+ * an updatable view) has no primary key, or if the relation is an updatable
+ * view where not all of the base relation's primary key columns are visible.
+ */
+List *
+map_primary_key_to_list(Relation rel, Query *parsetree)
+{
+ Bitmapset *keyCols;
+ TupleDesc tupdesc;
+ int i, varattno = 1;
+ List *returningList = NIL;
+ bool closeRel = false;
+ Relation rd = rel;
+ TargetEntry *te;
+ Relation rd_orig = rel;
+ TupleDesc tupdesc_orig;
+
+ /* 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\" has no primary key",
+ RelationGetRelationName(rd))));
+
+ tupdesc = RelationGetDescr(rd);
+ tupdesc_orig = RelationGetDescr(rd_orig);
+
+ while ((i = bms_first_member(keyCols)) >= 0)
+ {
+ int attrno = (i + FirstLowInvalidHeapAttributeNumber) - 1;
+ Form_pg_attribute attr = tupdesc->attrs[attrno];
+ Var *newvar;
+ char *pk_attname = NameStr(attr->attname);
+ bool col_is_visible = false;
+
+ /*
+ * If base and target relations are not the same, check that the
+ * base relation's primary key column is visible in the original
+ * target relation
+ */
+ if(rd != rd_orig)
+ {
+ int j;
+ int numberOfAttributes = tupdesc_orig->natts;
+ Form_pg_attribute *attrs = tupdesc_orig->attrs;
+
+ for (j = 0; j < numberOfAttributes; j++)
+ {
+ char *orig_attname = NameStr(attrs[j]->attname);
+
+ if(strcmp(orig_attname, pk_attname) == 0)
+ col_is_visible = true;
+ }
+
+ if(col_is_visible == false)
+ ereport(ERROR,
+ /* XXX more appropriate error message, code? */
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("primary key not available for relation \"%s\"",
+ RelationGetRelationName(rd_orig))));
+ }
+
+ newvar = makeVar(
+ parsetree->resultRelation,
+ varattno,
+ attr->atttypid,
+ attr->atttypmod,
+ attr->attcollation,
+ 0
+ );
+
+ te = makeTargetEntry((Expr *) newvar,
+ varattno,
+ pstrdup(pk_attname),
+ false);
+
+ te->resorigtbl = rd->rd_id;
+ te->resorigcol = newvar->varattno;
+
+ returningList = lappend(returningList, te);
+
+ varattno++;
+ }
+
+ /* If the relation was resolved from a view, we'll need to close it */
+ if(closeRel == true)
+ RelationClose(rd);
+
+ bms_free(keyCols);
+
+ return returningList;
+}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ff126eb..5173449 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -131,6 +131,7 @@ typedef struct Query
List *withCheckOptions; /* a list of WithCheckOption's */
List *returningList; /* return-values list (of TargetEntry) */
+ bool returningPK; /* RETURNING PRIMARY KEY specified */
List *groupClause; /* a list of SortGroupClause's */
@@ -1045,6 +1046,7 @@ typedef struct InsertStmt
List *cols; /* optional: names of the target columns */
Node *selectStmt; /* the source SELECT/VALUES, or NULL */
List *returningList; /* list of expressions to return */
+ bool returningPK; /* RETURNING PRIMARY KEY specified */
WithClause *withClause; /* WITH clause */
} InsertStmt;
@@ -1059,6 +1061,7 @@ typedef struct DeleteStmt
List *usingClause; /* optional using clause for more tables */
Node *whereClause; /* qualifications */
List *returningList; /* list of expressions to return */
+ bool returningPK; /* RETURNING PRIMARY KEY specified */
WithClause *withClause; /* WITH clause */
} DeleteStmt;
@@ -1074,6 +1077,7 @@ typedef struct UpdateStmt
Node *whereClause; /* qualifications */
List *fromClause; /* optional from clause for more tables */
List *returningList; /* list of expressions to return */
+ bool returningPK; /* RETURNING PRIMARY KEY specified */
WithClause *withClause; /* WITH clause */
} UpdateStmt;
diff --git a/src/include/rewrite/rewriteManip.h b/src/include/rewrite/rewriteManip.h
index c2e4af4..2800829 100644
--- a/src/include/rewrite/rewriteManip.h
+++ b/src/include/rewrite/rewriteManip.h
@@ -15,7 +15,7 @@
#define REWRITEMANIP_H
#include "nodes/parsenodes.h"
-
+#include "utils/rel.h"
typedef struct replace_rte_variables_context replace_rte_variables_context;
@@ -82,4 +82,6 @@ extern Node *ReplaceVarsFromTargetList(Node *node,
int nomatch_varno,
bool *outer_hasSubLinks);
+extern List *map_primary_key_to_list(Relation rel, Query *parsetree);
+
#endif /* REWRITEMANIP_H */
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
index 69bdacc..65fb8f6 100644
--- a/src/test/regress/expected/returning.out
+++ b/src/test/regress/expected/returning.out
@@ -331,3 +331,100 @@ SELECT * FROM voo;
17 | zoo2
(2 rows)
+-- RETURNING PRIMARY KEY
+-- On base table
+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)
+
+-- On base table with multi-column 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;
+ f1 | f2
+----+----
+ 1 | 1
+ 1 | 2
+(2 rows)
+
+-- On updatable view
+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)
+
+-- On view with DO INSTEAD rule
+CREATE TEMP VIEW v_retpk2 AS SELECT f1 FROM retpk1;
+CREATE RULE v_retpk2_rule AS ON INSERT TO v_retpk2 DO INSTEAD
+ INSERT INTO retpk1(f1) VALUES(new.f1) RETURNING PRIMARY KEY;
+INSERT INTO v_retpk2 VALUES(1) RETURNING PRIMARY KEY;
+ f1
+----
+ 1
+(1 row)
+
+-- On view with INSTEAD OF TRIGGER
+CREATE OR REPLACE FUNCTION f_retpk1_i()
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ INSERT INTO retpk1(f1, f2) VALUES(new.f1, new.f2);
+ RETURN NEW;
+END;
+$$;
+CREATE TRIGGER rw_view1_trig
+ INSTEAD OF INSERT ON v_retpk1
+ FOR EACH ROW EXECUTE PROCEDURE f_retpk1_i();
+INSERT INTO v_retpk1 (f1, f2) values(99,'xx') RETURNING PRIMARY KEY;
+ f1
+----
+ 99
+(1 row)
+
+-- Invalid usages
+-- Cannot be combined with column specifications
+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, *;
+ ^
+-- Error if no primary key present
+INSERT INTO foo (f1) VALUES(DEFAULT) RETURNING PRIMARY KEY;
+ERROR: relation "foo" has no primary key
+-- Primary key must not be "leaked" from view
+CREATE TEMP VIEW v_retpk3 AS SELECT f2 FROM retpk1;
+INSERT INTO retpk1 (f1, f2) VALUES(DEFAULT, 'foo');
+UPDATE v_retpk3 SET f2 = 'bar' WHERE f2 = 'foo' RETURNING PRIMARY KEY;
+ERROR: primary key not available for relation "v_retpk3"
+-- On DO INSTEAD rule, returning list column count must match
+CREATE RULE v_retpk1_rule AS ON INSERT TO v_retpk1 DO INSTEAD
+ INSERT INTO retpk1 VALUES(new.*) RETURNING PRIMARY KEY;
+ERROR: RETURNING list has too few entries
diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql
index 0ed9a48..df193b0 100644
--- a/src/test/regress/sql/returning.sql
+++ b/src/test/regress/sql/returning.sql
@@ -154,3 +154,62 @@ UPDATE joinview SET f1 = f1 + 1 WHERE f3 = 57 RETURNING *, other + 1;
SELECT * FROM joinview;
SELECT * FROM foo;
SELECT * FROM voo;
+
+-- RETURNING PRIMARY KEY
+
+-- On base table
+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;
+
+-- On base table with multi-column 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;
+
+-- On updatable view
+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;
+
+-- On view with DO INSTEAD rule
+CREATE TEMP VIEW v_retpk2 AS SELECT f1 FROM retpk1;
+CREATE RULE v_retpk2_rule AS ON INSERT TO v_retpk2 DO INSTEAD
+ INSERT INTO retpk1(f1) VALUES(new.f1) RETURNING PRIMARY KEY;
+INSERT INTO v_retpk2 VALUES(1) RETURNING PRIMARY KEY;
+
+
+-- On view with INSTEAD OF TRIGGER
+CREATE OR REPLACE FUNCTION f_retpk1_i()
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ INSERT INTO retpk1(f1, f2) VALUES(new.f1, new.f2);
+ RETURN NEW;
+END;
+$$;
+
+CREATE TRIGGER rw_view1_trig
+ INSTEAD OF INSERT ON v_retpk1
+ FOR EACH ROW EXECUTE PROCEDURE f_retpk1_i();
+
+INSERT INTO v_retpk1 (f1, f2) values(99,'xx') RETURNING PRIMARY KEY;
+
+-- Invalid usages
+
+-- Cannot be combined with column specifications
+INSERT INTO retpk1 VALUES(DEFAULT, 'baz') RETURNING PRIMARY KEY, *;
+
+-- Error if no primary key present
+INSERT INTO foo (f1) VALUES(DEFAULT) RETURNING PRIMARY KEY;
+
+-- Primary key must not be "leaked" from view
+CREATE TEMP VIEW v_retpk3 AS SELECT f2 FROM retpk1;
+INSERT INTO retpk1 (f1, f2) VALUES(DEFAULT, 'foo');
+UPDATE v_retpk3 SET f2 = 'bar' WHERE f2 = 'foo' RETURNING PRIMARY KEY;
+
+-- On DO INSTEAD rule, returning list column count must match
+CREATE RULE v_retpk1_rule AS ON INSERT TO v_retpk1 DO INSTEAD
+ INSERT INTO retpk1 VALUES(new.*) RETURNING PRIMARY KEY;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
