Looping row-by-row through a selected result set

Available in

PSQL

Syntax

A FOR SELECT statement

• retrieves each row sequentially from the result set and executes the statement or block of statements on the row. In each iteration of the loop, the field values of the current row are copied into pre-declared variables.

  Including the AS CURSOR clause enables positioned deletes and updates to be performed — see notes below

• can carry named parameters that must be previously declared in the DECLARE VARIABLE statement or exist as input or output parameters of the procedure

• requires an clause that is located at the end of the SELECT …​ FROM …​ specification. In each iteration of the loop, the field values in the current row are copied to the list of variables specified in the INTO clause. The loop repeats until all rows are retrieved, after which it terminates

• can be terminated before all rows are retrieved by using a LEAVE statement

Other points to take into account regarding undeclared cursors:

1. the cursor name argument associated with an clause must not clash with any names created by DECLARE VARIABLE or DECLARE CURSOR statements at the top of the body code, nor with any other cursors surfaced by an AS CURSOR clause

2. The optional FOR UPDATE clause in the SELECT statement is not required for a positioned update

Examples using FOR SELECT

1. A simple loop through query results:

2. Using the AS CURSOR clause to surface a cursor for the positioned delete of a record:

See also

DECLARE CURSOR, , SELECT, , DELETE