Thread: Using "exit" to bring "goto" functionality.
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$;
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:
“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$;
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:
"Porting from Oracle PL/SQL"
doesn't mention "goto". But "exit <label>" might help.
> On Jul 10, 2023, at 11:54, Bryn Llewellyn <bryn@yugabyte.com> wrote:
>
> What is the rationale for supporting what seems to be on its face this strange functionality?
It allows you to EXIT or CONTINUE a loop that is not the innermost one, by naming the label of an outer loop.
One can debate endlessly whether it's good or bad to include that functionality, but it definitely has use cases. I
wouldassume that at this point, it's not going anywhere.
The relevant documentation is here:
https://www.postgresql.org/docs/15/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS
I am a newbe at my current job. They have a separate log table for every table because they want all the columns. I have been looking for a way to go to a common log table without requiring major changes to the application. With your help, I have a proof of principle that I can demonstrate now. > On 07/10/2023 2:58 PM EDT Christophe Pettus <xof@thebuild.com> wrote: > > > > On Jul 10, 2023, at 11:54, Bryn Llewellyn <bryn@yugabyte.com> wrote: > > > > What is the rationale for supporting what seems to be on its face this strange functionality? > > It allows you to EXIT or CONTINUE a loop thaIt is not the innermost one, by naming the label of an outer loop. > > One can debate endlessly whether it's good or bad to include that functionality, but it definitely has use cases. I wouldassume that at this point, it's not going anywhere. > > The relevant documentation is here: > > https://www.postgresql.org/docs/15/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS
On 7/10/23 12:08, DAVID ROTH wrote: > I am a newbe at my current job. > They have a separate log table for every table because they want all the columns. > I have been looking for a way to go to a common log table without requiring major changes to the application. > > With your help, I have a proof of principle that I can demonstrate now. FYI, you replied to the wrong thread. > > > >> On 07/10/2023 2:58 PM EDT Christophe Pettus <xof@thebuild.com> wrote: >> >> >>> On Jul 10, 2023, at 11:54, Bryn Llewellyn <bryn@yugabyte.com> wrote: >>> >>> What is the rationale for supporting what seems to be on its face this strange functionality? >> >> It allows you to EXIT or CONTINUE a loop thaIt is not the innermost one, by naming the label of an outer loop. >> >> One can debate endlessly whether it's good or bad to include that functionality, but it definitely has use cases. I wouldassume that at this point, it's not going anywhere. >> >> The relevant documentation is here: >> >> https://www.postgresql.org/docs/15/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS > > -- Adrian Klaver adrian.klaver@aklaver.com
xof@thebuild.com wrote:bryn@yugabyte.com wrote:
What is the rationale for supporting what seems to be on its face this strange functionality?
It allows you to EXIT or CONTINUE a loop that is not the innermost one, by naming the label of an outer loop. One can debate endlessly whether it's good or bad to include that functionality, but it definitely has use cases. I would assume that at this point, it's not going anywhere. The relevant documentation is here:
https://www.postgresql.org/docs/15/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS
Sorry, I should’ve said that I’d read this:
« When used with a BEGIN block, EXIT passes control to the next statement after the end of the block. Note that a label must be used for this purpose; an unlabeled EXIT is never considered to match a BEGIN block. (This is a change from pre-8.4 releases of PostgreSQL, which would allow an unlabeled EXIT to match a BEGIN block.) »
Yes, I see that the doc implies that the functionality that I asked about (premature exit from a block statement) will be supported indefinitely.
I can only assume that somebody implemented this back in the day and that nobody thought to say that it was a bad idea.
FYI, I tied both “exit” and “continue” outside of a loop in PL/SQL (using a fairly recent version of Oracle Database). There, each of these causes "must appear inside a loop" or "label 'B1' must label a LOOP statement".
Hi
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:
The reason why PL/pgSQL has not "goto" statement is mainly technological. PL/pgSQL is an interpreter of high level commands. For this kind of interpreter the "goto" - unstructured jump cannot be effectively implemented. PL/pgSQL is very simple, and relatively fast (expressions are slow due evaluation by SQL executor), but "goto" cannot be implemented there. Interpreter of PL/pgSQL is very different from the more usual p-code interpreter
Regards
Pavel
> pavel.stehule@gmail.com wrote: > >> bryn@yugabyte.com wrote: >> >> As it happens, Oracle's PL/SQL has a "goto" statement. But PL/pgSQL does not. (I assume that this is because "goto" isconsidered a bad thing.) But PL/SQL programmers do use it. However, the doc section: > > The reason why PL/pgSQL has not "goto" statement is mainly technological. PL/pgSQL is an interpreter of high level commands. For this kind of interpreter the "goto" - unstructured jump cannot be effectively implemented. PL/pgSQL is verysimple, and relatively fast (expressions are slow due evaluation by SQL executor), but "goto" cannot be implemented there.Interpreter of PL/pgSQL is very different from the more usual p-code interpreter. It’s interesting to know that the reason that PL/pgSQL doesn’t support “goto” is an implementation restriction rather thana purist stance. Thanks! I mentioned PL/SQL only to say that it does not support the premature exit from a block statement that PL/pgSQL _does_ support(and document). I accept, now, that I’ll never know the rationale for this.