Thread: Trouble EXITing plpgsql labeled BEGIN blocks with DECLAREs

FYI, mostly.  But I do have questions as to how to write code
that will continue to work in subsequent postgresql versions.

See code below.

begintest() uses EXIT to exit a BEGIN block from within nested
loops.  No problem.

begintest2() simplifies this, omitting the nested loops.
Still no problem.

begintest3() changes the return value of begintest2(), instead
of returning INT returns VOID.  No problems.

begintest4() moves the declariation from the outermost BEGIN block
to the inner BEGIN block, the one that's EXITed.  It fails with:

WARNING:  plpgsql: ERROR during compile of begintest4 near line 9
ERROR:  syntax error at or near "some_label"

I've written some code like begintest4(), if I change it to
begintest3() will it work forever?  (Actually my code is a trigger
function and returns TRIGGER instead of VOID.)

It seems that having a DECLARE on a labeled BEGIN block is
the problem. ?  Hence begintest5() which fails, and begintest6()
  and 7 which works.  The difference between 5 and 6 is
whether or not the inner BLOCK, the EXITed one,
DECLAREs a variable.  The difference between 5 and 7 is
that 7 has another layer of BEGIN between <<label>>
and DECLARE, so again, whether the EXITed block has
a DECLARE.

I want to write the code with EXIT, intead of using RETURN,
so that the reader does not have to look through the code
to find RETURNs sprinkeled within.  If he ever wants to add
code to be run just before the function exits he can just add
such code before the RETURN at the bottom of the function, without
having to refactor the routine's control structure.  Can I do
this just be adding another layer of BEGIN block between
DECLARE and <<label>> (which fixed my code) or do I have to
give up and use RETURNS?

(This has the feel of an optimizer problem.)


=> select version();
  PostgreSQL 7.3.4 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)


CREATE FUNCTION begintest()
   RETURNS INT
   LANGUAGE plpgsql
   AS '
   DECLARE
     var INT;

   BEGIN

     <<somelabel>>
     BEGIN

       FOR i IN 1 .. 10 LOOP
         var := i;
         FOR j in 1 .. 10 LOOP
           IF i >= 5 THEN
             EXIT somelabel;
           END IF;
         END LOOP;
       END LOOP;
     END;

     RETURN var;
   END;
';

SELECT begintest();

DROP FUNCTION begintest();



CREATE FUNCTION begintest2()
   RETURNS INT
   LANGUAGE plpgsql
   AS '
   DECLARE
     var INT;
   BEGIN

     <<some_label>>
     BEGIN

       var := 5;
       EXIT some_label;

       var := 0;
     END;

     RETURN var;
   END;
';

SELECT begintest2();

DROP FUNCTION begintest2();



CREATE FUNCTION begintest3()
   RETURNS VOID
   LANGUAGE plpgsql
   AS '
   DECLARE
     var INT;
   BEGIN

     <<somelabel>>
     BEGIN

       var := 5;
       EXIT somelabel;

       var := 0;
     END;

     RETURN NULL;
   END;
';

SELECT begintest3();

DROP FUNCTION begintest3();


CREATE FUNCTION begintest4()
   RETURNS VOID
   LANGUAGE plpgsql
   AS '
   BEGIN
     DECLARE
       var INT;

     <<some_label>>
     BEGIN

       var := 5;
       EXIT some_label;

       var := 0;
     END;

     RETURN NULL;
   END;
';

SELECT begintest4();

DROP FUNCTION begintest4();


CREATE FUNCTION begintest5()
   RETURNS INT
   LANGUAGE plpgsql
   AS '
   DECLARE
    othervar INT;

   BEGIN
     DECLARE
       var INT;

     <<some_label>>
     BEGIN

       var := 5;
       EXIT some_label;

       var := 0;
     END;

     othervar := 2;

     RETURN othervar;
   END;
';

SELECT begintest5();

DROP FUNCTION begintest5();


CREATE FUNCTION begintest6()
   RETURNS INT
   LANGUAGE plpgsql
   AS '
   DECLARE
    othervar INT;
    var INT;

   BEGIN

     <<some_label>>
     BEGIN

       var := 5;
       EXIT some_label;

       var := 0;
     END;

     othervar := 2;

     RETURN othervar;
   END;
';

SELECT begintest6();

DROP FUNCTION begintest6();



CREATE FUNCTION begintest7()
   RETURNS INT
   LANGUAGE plpgsql
   AS '
   DECLARE
    othervar INT;

   BEGIN
     DECLARE
       var INT;

     BEGIN
       <<some_label>>
       BEGIN

         var := 5;
         EXIT some_label;

         var := 0;
       END;
     END;

     othervar := 2;

     RETURN othervar;
   END;
';

SELECT begintest7();

DROP FUNCTION begintest7();




Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein

"Karl O. Pinc" <kop@meme.com> writes:
> begintest4() moves the declariation from the outermost BEGIN block
> to the inner BEGIN block, the one that's EXITed.  It fails with:

> WARNING:  plpgsql: ERROR during compile of begintest4 near line 9
> ERROR:  syntax error at or near "some_label"

You're misreading the syntax.  A <<label>> applies to a whole block
including any declarations.  So instead of

>    BEGIN
>      DECLARE
>        var INT;
>      <<some_label>>
>      BEGIN
>        var := 5;
>        EXIT some_label;
>        var := 0;
>      END;
>      RETURN NULL;
>    END;

write

>    BEGIN
>      <<some_label>>
>      DECLARE
>        var INT;
>      BEGIN
>        var := 5;
>        EXIT some_label;
>        var := 0;
>      END;
>      RETURN NULL;
>    END;

See the definition of "block" at the very top of
http://developer.postgresql.org/docs/postgres/plpgsql-structure.html
...

            regards, tom lane