Thread: parameters in functions and overlap with names of columns
I've
create or replace function(...
declare
col1 varchar(32);
...
create table pippo(
col1 varchar(32),
...
Unfortunately I can't schema specify the column to avoid name
overlap.
Is there another way other than just simply rename the variable?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
On Tue, Aug 04, 2009 at 02:20:00PM +0200, Ivan Sergio Borgonovo wrote: > create or replace function(... > declare > col1 varchar(32); > Unfortunately I can't schema specify the column to avoid name > overlap. I think this is a limitation of plpgsql's parser; I tend to declare local variables with an underscore prefix such as "_col1" in your example. > Is there another way other than just simply rename the variable? I don't think so. -- Sam http://samason.me.uk/
2009/8/4 Ivan Sergio Borgonovo <mail@webthatworks.it>: > I've > > create or replace function(... > > declare > col1 varchar(32); > ... > > create table pippo( > col1 varchar(32), > ... > > Unfortunately I can't schema specify the column to avoid name > overlap. > > Is there another way other than just simply rename the variable? yes - the most common is an using of prefix '_' for local plpgsql variables. Other possibility is using qualified names. Pavel > > thanks > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Tue, 4 Aug 2009 16:01:58 +0200 Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2009/8/4 Ivan Sergio Borgonovo <mail@webthatworks.it>: > > I've > > > > create or replace function(... > > > > declare > > col1 varchar(32); > > ... > > > > create table pippo( > > col1 varchar(32), > > ... > > > > Unfortunately I can't schema specify the column to avoid name > > overlap. > > > > Is there another way other than just simply rename the variable? > > yes - the most common is an using of prefix '_' for local plpgsql > variables. Other possibility is using qualified names. Just to be sure... by qualified names you mean schema qualified name or table qualified names in case of columns... right... For a second I had the hope there was another way other than having a col1, a _col1 and a __col1 too ;) -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo wrote: > On Tue, 4 Aug 2009 16:01:58 +0200 > Pavel Stehule <pavel.stehule@gmail.com> wrote: > > > 2009/8/4 Ivan Sergio Borgonovo <mail@webthatworks.it>: > > > Is there another way other than just simply rename the variable? > > > > yes - the most common is an using of prefix '_' for local plpgsql > > variables. Other possibility is using qualified names. > > Just to be sure... by qualified names you mean schema qualified name > or table qualified names in case of columns... right... In this case I think it also means you can qualify the variable names with the function name; and/or declare named blocks inside the function, and qualify the variables with the block name. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
2009/8/4 Alvaro Herrera <alvherre@commandprompt.com>: > Ivan Sergio Borgonovo wrote: >> On Tue, 4 Aug 2009 16:01:58 +0200 >> Pavel Stehule <pavel.stehule@gmail.com> wrote: >> >> > 2009/8/4 Ivan Sergio Borgonovo <mail@webthatworks.it>: > >> > > Is there another way other than just simply rename the variable? >> > >> > yes - the most common is an using of prefix '_' for local plpgsql >> > variables. Other possibility is using qualified names. >> >> Just to be sure... by qualified names you mean schema qualified name >> or table qualified names in case of columns... right... > > In this case I think it also means you can qualify the variable names > with the function name; and/or declare named blocks inside the function, > and qualify the variables with the block name. yes Pavel > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> yes - the most common is an using of prefix '_' for local plpgsql
>> variables. Other possibility is using qualified names.
> Just to be sure... by qualified names you mean schema qualified name
> or table qualified names in case of columns... right...
Right. The plpgsql parser is (just barely) smart enough to not try
to substitute a local variable name for the second part of a qualified
name "a.b". So you could write "tab.col1" in your queries.
regards, tom lane
2009/8/4 Ivan Sergio Borgonovo <mail@webthatworks.it>: > On Tue, 4 Aug 2009 16:01:58 +0200 > Pavel Stehule <pavel.stehule@gmail.com> wrote: > >> 2009/8/4 Ivan Sergio Borgonovo <mail@webthatworks.it>: >> > I've >> > >> > create or replace function(... >> > >> > declare >> > col1 varchar(32); >> > ... >> > >> > create table pippo( >> > col1 varchar(32), >> > ... >> > >> > Unfortunately I can't schema specify the column to avoid name >> > overlap. >> > >> > Is there another way other than just simply rename the variable? >> >> yes - the most common is an using of prefix '_' for local plpgsql >> variables. Other possibility is using qualified names. > > Just to be sure... by qualified names you mean schema qualified name > or table qualified names in case of columns... right... > > For a second I had the hope there was another way other than having a > col1, a _col1 and a __col1 too ;) > Maybe prefix _ isn't nice, but it is 100% safe. I use both - prefix for variables and aliases for columns. The collision of column and variable names is over lot of very strange bugs, and any way that can protect us is perfect. Pavel > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >