EXECUTE

Executes a dynamically-built SQL string.

Category: dynamic-sql

Syntax

EXECUTE <sql_string> [INTO [STRICT] <target>] [USING <params>];

Description

## Overview EXECUTE runs a dynamically-constructed SQL statement at runtime. It is essential for scenarios where table names, column names, or entire query structures are determined by variable values, such as in generic ETL pipelines or data migration scripts. ## Behavior - The sql_string expression is evaluated first, producing a TEXT value. If the expression evaluates to a non-text type, a type mismatch error is raised. - When USING is specified, each parameter expression is evaluated and the resulting literal value replaces the corresponding $1, $2, etc. placeholder in the SQL string via string substitution. - The resolved SQL is executed through DeltaForge's routed execution engine. SELECT queries go through DataFusion; DML operations (INSERT, UPDATE, DELETE) are routed to the Delta engine. - With INTO, the first result row is decomposed into the target variable(s). STRICT requires exactly one result row; zero rows raise NO_DATA_FOUND and multiple rows raise TOO_MANY_ROWS. - Without INTO, the query executes for side effects only. Result sets from SELECT queries are discarded. - The FOUND variable and diagnostics (ROW_COUNT) are updated after execution. ## Differences from PostgreSQL - USING parameters are substituted via string replacement rather than true parameterized queries. This means $1 placeholders in the SQL string are replaced with SQL literal representations of the values. This is functionally equivalent but differs in implementation from PostgreSQL's prepared-statement approach. - DeltaForge does not cache the plan for EXECUTE statements (each execution re-parses the SQL). PostgreSQL may cache plans for repeated EXECUTE calls with the same query structure.

Parameters

Name	Type	Description
`sql_string`		Build or provide a text expression that evaluates to a valid SQL statement.
`target`		Assign the first result row to a record or scalar variable(s). Add STRICT to require exactly one row.
`params`		Supply parameter values for $1, $2, etc. placeholders in the SQL string.

Examples

DO $$
DECLARE
  row_count INT;
BEGIN
  EXECUTE 'SELECT count(*) FROM orders' INTO row_count;
  PRINT 'Orders:', row_count;
END;
$$;

DO $$
DECLARE
  tbl TEXT := 'customers';
  cnt INT;
BEGIN
  EXECUTE 'SELECT count(*) FROM ' || tbl INTO cnt;
  PRINT tbl, 'has', cnt, 'rows';
END;
$$;

DO $$
DECLARE
  log_msg TEXT := 'Pipeline run completed';
BEGIN
  EXECUTE 'INSERT INTO audit_log (message, created_at) VALUES ($1, now())' USING log_msg;
  PRINT 'Logged:', log_msg;
END;
$$;

DO $$
DECLARE
  rec RECORD;
  schema_name TEXT := 'public';
  table_name TEXT := 'employees';
BEGIN
  EXECUTE 'SELECT * FROM ' || schema_name || '.' || table_name || ' LIMIT 1' INTO STRICT rec;
  PRINT 'First employee:', rec.name;
END;
$$;

DO $$
DECLARE
  col_name TEXT;
  columns TEXT[] := ARRAY['id', 'name', 'status'];
  ddl TEXT;
BEGIN
  ddl := 'CREATE TABLE staging (id BIGINT';
  FOREACH col_name IN ARRAY columns LOOP
    IF col_name <> 'id' THEN
      ddl := ddl || ', ' || col_name || ' TEXT';
    END IF;
  END LOOP;
  ddl := ddl || ')';
  EXECUTE ddl;
  PRINT 'Table created';
END;
$$;

Pitfalls

SQL injection is possible if user-supplied values are concatenated directly into the SQL string. Use USING with $N placeholders instead of string concatenation for data values.
STRICT with INTO raises an error if the query returns zero or more than one row. Omit STRICT if zero rows is an acceptable outcome.
Each EXECUTE re-parses the SQL string. For performance-critical loops, consider using static SQL or FOR...IN...LOOP when the query structure is known at parse time.
USING parameters use string substitution. Complex types (arrays, records) may not serialize correctly as SQL literals.