BREAK statement in PL/pgSQL

postgresql loops plpgsql break
user1844840 picture user1844840 · Mar 2, 2013 · Viewed 32.2k times · Source

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?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Mar 2, 2013

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:

Related questions

Loop on tables with PL/pgSQL in Postgres 9.0+

I want to loop through all my tables to count rows in each of them. The following query gets me an error: DO $$ DECLARE tables CURSOR FOR SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%' ORDER BY …

Read More
Iterating over integer[] in PL/pgSQL

I am trying to loop through an integer array (integer[]) in a plpgsql function. Something like this: declare a integer[] = array[1,2,3]; i bigint; begin for i in a loop raise notice "% ",i; end loop; return true; end In my actual …

Read More
Cursor based records in PostgreSQL

I'm trying to use cursors for a query that joins multiple tables. I've seen that for oracle there is a cursor based record. When I try the same for Postgres, it throws some error. How can I do the same …

Read More