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? Thanks! Best regards, Joel Jacobson
