PL/pgSQL

The procedural language for stored logic: declarations, control flow, cursors, dynamic SQL, and error handling.

Control Flow

• Assignment (:=)Assigns a value to a variable, record field, or array element.
• BEGIN...END BlockNamed or anonymous block with optional declarations and exception handlers.
• CASEMulti-way conditional (searched or simple form).
• EXIT / CONTINUEExits a loop or skips to the next iteration.
• FOR (integer range)Iterates over an integer range.
• FOR (query results)Iterates over rows returned by a query.
• FOREACHIterates over elements of an array.
• IF / ELSIF / ELSEConditional execution based on boolean expressions.
• LOOPUnconditional loop -- must use EXIT to terminate.
• WHILELoop with a condition tested before each iteration.

Cursors

• CLOSECloses an open cursor, releasing resources.
• FETCHRetrieves the next row from a cursor.
• MOVERepositions a cursor without fetching data.
• OPENOpens a cursor for fetching.

Declarations

• Cursor DeclarationDeclares a cursor for row-by-row result processing.
• Variable DeclarationDeclares a variable with type, optional NOT NULL, CONSTANT, DEFAULT, and COLLATION.

Dynamic SQL

• EXECUTEExecutes a dynamically-built SQL string.
• PERFORMExecutes a query and discards the result (side-effects only).

Error Handling

• ASSERT (PL/pgSQL)Asserts a condition is true, raising ASSERT_FAILURE if not.
• EXCEPTION HandlerCatches and handles errors within a block.
• GET DIAGNOSTICSRetrieves execution status or exception details.
• RAISERaises an error, warning, or notice with formatted message.

Output

• PRINTOutputs values to the result stream (DeltaForge extension).
• RETURN / RETURN NEXT / RETURN QUERYReturns a value, adds a row to output, or returns a query result.

Transactions

• COMMIT / ROLLBACKCommits or rolls back the current transaction within a procedure.