Re: ToDo: plpgsql plugin for query and expression verification

2010/2/16 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> I don't would to execute function - it is useless because you need
>> good UI for execution all path. My idea is different. gram.y has
>> check_sql_expr rutine. This is used for parser checking every static
>> SQL fragment in plpgsql function. With some hook we can do full plan
>> generation instead.
>
> Previous proposals in this line have foundered on examples like
> functions that create a temp table and then manipulate it.
> Only DDL-free functions can be statically checked in the way
> you suggest.
>

No and yes.

yes - 100% test are possible only on a) DDL free functions, b) 100%
static schema.
no - in reality schema is usually stable and we are able to check sql
using stable schema.

This proposal isn't about ideal checking - it isn't possible. It is
about the maximum from what is possible.

I would to identify bugs in not often using execution path before
production. This case is real. Stored procedures works well and after
half of year we finding broken identifiers in some queries.

> Between that and the parameter-related limitations that Hitoshi
> points out, the use case seems to be rather restricted ...

why? why is it better? do you have a way for runtime checking of all
possible execution path?

regards
Pavel

>
>                        regards, tom lane
>