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 things like LINT checking. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
