BREAK statement in PL/pgSQL

Question

How to have the break statement in PostgreSQL? I have the structure like this:

for()
 {
 for()
 {
  if(somecondition)
  break;
 }
}

As per my understanding it should only break the inner for loop?

Erwin Brandstetter · Accepted Answer · 2019-02-26T01:00:07.403

95

There is no ~~BREAK~~ in PL/pgSQL.

EXIT terminates the loop.
CONTINUE continues at the next iteration of the loop.
You can attach a <<label>> to loops and add it as parameter to each of these commands. Then you terminate / continue the labeled loop. Else, it concerns the inner loop.
RETURN exits from the function (so not applicable in a DO statement).

All of this applies to procedural elements of PL/pgSQL, not SQL.
Code example using all three:

Loop in function does not work as expected

edited Feb 26 '19 at 01:00

answered Mar 02 '13 at 17:30

Erwin Brandstetter

605,456
145
1,078
1,228

2

You are correct here , I don't know why other people have voted it down here. – user1844840 Mar 11 '13 at 05:58
1

`EXIT` is equivalent of what the OP meant by `BREAK`. There is no BREAK in PL/pgSQL. is a bit misleading otherwise. – Gajus Jul 30 '19 at 14:30
or you can use a RAISE EXCEPTION – Diego Feb 28 '21 at 20:33

score 0 · Answer 2 · answered Oct 07 '19 at 11:42

Note, that: Yes! you need the "WHEN", even if you end up (like me ;-)) with something like

LOOP
  ...
  IF l_my_var = 'some condition' THEN
    -- this is ok, bla
    IF l_debug_level >= 2 THEN
      RAISE NOTICE 'debug 2: skipping a duplicate %, l_my_var;
    END IF;
    -- do something
    CONTINUE WHEN TRUE; -- https://stackoverflow.com/questions/15173194/break-statement-in-pl-pgsql
  ELSE
    -- do something
  END IF;
  ...

which looks somewhat twisted with both IF and WHEN.

Remark to the editor: One could make the link behind "CONTINUE" more precise: https://www.postgresql.org/docs/9.6/plpgsql-control-structures.html#AEN66440

BREAK statement in PL/pgSQL

2 Answers2

Linked