On 2014-11-22 05:11, Peter Geoghegan wrote:
On Fri, Nov 21, 2014 at 7:49 PM, Marko Tiikkaja <ma...@joh.to> wrote:
A common mistake is to try and qualify column references on the LHS of SET
in UPDATE.
I think that this is a good idea, but as written the patch doesn't
handle aliases correctly:
postgres=# create table foo (val text);
CREATE TABLE
postgres=# update foo f set val = 'bar' where f.val != 'fd';
UPDATE 0
postgres=# update foo f set f.val = 'bar' where f.val != 'fd';
ERROR: 42703: column "f" of relation "foo" does not exist
LINE 1: update foo f set f.val = 'bar' where f.val != 'fd';
^
LOCATION: transformUpdateStmt, analyze.c:2015
Good point! Changed in v2, attached.
.marko
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 1989,2000 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
attrno = attnameAttNum(pstate->p_target_relation,
origTarget->name,
true);
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)),
parser_errposition(pstate,
origTarget->location)));
updateTargetListEntry(pstate, tle, origTarget->name,
attrno,
--- 1989,2020 ----
attrno = attnameAttNum(pstate->p_target_relation,
origTarget->name,
true);
if (attrno == InvalidAttrNumber)
+ {
+ const char *targetrefname;
+ const char *message_hint = NULL;
+
+ /*
+ * Table-qualifying the LHS expression in SET is a
common mistake;
+ * provide a hint if that seems to be the problem.
+ */
+ if (target_rte->alias)
+ targetrefname = target_rte->alias->aliasname;
+ else
+ targetrefname =
RelationGetRelationName(pstate->p_target_relation);
+
+ if (strcmp(origTarget->name, targetrefname) == 0 &&
+ origTarget->indirection != NIL &&
+ IsA(linitial(origTarget->indirection), String))
+ message_hint = "Target column references in
UPDATE must not be qualified";
+
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("column \"%s\" of relation
\"%s\" does not exist",
origTarget->name,
!
RelationGetRelationName(pstate->p_target_relation)),
! message_hint ? errhint("%s",
message_hint) : 0,
parser_errposition(pstate,
origTarget->location)));
+ }
updateTargetListEntry(pstate, tle, origTarget->name,
attrno,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers