On Wednesday 20 October 2010 9:48:39 pm Neil D'Souza wrote: > > You have same plpgsql identifiers as sql identifiers, and because > > plpgsql identifiers has higher priority, your query is broken. For > > simple functions like this don't use a plpgsql language - use sql > > language instead. > > Thank you for the quick reply. The example I constructed was > specifically for this post. I modified the function as below and it > works fine now. It would be great if the point you mentioned was a > note in the PGSQL Documentation (or did I miss it). In case I didnt > miss it, Is there anyone I have to write to, to help get this note in? > For the record it is in the docs twice: http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT "Caution PL/pgSQL will substitute for any identifier matching one of the function's declared variables; it is not bright enough to know whether that's what you meant! Thus, it is a bad idea to use a variable name that is the same as any table, column, or function name that you need to reference in commands within the function. For more discussion see Section 38.10.1. " http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html#PLPGSQL-VAR-SUBST "The substitution mechanism will replace any token that matches a known variable's name. This poses various traps for the unwary. For example, it is a bad idea to use a variable name that is the same as any table or column name that you need to reference in queries within the function, because what you think is a table or column name will still get replaced. In the above example, suppose that logtable has column names logtxt and logtime, and we try to write the INSERT as... " -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
