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@gmail.com