Retrieves execution status or exception details.
GET [STACKED] DIAGNOSTICS <var> = <item> [, ...];
Items: ROW_COUNT, RESULT_OID, PG_CONTEXT,
RETURNED_SQLSTATE, MESSAGE_TEXT, EXCEPTION_DETAIL, EXCEPTION_HINT, etc.## Overview GET DIAGNOSTICS retrieves metadata about the most recent SQL statement execution or the current exception context. The non-stacked form accesses statement-level diagnostics; the STACKED form accesses exception details within an EXCEPTION handler. ## Behavior - Non-stacked (GET DIAGNOSTICS): retrieves information from the Diagnostics struct maintained by the executor. Available items are: - ROW_COUNT: the number of rows affected by the last DML statement (INT64). - RESULT_OID: the OID of the last inserted row (INT64, often NULL in DeltaForge). - PG_CONTEXT: the call stack context as text (TEXT, may be NULL). - Stacked (GET STACKED DIAGNOSTICS): retrieves information from the ExceptionContext struct, which is populated when an exception handler is entered. Available items are: - RETURNED_SQLSTATE: the 5-character SQL state code. - MESSAGE_TEXT: the error message. - EXCEPTION_DETAIL: additional detail text. - EXCEPTION_HINT: hint text. - EXCEPTION_CONTEXT: call stack context. - COLUMN_NAME, CONSTRAINT_NAME, DATATYPE_NAME, TABLE_NAME, SCHEMA_NAME: metadata about the object involved in the error. - Multiple items can be retrieved in a single GET DIAGNOSTICS statement, separated by commas. - Each item is assigned to the specified target variable. If the diagnostic item is not available, NULL is assigned. ## Differences from PostgreSQL - PostgreSQL supports additional diagnostic items not listed here (e.g., PG_ROUTINE_OID). DeltaForge returns NULL for unrecognized items. - GET STACKED DIAGNOSTICS outside an exception handler raises an internal error in DeltaForge. PostgreSQL raises a similar error.
| Name | Type | Description |
|---|---|---|
stacked | Include STACKED to access the current exception context inside an EXCEPTION handler. |
DO $$
DECLARE
row_cnt INT;
BEGIN
DELETE FROM expired_sessions WHERE expiry < now();
GET DIAGNOSTICS row_cnt = ROW_COUNT;
PRINT 'Deleted', row_cnt, 'expired sessions';
END;
$$;DO $$
DECLARE
affected INT;
BEGIN
UPDATE orders SET status = 'archived' WHERE created_at < '2025-01-01';
GET DIAGNOSTICS affected = ROW_COUNT;
IF affected = 0 THEN
PRINT 'No orders to archive';
ELSE
PRINT 'Archived', affected, 'orders';
END IF;
END;
$$;DO $$
DECLARE
err_state TEXT;
err_msg TEXT;
err_detail TEXT;
err_hint TEXT;
BEGIN
SELECT 1/0;
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
err_state = RETURNED_SQLSTATE,
err_msg = MESSAGE_TEXT,
err_detail = EXCEPTION_DETAIL,
err_hint = EXCEPTION_HINT;
PRINT 'State:', err_state;
PRINT 'Message:', err_msg;
PRINT 'Detail:', err_detail;
PRINT 'Hint:', err_hint;
END;
$$;DO $$
DECLARE
rows_inserted INT;
rows_updated INT;
BEGIN
INSERT INTO target_table SELECT * FROM staging WHERE NOT EXISTS (SELECT 1 FROM target_table t WHERE t.id = staging.id);
GET DIAGNOSTICS rows_inserted = ROW_COUNT;
UPDATE target_table SET updated_at = now() WHERE id IN (SELECT id FROM staging);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
PRINT 'Inserted:', rows_inserted, 'Updated:', rows_updated;
END;
$$;