Hello all, a lazy deep SQL validation inside plpgsq functions is interesting attribute. It allows to work with temporary tables and it make testing and debugging harder, because lot of errors in embedded queries are detected too late. I wrote a simple module that can to help little bit. It is based on plpgsql plugin API and it ensures a deep validation of embedded sql early - after start of execution. I am thinking, so this plugin is really useful and it is example of plpgsql pluging - that is missing in contrib.
Example:
buggy function - raise error when par > 10
CREATE OR REPLACE FUNCTION public.kuku(a integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
begin
if (a > 10) then
return b + 1;
else
return a + 1;
end if;
end;
$function$
but it is works for par <= 10
postgres=# select kuku(1);
kuku
------
2
(1 row)
postgres=# load 'plpgsql';
LOAD
postgres=# load 'plpgsql_esql_checker';
LOAD
postgres=# select kuku(1);
ERROR: column "b" does not exist
LINE 1: SELECT b + 1
^
QUERY: SELECT b + 1
CONTEXT: PL/pgSQL function "kuku" line 3 at RETURN
with esql checker this bug is identified without dependency on used
parameter's value
What do you think about this idea?
The code contains a plpgsql_statement_tree walker - it should be moved
to core and used generally - statistic, coverage tests, ...
Regards
Pavel Stehule
plpgsql-checker-9.0.tgz
Description: GNU Zip compressed data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
