parameters in functions and overlap with names of columns

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

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

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