SUSPEND STATEMENT

Pupose

Use SUSPEND to return a row of data from a procedure to its caller.

Syntax

<suspend_stmt> ::=
   SUSPEND ;

Semantics

Suspends execution of a PSQL routine until the next value is requested by the calling application, and returns output values, if any, to the calling application.

If the procedure is called from a select statement, processing will continue at the statement following SUSPEND when the next row of data is needed. Use the EXIT statement – or let the code path end at the final END of the body – to signal that there are no more rows to return.

If the procedure is called from a EXECUTE PROCEDURE statement, then SUSPEND has the same effect as EXIT. This usage is legal, but not recommended.

Examples

The below creates a procedure that returns the a series of integers and stops as soon as a value larger than 1000 has been reached.

CREATE PROCEDURE range RETURNING ( a INTEGER ) AS
DECLARE i INTEGER = 1;
DECLARE j INTEGER = 1;
BEGIN
   a = i + j;
   i = j; j = a;
   IF (a>1000) THEN EXIT;
   SUSPEND;
END;

See also

EXIT statement; PSQL Programs; See the SQL Reference Manual for a description of using procedures in SELECT statements.