Re: a query on stored procedures/functions in pgsql - Mailing list pgsql-general

From Adrian Klaver
Subject Re: a query on stored procedures/functions in pgsql
Date
Msg-id 201010210704.41752.adrian.klaver@gmail.com
Whole thread Raw
In response to Re: a query on stored procedures/functions in pgsql  ("Neil D'Souza" <neil.xavier.dsouza@gmail.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Josh Kupershmidt
Date:
Subject: Re: Old values in statement triggers?
Next
From: Dave Page
Date:
Subject: Re: 9.0 SSL renegotiation failure restoring data