Thread

proposal: new contrib module plpgsql's embeded sql validator

Pavel Stehule <pavel.stehule@gmail.com> — 2011-07-08T04:31:31Z

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