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

2011/7/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> =?ISO-8859-1?Q?Petr_Jel=EDnek?= <pjmodos(at)pjmodos(dot)net> writes:
>> But, I think we should add valitation hook to plpgsql plugin structure
>> so that you don't have to actually execute the function to check it -
>> curretly there are only executing hooks which is why the plugin only
>> works when you the func (not good for automation).
>
> If you mean that such checks would be done automatically, no, they
> shouldn't be.  Consider a function that creates a table and then uses
> it, or even just depends on using a table that doesn't yet exist when
> you do CREATE FUNCTION.

yes, any deep check is not possible for function that uses a temporary tables.

A plpgsql_lint is not silver bullet - for these cases is necessary to
disable lint.

. I can't to speak generally - I have no idea, how much percent of
functions are functions with access to temporary tables - in my last
project I use 0 temp tables on cca 300 KB of plpgsql code.

The more terrible problem is a new dependency between functions. I use
a workaround - some like headers

CREATE FUNCTIONS foo(define interface here) RETURNS ... AS $$ BEGIN
RETURN; END; $$ LANGUAGE plpgsql;

....

...

--real implementation of foo
CREATE OR REPLACE FUNCTIONS foo(...)
RETURNS ...
AS ..

It works because I write a plpgsql script in hand - I don't use a dump
for plpgsql, but it is not solution for production servers. On second
hand - plpgsql_lint or some similar (and builtin or external) should
not be active on production servers. A planning only really processed
queries is necessary optimization if we have not a global plan cache.

Regards

Pavel

>
>                        regards, tom lane
>