Обсуждение: 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;

>
> 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 from
>
> Tony 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) 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

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