Thread: PL/pgSQL RENAME functionality in TODOs
I was testing the following statement and found it working fine on version 8.2.1. "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? --Imad www.EnterpriseDB.com
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 On 1/31/07, Bruce Momjian <bruce@momjian.us> wrote: > > 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. + >
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 selectinto 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