PL/pgSQL RENAME functionality in TODOs

imad <immaad@gmail.com> writes:

"Fix RENAME to work on variables other than OLD/NEW"
I can rename just any variable declared in a PL block apart from
OLD/NEW. Is the TODOs list out of sync or I am missing the point here?

Really? It looks pretty broken to me still:

regression=# create function foo() returns int as $$
regression$# declare
regression$# x int := 1;
regression$# rename x to y;
regression$# begin
regression$# return y;
regression$# end;$$ language plpgsql;
ERROR: syntax error at or near "x"
LINE 4: rename x to y;
^

See old discussion here:
http://archives.postgresql.org/pgsql-hackers/2002-03/msg00591.php

regards, tom lane

URL added to TODO. (I didn't have URLs in there at the time).

---------------------------------------------------------------------------

Tom Lane wrote:

imad <immaad@gmail.com> writes:

"Fix RENAME to work on variables other than OLD/NEW"
I can rename just any variable declared in a PL block apart from
OLD/NEW. Is the TODOs list out of sync or I am missing the point here?

Really? It looks pretty broken to me still:

regression=# create function foo() returns int as $$
regression$# declare
regression$# x int := 1;
regression$# rename x to y;
regression$# begin
regression$# return y;
regression$# end;$$ language plpgsql;
ERROR: syntax error at or near "x"
LINE 4: rename x to y;
^

See old discussion here:
http://archives.postgresql.org/pgsql-hackers/2002-03/msg00591.php

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

OK, so renaming does not work in the same block.
You can rename a vairable in a nested block and thats why it works for OLD/NEW.

BTW, what is the purpose behind it? Declaring a variable in a block
and quickly renaming it does not make sense to me.

--Imad
www.EnterpriseDB.com

imad <immaad@gmail.com> writes:

OK, so renaming does not work in the same block.
You can rename a vairable in a nested block and thats why it works for OLD/NEW.

BTW, what is the purpose behind it? Declaring a variable in a block
and quickly renaming it does not make sense to me.

I agree it's pretty useless; but if we're gonna forbid it then we should
throw a more sensible error than "syntax error".

Actually, it seems to me that it works in the nested-block case only for
rather small values of "work":

regression=# create function foo() returns int as $$
regression$# declare
regression$# x int := 1;
regression$# begin
regression$# x := 2;
regression$# declare
regression$# rename x to y;
regression$# begin
regression$# y := 3;
regression$# end;
regression$# return x;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select foo();
ERROR: column "x" does not exist
LINE 1: SELECT x
^
QUERY: SELECT x
CONTEXT: PL/pgSQL function "foo" line 10 at return
regression=#

Surely the variable's name should be x again after we're out of the
nested block?

regards, tom lane

On 2/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

imad <immaad@gmail.com> writes:

OK, so renaming does not work in the same block.
You can rename a vairable in a nested block and thats why it works for OLD/NEW.

BTW, what is the purpose behind it? Declaring a variable in a block
and quickly renaming it does not make sense to me.

I agree it's pretty useless; but if we're gonna forbid it then we should
throw a more sensible error than "syntax error".

Actually, it seems to me that it works in the nested-block case only for
rather small values of "work":

regression=# create function foo() returns int as $$
regression$# declare
regression$# x int := 1;
regression$# begin
regression$# x := 2;
regression$# declare
regression$# rename x to y;
regression$# begin
regression$# y := 3;
regression$# end;
regression$# return x;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select foo();
ERROR: column "x" does not exist
LINE 1: SELECT x
^
QUERY: SELECT x
CONTEXT: PL/pgSQL function "foo" line 10 at return
regression=#

Surely the variable's name should be x again after we're out of the
nested block?

Yes, seems to be the only possible reason of renaming a variable.

--Imad
www.EnterpriseDB.com

Hello,

std. use rename only for triggers and variables new and old. It has sense. I
don't see sense for rename in clasic plpgsql functions. There was one
reason, rename unnamed $params. But currently plpgsql support named params
and this reason is obsolete.

Regards
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

On Feb 1, 2007, at 5:08 AM, Pavel Stehule wrote:

std. use rename only for triggers and variables new and old. It has
sense. I don't see sense for rename in clasic plpgsql functions.
There was one reason, rename unnamed $params. But currently plpgsql
support named params and this reason is obsolete.

Unless things have changed it can be a real PITA to deal with plpgsql
variables that share the same name as a field in a table. IIRC
there's some cases where it's not even possible to unambiguously
refer to the plpgsql variable instead of the field.

For internal variables there's a decent work-around... just prefix
all variables with something like v_. But that's pretty ugly for
parameters... get_user(user_id int) is certainly a nicer interface
than get_user(p_user_id int).

But I think a way to get around that would be to RENAME the arguments
in the DECLARE section, so user_id could become p_user_id under the
covers.

So perhaps there is still a point to RENAME after-all, at least for
paramaters.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

But I think a way to get around that would be to RENAME the arguments in
the DECLARE section, so user_id could become p_user_id under the covers.

It's one case. But I don't belive so result will be more readable. Better
solution is using names qualificated by function name. I am not sure if
plpgsql support it. I thing so Oracle support it and SQL/PSM support it too.

like:
create or replace function fx(a integer, b integer)
returns void as $$
declare la integer, lb integer;
begin
select into la, lb tab.a, tab.b
from tab
where tab.a = fx.a and tab.b = fx.b

I am sorry. I don't belive so using RENAME is better

So perhaps there is still a point to RENAME after-all, at least for
paramaters.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Pavel Stehule

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/

On 2/2/07, Jim Nasby <decibel@decibel.org> wrote:

On Feb 1, 2007, at 5:08 AM, Pavel Stehule wrote:

std. use rename only for triggers and variables new and old. It has
sense. I don't see sense for rename in clasic plpgsql functions.
There was one reason, rename unnamed $params. But currently plpgsql
support named params and this reason is obsolete.

Unless things have changed it can be a real PITA to deal with plpgsql
variables that share the same name as a field in a table. IIRC
there's some cases where it's not even possible to unambiguously
refer to the plpgsql variable instead of the field.

For internal variables there's a decent work-around... just prefix
all variables with something like v_. But that's pretty ugly for
parameters... get_user(user_id int) is certainly a nicer interface
than get_user(p_user_id int).

But I think a way to get around that would be to RENAME the arguments
in the DECLARE section, so user_id could become p_user_id under the
covers.

So perhaps there is still a point to RENAME after-all, at least for
paramaters.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Parameters can be renamed in 8.2.
The only thing which does not work is renaming a variable immediately after
its declaration which is a useless functionality.

So, should we still consider it a ToDo?

-- Imad
www.EnterpriseDB.com

imad <immaad@gmail.com> writes:

So, should we still consider it a ToDo?

Whatever you think about the rename-in-same-block-as-declared case,
it's still broken, as per my example showing that the effects are not
limited to the containing block. However, considering that no one
has taken an interest in fixing it since 7.2, it's obviously not a
high-priority feature.

regards, tom lane