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

Reply via email to