Hi,
The JDBC API provides the getGeneratedKeys() method as a way of retrieving
primary key values without the need to explicitly specify the primary key
column(s). This is a widely-used feature, however the implementation has
significant
performance drawbacks.
Currently this feature is implemented in the JDBC driver by appending
"RETURNING *" to the supplied statement. However this means all columns of
affected rows will be returned to the client, which causes significant
performance problems, particularly on wide tables. To mitigate this, it would
be desirable to enable the JDBC driver to request only the primary key value(s).
One possible solution would be to have the driver request the primary key for
a table, but this could cause a race condition where the primary key could
change,
and even if it does not, it would entail extra overhead.
A more elegant and universal solution, which would allow the JDBC driver to
request the primary key in a single request, would be to extend the RETURNING
clause syntax with the option PRIMARY KEY. This resolves during parse
analysis into the columns of the primary key, which can be done unambiguously
because the table is already locked by that point and the primary key cannot
change.
A patch is attached which implements this, and will be added to the next
commitfest.
A separate patch will be submitted to the JDBC project. Example usage shown
below.
Regards
Ian Barwick
/* ---------------------------------------------- */
postgres=# CREATE TABLE foo (id SERIAL PRIMARY KEY);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(DEFAULT) RETURNING PRIMARY KEY;
id
----
1
(1 row)
INSERT 0 1
postgres=# CREATE TABLE bar (id1 INT NOT NULL, id2 INT NOT NULL, PRIMARY
KEY(id1, id2));
CREATE TABLE
postgres=# INSERT INTO bar VALUES(1,2) RETURNING PRIMARY KEY;
id1 | id2
-----+-----
1 | 2
(1 row)
INSERT 0 1
postgres=# INSERT INTO bar VALUES(2,1),(2,2) RETURNING PRIMARY KEY;
id1 | id2
-----+-----
2 | 1
2 | 2
(2 rows)
INSERT 0 2
postgres=# CREATE TABLE no_pkey (id SERIAL NOT NULL);
CREATE TABLE
postgres=# INSERT INTO no_pkey VALUES(DEFAULT) RETURNING id;
id
----
1
(1 row)
INSERT 0 1
postgres=# INSERT INTO no_pkey VALUES(DEFAULT) RETURNING PRIMARY KEY;
ERROR: Relation does not have any primary key(s)
/* ---------------------------------------------- */
--
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 7f565be..e1042ea 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -27,7 +27,7 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [
( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
[ 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>
@@ -56,7 +56,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>
@@ -211,6 +213,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 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 <node> returning_clause
%type <list> opt_fdw_options fdw_options
%type <defelt> 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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
