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 CREATE FUNCTION,
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 #variable_conflict use_variable
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.

Example 

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.
Thanks to 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! 🙂

Uncategorized

2012-06-27

3 Comments