Using "exit" to bring "goto" functionality.

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Using "exit" to bring "goto" functionality.
Date: 2023-07-10 18:54:08
Message-ID: 655E5E21-FCED-405F-81F6-AE2713F27337@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I wanted to see what error(s) would be reported if "exit" or "continue" is used, in PL/pgSQL, outside of a loop—and in particular if these were reported as syntax errors or runtime errors. There were no surprises with "continue". But I was surprised by this:

create procedure p()
language plpgsql
as $body$
begin
exit;
end;
$body$;

It causes the 42601 syntax error: EXIT cannot be used outside a loop, unless it has a label

"unless it has a label"? Eh?

I Googled this for that message and found this:

https://postgrespro.com/list/thread-id/2364409 <https://postgrespro.com/list/thread-id/2364409>
“Thread: Better testing coverage and unified coding for plpgsql loops”, Tom Lane, 31 December 2017

The message is mentioned. But I couldn't find any opinion about the intention. I tried this (using Version 15.3):

create function f(n in int)
returns text
language plpgsql
as $body$
declare
v text not null := 'a';
begin
<<b1>>begin
v := v||'1';
exit b1 when length(v) > n;
v := v||'2';
exit b1 when length(v) > n;
v := v||'3';
end b1;
return v;
end;
$body$;

It completed without error. I tried "select f(1)" and then with the actuals "2" and "99"—and I got the outcomes that the code asks for. In other words, "exit" can, in at least some scenarios, be used to emulate "goto". (Rather like a premature "return" in the middle of a procedure.)

What is the rationale for supporting what seems to be on its face this strange functionality?

As it happens, Oracle's PL/SQL has a "goto" statement. But PL/pgSQL does not. (I assume that this is because "goto" is considered a bad thing.) But PL/SQL programmers do use it. However, the doc section:

https://www.postgresql.org/docs/current/plpgsql-porting.html <https://www.postgresql.org/docs/current/plpgsql-porting.html>
"Porting from Oracle PL/SQL"

doesn't mention "goto". But "exit <label>" might help.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christophe Pettus 2023-07-10 18:58:33 Re: Using "exit" to bring "goto" functionality.
Previous Message Christophe Pettus 2023-07-10 18:52:04 Re: Trigger Function question