One of the biggest incompatibility issues between 9.x and 8.4 is the new behavior of
PL/pgSQL functions where parameter names are in conflict with columns.
In 8.4, PL/pgSQL silently use the value of the parameters.
In 9.x, PL/pgSQL throws an error, but not when doing
only when actually executing the function and hitting a row in conflict.
This means it is currently impossible to get a list of any functions with this problem.
You could disable the new behavior by adding
to all existing functions or to the global configuration, but this is sub-optimal,
it would be much better if the new improved behavior could be used for all functions.
I asked around on IRC in #postgresql on irc.freenode.net and got lucky!
A new feature will hopefully be added to 9.3, the function is called plpgsql_check_function developed by Pavel Stěhule.
plpgsql_check_function will detect any problems you might get during execution,
not only the errors you get when creating functions.
ubuntu@pgsql93:~$ psql psql (9.3devel) Type "help" for help. ubuntu=# CREATE OR REPLACE FUNCTION myfunc(id integer) RETURNS TEXT AS $$ ubuntu$# DECLARE ubuntu$# value text; ubuntu$# BEGIN ubuntu$# SELECT value INTO value FROM mytable WHERE id = id; ubuntu$# RETURN value; ubuntu$# END; ubuntu$# $$ LANGUAGE plpgsql; CREATE FUNCTION ubuntu=# SELECT plpgsql_check_function(oid) FROM pg_proc WHERE proname = 'myfunc'; plpgsql_check_function ------------------------------------------------------------------------- error:42702:5:SQL statement:column reference "value" is ambiguous Query: SELECT value FROM mytable WHERE id = id -- ^ Detail: It could refer to either a PL/pgSQL variable or a table column. (4 rows)
I’m just about to upgrade our production database from 8.4 to 9.1.
Out of the 1474 functions (1.8 MB of code), 132 functions had some kind of problem.
plpgsql_check_function I didn’t have to go through all the PL/pgSQL functions manually.
Thank you Pavel! You saved me a lot of work today! 🙂
This looks to be really handy. Why can’t something like this be added when you try to create a function for the first time? That is better creation time checking.
plpgsql is dynamic language with possibility creating function without respecting dependencies. Checking functions on start means new lot of dependencies.
A motivating discussion is worth comment. I believe that you need to write more about this
topic, it may not be a taboo subject but usually people don’t discuss such issues.
To the next! Cheers!!