function body error checking issues
Bruce,
here is a example:
(there is a simple error in the function that does not get caught when running the query in 8.0)
CREATE or REPLACE FUNCTION annual.get_ratio( float8, float8)
RETURNS pg_catalog.float8 AS
$BODY$
DECLARE
execptioncount_in alias for $1;
questioncount_in alias for $2;
ratio_out float8;
BEGIN
IF (execptioncount_in >0) AND (questioncount_in >0) THEN
RATIO_OUT = execptioncount_in / questioncount_in;
ELSE
--this variable is not declared, yet when I run this query no error is raised
EXCEPTIONRATIO_OUT = 0;
END IF;
return ratio_out;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Show quoted text
From: Bruce Momjian <pgman@candle.pha.pa.us>
Date: 2005/02/21 Mon AM 10:47:35 EST
To: tony_caduto@amsoftwaredesign.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] is there anyway to get the backends IP address fromTony Caduto wrote:
Bruce,
On another note, is there plans to improve the type checking of stored
functions during the save/compile?
Currently I can pretty much make tons of mistakes (on purpose of course
:-) and they are not flagged as errors until runtime.
The biggest complaint I see from other DBAs (MS SQL, Oracle) is that
Postgres does little to no pre-runtime type checking.We have no plans to improve that. We do have 'check_function_bodies'
which defaults to true and does some checking. Would you give us a
particular example you would like improved?-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
tony_caduto@amsoftwaredesign.com wrote:
Bruce,
here is a example:
(there is a simple error in the function that does not get caught when running the query in 8.0)CREATE or REPLACE FUNCTION annual.get_ratio( float8, float8)
RETURNS pg_catalog.float8 AS
$BODY$
DECLARE
execptioncount_in alias for $1;
questioncount_in alias for $2;
ratio_out float8;BEGIN
IF (execptioncount_in >0) AND (questioncount_in >0) THENRATIO_OUT = execptioncount_in / questioncount_in;
ELSE
--this variable is not declared, yet when I run this query no error is raised
EXCEPTIONRATIO_OUT = 0;
END IF;
return ratio_out;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
Syntax error reporting has been improved in our code so 8.1 might be
better and catching such errors.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote:
Syntax error reporting has been improved in our code so 8.1 might be
better and catching such errors.
Yes, current sources catches this at definition-time:
% psql -f test.sql
psql:test.sql:21: ERROR: syntax error at or near "EXCEPTIONRATIO_OUT"
at character 1
QUERY: EXCEPTIONRATIO_OUT = 0
CONTEXT: SQL statement in PL/PgSQL function "get_ratio" near line 13
psql:test.sql:21: LINE 1: EXCEPTIONRATIO_OUT = 0
psql:test.sql:21: ^
If folks have more suggestions for improving pl/pgsql compile-time error
checking, speak up. I'm also planning to implement trivially-dead-code
detection (like statements that follow a RETURN, and so on), although
that's not in HEAD yet.
-Neil