Re: proposal: new contrib module plpgsql's embeded sql validator

On Jul 7, 2011, at 11:31 PM, Pavel Stehule wrote:
> 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, ...

I think this should at least be a contrib module; it seems very useful.

On a somewhat related note, I'd also really like to have the ability to parse things like .sql files externally, to do
thingslike LINT checking. 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net