Horton

Thread

Commits

  1. Fix EXIT out of outermost block in plpgsql.

  1. ERROR: control reached end of function without RETURN, except, it is not a function it is a procedure or a nameless block.

    Herwig Goemans <herwig.goemans@gmail.com> — 2021-09-13T11:23:06Z

    Hi Support,


I get an error in a stored procedure - not a stored function mind you - 
control reached end of function without RETURN

The error occurs when doing an exit <label> in  a stored procedure. Now 
I cannot give all of the stored procedures involved but I can simulate 
te problem with a very simple block:



DO
$BODY$
<<simple_block>>
BEGIN
begin
                exit simple_block;
          -- for demo purposes
               raise notice '%', 'unreachable!';
    end;
    raise notice '%', 'End of block';
end;
$BODY$

This will return the error on Postgres version 13, Version 12 and 9.


Now I thought this was text-book behaviour of exiting a code block. I do 
not like programming this way but it is part of a migration project of 
Oracle to PG.

What do I miss ?

Kind regards,


If you put this code snippet in a file and execute like:

postgres@herwig-NUC10i7FNH:~$ psql -f  do_test.sql
psql:do_test.sql:12: ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function inline_code_block

Herwig

  2. Re: ERROR: control reached end of function without RETURN, except, it is not a function it is a procedure or a nameless block.

    Pavel Stehule <pavel.stehule@gmail.com> — 2021-09-13T11:44:10Z

    Hi

po 13. 9. 2021 v 13:23 odesílatel Herwig Goemans <herwig.goemans@gmail.com>
napsal:

> Hi Support,
>
>
> I get an error in a stored procedure - not a stored function mind you -
> control reached end of function without RETURN
>
> The error occurs when doing an exit <label> in  a stored procedure. Now
> I cannot give all of the stored procedures involved but I can simulate
> te problem with a very simple block:
>
>
>
> DO
> $BODY$
> <<simple_block>>
> BEGIN
> begin
>                 exit simple_block;
>           -- for demo purposes
>                raise notice '%', 'unreachable!';
>     end;
>     raise notice '%', 'End of block';
> end;
> $BODY$
>
> This will return the error on Postgres version 13, Version 12 and 9.
>
>
> Now I thought this was text-book behaviour of exiting a code block. I do
> not like programming this way but it is part of a migration project of
> Oracle to PG.
>
> What do I miss ?
>

This is Postgres's bug

You can use an just workaround - extra outer block BEGIN END;

Regards

Pavel



> Kind regards,
>
>
> If you put this code snippet in a file and execute like:
>
> postgres@herwig-NUC10i7FNH:~$ psql -f  do_test.sql
> psql:do_test.sql:12: ERROR:  control reached end of function without RETURN
> CONTEXT:  PL/pgSQL function inline_code_block
>
> Herwig
>
>
>
>

  3. Re: ERROR: control reached end of function without RETURN, except, it is not a function it is a procedure or a nameless block.

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-09-13T16:04:48Z

    Pavel Stehule <pavel.stehule@gmail.com> writes:
> po 13. 9. 2021 v 13:23 odesílatel Herwig Goemans <herwig.goemans@gmail.com>
> napsal:
>> I get an error in a stored procedure - not a stored function mind you -
>> control reached end of function without RETURN
>> The error occurs when doing an exit <label> in  a stored procedure.

> This is Postgres's bug

Yeah, agreed.  Looks reasonably simple to fix, though.

			regards, tom lane