Why the "UPDATE tab SET tab.col" is invalid?
Hi,
I found "Do not include the table's name in the specification of a target column
— for example, UPDATE tab SET tab.col = 1 is invalid." in the documentation.
Some people usually like to update table by alias. They want to add this feature.
So I get the source in the gram.y, and I found that they just take the alias as the name of column.
So I think we could analyze whether the "indirection" is alias or other.
The attachment is my patch to support - update table by alias:
CREATE TABLE update_test1 (
a INT, b INT, c TEXT
);
INSERT INTO update_test1 VALUES (1, 10, 'aa'),(2, 11, 'bb'),(3, 12, 'cc'),(4, 13, 'dd');
SELECT * FROM update_test1;
a | b | c
---+----+----
1 | 10 | aa
2 | 11 | bb
3 | 12 | cc
4 | 13 | dd
(4 rows)
UPDATE update_test1 SET tb1.a = 4 WHERE a = 1;
ERROR: column "a" of relation "tb1" does not exist
LINE 1: UPDATE update_test1 SET tb1.a = 4 WHERE a = 1;
^
UPDATE update_test1 tb1 SET tb1.f = 4 WHERE tb1.a = 1;
ERROR: column "f" of relation "tb1" does not exist
LINE 1: UPDATE update_test1 tb1 SET tb1.f = 4 WHERE tb1.a = 1;
^
UPDATE update_test1 tb SET tb1.a = 4 WHERE tb.a = 1;
ERROR: Perhaps you meant to reference the table alias "tb".
UPDATE update_test1 tb SET tb1.f = 4 WHERE tb.a = 1;
ERROR: Perhaps you meant to reference the table alias "tb".
UPDATE update_test1 tb1 SET tb1.a = 5 WHERE tb1.a = 1;
UPDATE update_test1 tb1 SET update_test1.a = 6 WHERE tb1.a = 2;
UPDATE update_test1 SET update_test1.a = 7 WHERE a = 3;
UPDATE update_test1 tb1 SET a = 8 WHERE a = 4;
SELECT * FROM update_test1;
a | b | c
---+----+----
5 | 10 | aa
6 | 11 | bb
7 | 12 | cc
8 | 13 | dd
(4 rows)
2016-04-07
WANGSHUO
A PGer
Attachments:
updateforcolalias.patchapplication/octet-stream; name=updateforcolalias.patchDownload
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 35b0699..4e3e3e7 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -23,9 +23,9 @@ PostgreSQL documentation
<synopsis>
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
- SET { <replaceable class="PARAMETER">column_name</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
- ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) |
- ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( <replaceable class="PARAMETER">sub-SELECT</replaceable> )
+ SET { <replaceable class="PARAMETER">[table_name.] column_name</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
+ ( <replaceable class="PARAMETER">[table_name.] column_name</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) |
+ ( <replaceable class="PARAMETER">[table_name.] column_name</replaceable> [, ...] ) = ( <replaceable class="PARAMETER">sub-SELECT</replaceable> )
} [, ...]
[ FROM <replaceable class="PARAMETER">from_list</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 7d2fedf..f35ed22 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -65,7 +65,7 @@ static void determineRecursiveColTypes(ParseState *pstate,
static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
static List *transformReturningList(ParseState *pstate, List *returningList);
static List *transformUpdateTargetList(ParseState *pstate,
- List *targetList);
+ List *targetList, RangeVar *relation);
static Query *transformDeclareCursorStmt(ParseState *pstate,
DeclareCursorStmt *stmt);
static Query *transformExplainStmt(ParseState *pstate,
@@ -976,7 +976,7 @@ transformOnConflictClause(ParseState *pstate,
false, true, true);
onConflictSet =
- transformUpdateTargetList(pstate, onConflictClause->targetList);
+ transformUpdateTargetList(pstate, onConflictClause->targetList, NULL);
onConflictWhere = transformWhereClause(pstate,
onConflictClause->whereClause,
@@ -2094,7 +2094,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
* Now we are done with SELECT-like processing, and can get on with
* transforming the target list to match the UPDATE target columns.
*/
- qry->targetList = transformUpdateTargetList(pstate, stmt->targetList);
+ qry->targetList = transformUpdateTargetList(pstate, stmt->targetList, stmt->relation);
qry->rtable = pstate->p_rtable;
qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
@@ -2111,7 +2111,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
* handle SET clause in UPDATE/INSERT ... ON CONFLICT UPDATE
*/
static List *
-transformUpdateTargetList(ParseState *pstate, List *origTlist)
+transformUpdateTargetList(ParseState *pstate, List *origTlist, RangeVar *relation)
{
List *tlist = NIL;
RangeTblEntry *target_rte;
@@ -2134,6 +2134,7 @@ transformUpdateTargetList(ParseState *pstate, List *origTlist)
TargetEntry *tle = (TargetEntry *) lfirst(tl);
ResTarget *origTarget;
int attrno;
+ char *colname_temp = NULL;
if (tle->resjunk)
{
@@ -2154,18 +2155,67 @@ transformUpdateTargetList(ParseState *pstate, List *origTlist)
attrno = attnameAttNum(pstate->p_target_relation,
origTarget->name, true);
+
+ if(attrno == InvalidAttrNumber && relation)
+ {
+ if(origTarget->indirection)
+ {
+ colname_temp = strVal(lfirst(list_head(origTarget->indirection)));
+
+ if(relation->alias)
+ {
+ if(relation->alias->aliasname)
+ {
+ if(strcmp(origTarget->name, relation->alias->aliasname) && strcmp(origTarget->name, relation->relname))
+ ereport(ERROR,(errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("Perhaps you meant to reference the table alias \"%s\".",
+ relation->alias->aliasname)));
+ else
+ {
+ if(colname_temp)
+ attrno = attnameAttNum(pstate->p_target_relation,
+ colname_temp, true);
+ }
+ }
+ }
+ else
+ {
+ if(strcmp(origTarget->name, relation->relname) == 0)
+ {
+ if(colname_temp)
+ attrno = attnameAttNum(pstate->p_target_relation,
+ colname_temp, true);
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colname_temp, origTarget->name),
+ parser_errposition(pstate, origTarget->location)));
+ }
+ }
+
+ }
+
if (attrno == InvalidAttrNumber)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("column \"%s\" of relation \"%s\" does not exist",
- origTarget->name,
- RelationGetRelationName(pstate->p_target_relation)),
+ colname_temp?colname_temp:origTarget->name,
+ colname_temp?origTarget->name:RelationGetRelationName(pstate->p_target_relation)),
parser_errposition(pstate, origTarget->location)));
- updateTargetListEntry(pstate, tle, origTarget->name,
+
+ if(NULL == colname_temp)
+ updateTargetListEntry(pstate, tle, origTarget->name,
attrno,
origTarget->indirection,
origTarget->location);
+ else
+ updateTargetListEntry(pstate, tle, colname_temp,
+ attrno,
+ NULL,
+ origTarget->location);
/* Mark the target column as requiring update permissions */
target_rte->updatedCols = bms_add_member(target_rte->updatedCols,
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index adc1fd7..e7e5843 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -182,3 +182,44 @@ INSERT INTO upsert_test VALUES (1, 'Bat') ON CONFLICT(a)
DROP TABLE update_test;
DROP TABLE upsert_test;
+CREATE TABLE update_test1 (
+ a INT,
+ b INT,
+ c TEXT
+);
+INSERT INTO update_test1 VALUES (1, 10, 'aa'),(2, 11, 'bb'),(3, 12, 'cc'),(4, 13, 'dd');
+SELECT * FROM update_test1;
+ a | b | c
+---+----+----
+ 1 | 10 | aa
+ 2 | 11 | bb
+ 3 | 12 | cc
+ 4 | 13 | dd
+(4 rows)
+
+UPDATE update_test1 SET tb1.a = 4 WHERE a = 1;
+ERROR: column "a" of relation "tb1" does not exist
+LINE 1: UPDATE update_test1 SET tb1.a = 4 WHERE a = 1;
+ ^
+UPDATE update_test1 tb1 SET tb1.f = 4 WHERE tb1.a = 1;
+ERROR: column "f" of relation "tb1" does not exist
+LINE 1: UPDATE update_test1 tb1 SET tb1.f = 4 WHERE tb1.a = 1;
+ ^
+UPDATE update_test1 tb SET tb1.a = 4 WHERE tb.a = 1;
+ERROR: Perhaps you meant to reference the table alias "tb".
+UPDATE update_test1 tb SET tb1.f = 4 WHERE tb.a = 1;
+ERROR: Perhaps you meant to reference the table alias "tb".
+UPDATE update_test1 tb1 SET tb1.a = 5 WHERE tb1.a = 1;
+UPDATE update_test1 tb1 SET update_test1.a = 6 WHERE tb1.a = 2;
+UPDATE update_test1 SET update_test1.a = 7 WHERE a = 3;
+UPDATE update_test1 tb1 SET a = 8 WHERE a = 4;
+SELECT * FROM update_test1;
+ a | b | c
+---+----+----
+ 5 | 10 | aa
+ 6 | 11 | bb
+ 7 | 12 | cc
+ 8 | 13 | dd
+(4 rows)
+
+DROP TABLE update_test1;
diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql
index 5637c68..ab59fc2 100644
--- a/src/test/regress/sql/update.sql
+++ b/src/test/regress/sql/update.sql
@@ -96,3 +96,26 @@ INSERT INTO upsert_test VALUES (1, 'Bat') ON CONFLICT(a)
DROP TABLE update_test;
DROP TABLE upsert_test;
+
+CREATE TABLE update_test1 (
+ a INT,
+ b INT,
+ c TEXT
+);
+
+INSERT INTO update_test1 VALUES (1, 10, 'aa'),(2, 11, 'bb'),(3, 12, 'cc'),(4, 13, 'dd');
+
+SELECT * FROM update_test1;
+
+UPDATE update_test1 SET tb1.a = 4 WHERE a = 1;
+UPDATE update_test1 tb1 SET tb1.f = 4 WHERE tb1.a = 1;
+UPDATE update_test1 tb SET tb1.a = 4 WHERE tb.a = 1;
+UPDATE update_test1 tb SET tb1.f = 4 WHERE tb.a = 1;
+
+UPDATE update_test1 tb1 SET tb1.a = 5 WHERE tb1.a = 1;
+UPDATE update_test1 tb1 SET update_test1.a = 6 WHERE tb1.a = 2;
+UPDATE update_test1 SET update_test1.a = 7 WHERE a = 3;
+UPDATE update_test1 tb1 SET a = 8 WHERE a = 4;
+SELECT * FROM update_test1;
+
+DROP TABLE update_test1;
"postgres_sure"<postgres_sure@163.com> writes:
I found "Do not include the table's name in the specification of a target column
— for example, UPDATE tab SET tab.col = 1 is invalid." in the documentation.
Some people usually like to update table by alias. They want to add this feature.
Sorry, but we aren't ever going to accept such a patch, because it would
introduce an unavoidable ambiguity: is "SET a.b = " meant to be an
assignment to column b of table a, or is it meant to be an assignment to
sub-field b of composite column a?
Yeah, we could invent some resolution rules to deal with that, but
better to just not add the nonstandard syntax (and it IS nonstandard,
SQL:2011 has nothing about it) in the first place.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Apr 7, 2016 at 4:39 AM, postgres_sure <postgres_sure@163.com> wrote:
Hi,
I found "Do not include the table's name in the specification of a target
column
— for example, UPDATE tab SET tab.col = 1 is invalid." in
the documentation.Some people usually like to update table by alias. They want to add this
feature.
Is this syntax described in the SQL standard?
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 7 April 2016 at 14:48, Merlin Moncure <mmoncure@gmail.com> wrote:
On Thu, Apr 7, 2016 at 4:39 AM, postgres_sure <postgres_sure@163.com> wrote:
Hi,
I found "Do not include the table's name in the specification of a target
column
— for example, UPDATE tab SET tab.col = 1 is invalid." in
the documentation.Some people usually like to update table by alias. They want to add this
feature.Is this syntax described in the SQL standard?
Given that the SQL standard has no concept of UPDATEs against a query
with multiple tables, and that's where this could bring the most
clarity, I'm not sure that it's reasonable to object on that basis.
As far as I can see the SQL standard doesn't let you alias tables in
an UPDATE (for the same reason, I imagine) but we allow _that_ even in
a single-table UPDATE.
<update statement: positioned> ::=
UPDATE <table name>
SET <set clause list>
WHERE CURRENT OF <cursor name>
Geoff
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 7 April 2016 at 15:51, I wrote:
<update statement: positioned> ::=
UPDATE <table name>
SET <set clause list>
WHERE CURRENT OF <cursor name>
I grabbed the wrong section of the doc; I should of course have pasted
the searched version:
<update statement: searched> ::=
UPDATE <table name>
SET <set clause list>
[ WHERE <search condition> ]
My point is still the same though :)
Geoff
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Geoff Winkless <pgsqladmin@geoff.dj> writes:
I grabbed the wrong section of the doc; I should of course have pasted
the searched version:
<update statement: searched> ::=
UPDATE <table name>
SET <set clause list>
[ WHERE <search condition> ]
My point is still the same though :)
Don't know which version of the SQL spec you're looking at, but SQL:2008
has
<update statement: searched> ::=
UPDATE <target table> [ [ AS ] <correlation name> ]
SET <set clause list>
[ WHERE <search condition> ]
Note the [ [ AS ] <correlation name> ] bit. However, they do NOT
allow the correlation name to appear in <set target>:
<set clause list> ::=
<set clause> [ { <comma> <set clause> }... ]
<set clause> ::=
<multiple column assignment>
| <set target> <equals operator> <update source>
<set target> ::=
<update target>
| <mutated set clause>
<multiple column assignment> ::=
<set target list> <equals operator> <assigned row>
<set target list> ::=
<left paren> <set target> [ { <comma> <set target> }... ] <right paren>
<assigned row> ::=
<contextually typed row value expression>
<update target> ::=
<object column>
| <object column>
<left bracket or trigraph> <simple value specification> <right bracket or trigraph>
<object column> ::=
<column name>
<mutated set clause> ::=
<mutated target> <period> <method name>
<mutated target> ::=
<object column>
| <mutated set clause>
<update source> ::=
<value expression>
| <contextually typed value specification>
<column name> is elsewhere defined as just <identifier>, if you were
hoping there was more there than meets the eye. The "mutated target"
business is some overly complex version of composite-type columns.
The reason why SQL doesn't allow an optional correlation name, and
probably never will, is the same as the reason why we don't, and probably
never will: it introduces an ambiguity as to whether you meant a dotted
set-clause target name to be a reference to a field of a composite column
or just a noise-word reference to the table's correlation name. If there
were any functional value in specifying the correlation name, it might be
worth dealing with the ambiguity; but there isn't.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 7 April 2016 at 16:45, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Don't know which version of the SQL spec you're looking at,
It was the draft 95 version, cos (being text file) it's easiest to
read :). I'll learn my lesson next time and expand the 2008 one.
but SQL:2008 has
<update statement: searched> ::=
UPDATE <target table> [ [ AS ] <correlation name> ]
SET <set clause list>
[ WHERE <search condition> ]
[snip]
The reason why SQL doesn't allow an optional correlation name, and
probably never will, is the same as the reason why we don't, and probably
never will: it introduces an ambiguity as to whether you meant a dotted
set-clause target name to be a reference to a field of a composite column
or just a noise-word reference to the table's correlation name.
I stand (comprehensively :) ) corrected, thanks for clarifying.
Geoff
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers