From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Joel Jacobson <joel(at)trustly(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Finding all 8.4->9.x incompatible PL/pgSQL functions with conflicting parameter/column names causing "column reference ... is ambiguous" errors |
Date: | 2012-06-20 10:49:45 |
Message-ID: | CAFj8pRB6PGchGq2CO10kTGnuP9a3Db32-eg6FyAj0-4kccG9sg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2012/6/20 Joel Jacobson <joel(at)trustly(dot)com>:
> I'm upgrading from 8.4 to 9.1, and have a lot of PL/pgSQL functions which
> works in 8.4, but when called, throws an error in 9.1.
>
> Example:
>
> CREATE TABLE mytable (id serial not null primary key, value text);
>
> INSERT INTO mytable (id, value) VALUES (1, 'foo');
> INSERT INTO mytable (id, value) VALUES (2, 'bar');
>
> CREATE OR REPLACE FUNCTION myfunc(id int) RETURNS TEXT AS $$
> DECLARE
> value text;
> BEGIN
> SELECT mytable.value INTO value FROM mytable WHERE mytable.id = id;
> RETURN value;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT myfunc(1);
> SELECT myfunc(2);
>
> This returns "foo" and "bar" like expected in 8.4, but in 9.1 I get "column
> reference "id" is ambiguous", "It could refer to either a PL/pgSQL variable
> or a table column.".
>
> This is of course easy to fix by qualifying id with the name of the
> function:
>
> -SELECT mytable.value INTO value FROM mytable WHERE mytable.id = id;
> +SELECT mytable.value INTO value FROM mytable WHERE mytable.id = myfunc.id;
>
> The problem is, how can I find all functions which have this problem?
>
> You don't get this error when creating the functions, only when running them
> and hitting a statement where there is a conflict.
>
> Would it be possible to somehow automatically scan through all functions and
> getting a list of the functions which have this problem?
>
you can try to apply patch - plpgsql_check_function
http://archives.postgresql.org/message-id/CAFj8pRBRWXA98T9k=Cqw==brpsL1OMwJiWzDi4GsivRaEEUBmQ@mail.gmail.com
you need 9.2 and with this functionality you can find functions with
some issues.
Regards
Pavel Stehule
> Thanks!
>
> Best regards,
>
> Joel Jacobson
From | Date | Subject | |
---|---|---|---|
Next Message | pandorino | 2012-06-20 12:31:17 | Re: pgstat wait timeout : permission denied |
Previous Message | Joel Jacobson | 2012-06-20 10:44:09 | Finding all 8.4->9.x incompatible PL/pgSQL functions with conflicting parameter/column names causing "column reference ... is ambiguous" errors |