From: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | "Neil D'Souza" <neil(dot)xavier(dot)dsouza(at)gmail(dot)com> |
Subject: | Re: a query on stored procedures/functions in pgsql |
Date: | 2010-10-21 14:04:41 |
Message-ID: | 201010210704.41752.adrian.klaver@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)klaver(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Page | 2010-10-21 14:05:28 | Re: 9.0 SSL renegotiation failure restoring data |
Previous Message | Josh Kupershmidt | 2010-10-21 13:49:35 | Re: Old values in statement triggers? |