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
