Question about function body checking and 8.1

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:

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/