Variables and control flow

A procedure separates local calculation from SQL execution. Declare local variables before BEGIN, update them with :=, and return a value with RETURN.

A bounded calculation

This anonymous procedure adds the integers from 1 through 10 and returns 55. It is included in the order-processing worksheet.

WITH sum_to AS PROCEDURE (n INT) RETURNS INT LANGUAGE SQL AS $$
DECLARE total INT DEFAULT 0;
BEGIN
 FOR i IN 1 TO n DO total := total + i; END FOR;
 RETURN total;
END;
$$ CALL sum_to(10);

Use loops for a bounded number of orchestration steps. For summing table rows, use SQL SUM instead. Validate externally supplied loop bounds in application procedures so a caller cannot accidentally request unbounded work.

Values inside SQL statements

The order-processing procedure counts pending orders into a local variable:

SELECT COUNT(*) INTO :changed
FROM tutorial.procedures.demo_orders
WHERE status = 'new' AND amount >= :min_amount;

Here, changed and min_amount are procedure variables. The colon marks a value binding in embedded SQL. The aggregate returns exactly one row, including when the count is zero. A general SELECT … INTO must return exactly one row; zero or multiple rows is an error.

In procedural expressions, refer to variables directly:

IF simulate_failure THEN
 RAISE forced_failure;
END IF;

Value bindings are not SQL identifiers. Use explicit, fully qualified table names in reusable procedures. If you need dynamic SQL, review EXECUTE IMMEDIATE in the SQL reference and validate any generated identifiers before constructing a statement.

Returning rows

A RESULTSET holds a query result that the procedure can return to Studio:

CREATE OR REPLACE PROCEDURE tutorial.procedures.ready_orders()
RETURNS TABLE(id INT, amount INT) LANGUAGE SQL EXECUTE AS CALLER AS $$
DECLARE rs RESULTSET;
BEGIN
 rs := (SELECT id, amount FROM tutorial.procedures.demo_orders
        WHERE status = 'ready' ORDER BY id);
 RETURN TABLE(rs);
END;
$$;

After the tutorial's committed update, CALL tutorial.procedures.ready_orders() returns (1, 120) and (4, 200). ORDER BY makes the displayed order deterministic. Keep returned results appropriately bounded for interactive clients.