Thread: Question about function body checking and 8.1
Hi, currently when you execute a CREATE OR REPLACE FUNCTION there is virtually no checking of the function body for variabletype compatibility or if the SQL statements are valid. It's not a big deal for long time users of Postgres, but for users migrating from systems such as MS SQL or Oracle it's reallya big deal for them. I work for a fairly large corp with close to 10,000 employees and I have introduced Postgres for internal development andthe biggest complaint I get from MS SQL server devs is the whole function body checking thing. The second biggest issue is about functions returning sets, apparently in MS SQL server you don't have to define a type ora cursor to return a result set. Anyway just thought I would pass this on. Thanks, Tony
tony_caduto@amsoftwaredesign.com wrote: > currently when you execute a CREATE OR REPLACE FUNCTION there is > virtually no checking of the function body for variable type > compatibility or if the SQL statements are valid. Only for very small values of "no". Please provide an example. -- Peter Eisentraut http://developer.postgresql.org/~petere/
tony_caduto@amsoftwaredesign.com wrote: > Hi, currently when you execute a CREATE OR REPLACE FUNCTION there is > virtually no checking of the function body for variable type > compatibility or if the SQL statements are valid. It's not a big deal > for long time users of Postgres, but for users migrating from systems > such as MS SQL or Oracle it's really a big deal for them. Hmm - this really is a separate issue for each language. I take it you're referring to plpgsql? > I work for a fairly large corp with close to 10,000 employees and I > have introduced Postgres for internal development and the biggest > complaint I get from MS SQL server devs is the whole function body > checking thing. > > The second biggest issue is about functions returning sets, > apparently in MS SQL server you don't have to define a type or a > cursor to return a result set. Well, technically you don't have to with plpgsql, but then you do have to: SELECT * FROM myfunc() AS t1(a int, b text, ...) -- Richard Huxton Archonet Ltd
On Tue, Mar 22, 2005 at 06:09:28PM +0100, Peter Eisentraut wrote:
> tony_caduto@amsoftwaredesign.com wrote:
> > currently when you execute a CREATE OR REPLACE FUNCTION there is
> > virtually no checking of the function body for variable type
> > compatibility or if the SQL statements are valid.
>
> Only for very small values of "no". Please provide an example.
Here's a simple one:
alvherre=# select version();
version
--------------------------------------------------------------------------------------------
PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-12)
(1 fila)
alvherre=# create function test_syntax() returns void language plpgsql as 'begin zelect 1; return; end';
CREATE FUNCTION
alvherre=# select test_syntax();
ERROR: error de sintaxis en o cerca de «zelect» en el carácter 1
QUERY: zelect 1
CONTEXT: PL/pgSQL function "test_syntax" line 1 at SQL statement
LINEA 1: zelect 1
^
alvherre=#
One would think that this is a pretty obvious syntax error ...
--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
One man's impedance mismatch is another man's layer of abstraction.
(Lincoln Yeoh)
Alvaro Herrera wrote: > On Tue, Mar 22, 2005 at 06:09:28PM +0100, Peter Eisentraut wrote: > > tony_caduto@amsoftwaredesign.com wrote: > > > currently when you execute a CREATE OR REPLACE FUNCTION there is > > > virtually no checking of the function body for variable type > > > compatibility or if the SQL statements are valid. > > > > Only for very small values of "no". Please provide an example. > > Here's a simple one: Well, mentioning that he was talking about PL/pgSQL would have helped. :) Implementing this type of syntax checker isn't hard since the code is already there, but there might be a small, ugly problem. IIRC, the parser and/or semantic analyzer of PL/pgSQL relies on knowing whether the function is called as a trigger. You don't know that at creation phase. So you'd need to relax the syntax checking in some ill-defined ways and propagate that relaxation flag all over the place. But besides that, this is really just a typing exercise for someone. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> alvherre=# create function test_syntax() returns void language plpgsql as 'begin zelect 1; return; end';
> CREATE FUNCTION
> alvherre=# select test_syntax();
> ERROR: error de sintaxis en o cerca de �zelect� en el car�cter 1
> QUERY: zelect 1
> CONTEXT: PL/pgSQL function "test_syntax" line 1 at SQL statement
> LINEA 1: zelect 1
> ^
> alvherre=#
Of course, Neil fixed that one already. It's still true that plpgsql
doesn't do any *semantic* analysis to speak of at function definition
time. I gather that Tony's users are looking for more than bare syntax
checking.
I'm not sure how much we could really do though; the obvious idea of
trying to test-plan each query in the function will fail on cases like
begin
create temp table foo ...;
insert into foo ...;
(And before you object that that doesn't work anyway, it probably will
once Neil gets done with cached-plan invalidation.)
regards, tom lane
Peter Eisentraut <peter_e@gmx.net> writes:
> Implementing this type of syntax checker isn't hard since the code is
> already there, but there might be a small, ugly problem. IIRC, the
> parser and/or semantic analyzer of PL/pgSQL relies on knowing whether
> the function is called as a trigger. You don't know that at creation
> phase.
Looking for RETURNS TRIGGER seems like an adequate cue for that.
regards, tom lane
Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Implementing this type of syntax checker isn't hard since the code > > is already there, but there might be a small, ugly problem. IIRC, > > the parser and/or semantic analyzer of PL/pgSQL relies on knowing > > whether the function is called as a trigger. You don't know that > > at creation phase. > > Looking for RETURNS TRIGGER seems like an adequate cue for that. Indeed... So that used to be a problem, but it seems all obstacles are now removed. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Ok,
here is a example
CREATE OR REPLACE FUNCTION mytest();
RETURNS VOID as
$$
DECLARE
mytestvar varchar;
mytestvar2 integer;
BEGIN
mytestvarr = 'bla';
select testfield from nonexistanttable where testfield = 2
INTO mytestvar2;
--The table does not exits, yet postgresql does not complain.
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
I also seem to remember that one of the 8.0 betas actually did better
checking, but then it was gone in the next beta. I could be wrong on
that though.
Also if you happen to use PLperl or any of the other ones, do they
actually do better checking than PLpgsql? Last time I used a PLperl
function it didn't do any checking at creation either.
Peter Eisentraut wrote:
>>compatibility or if the SQL statements are valid.
>>
>>
>
>Only for very small values of "no". Please provide an example.
>
>
>
On Mar 22, 2005, at 10:04 PM, Tony Caduto wrote: > Ok, > here is a example > > CREATE OR REPLACE FUNCTION mytest(); > RETURNS VOID as > $$ > DECLARE > mytestvar varchar; > mytestvar2 integer; > BEGIN > mytestvarr = 'bla'; > select testfield from nonexistanttable where testfield = 2 > INTO mytestvar2; > --The table does not exits, yet postgresql does not complain. > END; > $$ > LANGUAGE 'plpgsql' VOLATILE; > > I also seem to remember that one of the 8.0 betas actually did better > checking, but then it was gone in the next beta. I could be wrong on > that though. > > Also if you happen to use PLperl or any of the other ones, do they > actually do better checking than PLpgsql? Last time I used a PLperl > function it didn't do any checking at creation either. > I think (from experience rather than knowledge) that that is still the case as of 8.0.1. I know Tom Lane and I had a brief discussion on one of the lists on the subject a month or two ago, but I can't seem to find the emails. Sean
Sean Davis <sdavis2@mail.nih.gov> writes:
> On Mar 22, 2005, at 10:04 PM, Tony Caduto wrote:
>> Also if you happen to use PLperl or any of the other ones, do they
>> actually do better checking than PLpgsql? Last time I used a PLperl
>> function it didn't do any checking at creation either.
> I think (from experience rather than knowledge) that that is still the
> case as of 8.0.1. I know Tom Lane and I had a brief discussion on one
> of the lists on the subject a month or two ago, but I can't seem to
> find the emails.
CVS-tip createlang still thinks that plpgsql is the only standard PL
that has a validator procedure; therefore the others don't do any
checking at CREATE FUNCTION whatsoever.
It would be reasonable for someone to step up and improve this ...
regards, tom lane
On Tue, Mar 22, 2005 at 09:04:42PM -0600, Tony Caduto wrote: > > CREATE OR REPLACE FUNCTION mytest(); > RETURNS VOID as > $$ > DECLARE > mytestvar varchar; > mytestvar2 integer; > BEGIN > mytestvarr = 'bla'; > select testfield from nonexistanttable where testfield = 2 > INTO mytestvar2; > --The table does not exits, yet postgresql does not complain. > END; > $$ > LANGUAGE 'plpgsql' VOLATILE; This is at most a warning. Just because the table doesn't exist now doesn't mean it won't exixt when the function is run. Need to be careful here otherwise when restoring a dump you'll end up with lots of useless errors because the tables were created after the functions... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.