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

From: Jim Nasby <jim(at)nasby(dot)net>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proposal: new contrib module plpgsql's embeded sql validator
Date: 2011-07-17 20:31:45
Message-ID: 0AA9297B-E97A-4AA0-BE1E-713ABC9D65EC@nasby.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2011-07-17 20:42:39 Re: Re: patch review : Add ability to constrain backend temporary file space
Previous Message Jim Nasby 2011-07-17 20:26:25 Re: storing TZ along timestamps