2012/6/20 Joel Jacobson <j...@trustly.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==brpsl1omwjiwzdi4gsivraeeu...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
