Dynamic SQL and Stored Procedures

Dynamic SQL — SQL built as a string and executed at runtime — is necessary when table names, column names, or operators cannot be known at query-writing time. PostgreSQL provides EXECUTE for dynamic SQL inside PL/pgSQL, and FORMAT for safe string construction.

Stored procedures (created with CREATE PROCEDURE, not CREATE FUNCTION) can manage their own transactions — committing and rolling back within the procedure body. This is different from functions, which always run within the caller's transaction.

Example 1: Dynamic table operations with EXECUTE

-- ❌
-- Create a summary table for any parent table dynamically
CREATE OR REPLACE FUNCTION create_monthly_summary(
    p_source_table TEXT,
    p_date_column  TEXT,
    p_amount_column TEXT
)
RETURNS INT AS $$
DECLARE
    v_summary_table TEXT;
    v_row_count     INT;
BEGIN
    v_summary_table := p_source_table || '_monthly';

    -- Safely construct and execute dynamic SQL
    EXECUTE FORMAT(
        $sql$CREATE TABLE IF NOT EXISTS %I AS
         SELECT
             DATE_TRUNC('month', %I)::DATE AS month,
             COUNT(*) AS row_count,
             SUM(%I) AS total_amount,
             AVG(%I) AS avg_amount
         FROM %I
         GROUP BY 1$sql$,
        v_summary_table,    -- %I = identifier (quoted safely)
        p_date_column,
        p_amount_column,
        p_amount_column,
        p_source_table
    );

    EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(v_summary_table)
    INTO v_row_count;

    RETURN v_row_count;
END;
$$ LANGUAGE plpgsql;

-- Usage:
SELECT create_monthly_summary('orders', 'created_at', 'total_amount');
SELECT create_monthly_summary('invoices', 'issue_date', 'invoice_total');

FORMAT('%I', value) — %I produces a properly double-quoted identifier (safe against SQL injection and reserved words). %L produces a properly single-quoted literal. Never use string concatenation with user-supplied values — always use %I and %L.

Example 2: EXECUTE with dynamic WHERE clauses

-- Generic search function with dynamic filters
CREATE OR REPLACE FUNCTION search_orders(
    p_status      TEXT DEFAULT NULL,
    p_min_amount  NUMERIC DEFAULT NULL,
    p_max_amount  NUMERIC DEFAULT NULL,
    p_channel     TEXT DEFAULT NULL,
    p_limit       INT DEFAULT 50
)
RETURNS SETOF orders AS $$
DECLARE
    v_sql    TEXT;
    v_params TEXT[] := '{}';
    v_idx    INT := 1;
BEGIN
    v_sql := 'SELECT * FROM ec_orders WHERE 1=1';

    IF p_status IS NOT NULL THEN
        v_sql := v_sql || ' AND status = $' || v_idx;
        v_params := v_params || p_status;
        v_idx := v_idx + 1;
    END IF;

    IF p_min_amount IS NOT NULL THEN
        v_sql := v_sql || ' AND total_amount >= $' || v_idx;
        v_params := v_params || p_min_amount::TEXT;
        v_idx := v_idx + 1;
    END IF;

    IF p_max_amount IS NOT NULL THEN
        v_sql := v_sql || ' AND total_amount <= $' || v_idx;
        v_params := v_params || p_max_amount::TEXT;
        v_idx := v_idx + 1;
    END IF;

    v_sql := v_sql || ' ORDER BY created_at DESC LIMIT ' || p_limit;

    RETURN QUERY EXECUTE v_sql USING
        (v_params[1])::TEXT, (v_params[2])::TEXT, (v_params[3])::TEXT;
END;
$$ LANGUAGE plpgsql;

Example 3: Stored procedures with transaction control

-- Procedure that processes a batch of overdue invoices
-- Can commit after each chunk (important for long-running processes)
CREATE OR REPLACE PROCEDURE process_overdue_batch(p_batch_size INT DEFAULT 100)
LANGUAGE plpgsql AS $$
DECLARE
    v_processed INT := 0;
    v_total INT;
BEGIN
    SELECT COUNT(*) INTO v_total
    FROM invoices
    WHERE status = 'sent' AND due_date < CURRENT_DATE - 7;

    RAISE NOTICE 'Processing % overdue invoices in batches of %', v_total, p_batch_size;

    LOOP
        -- Update a batch
        WITH batch AS (
            SELECT invoice_id FROM invoices
            WHERE status = 'sent' AND due_date < CURRENT_DATE - 7
            LIMIT p_batch_size
            FOR UPDATE SKIP LOCKED
        )
        UPDATE invoices SET status = 'overdue'
        FROM batch WHERE invoices.invoice_id = batch.invoice_id;

        GET DIAGNOSTICS v_processed = ROW_COUNT;

        -- COMMIT inside a procedure is allowed (not in functions)
        COMMIT;

        EXIT WHEN v_processed < p_batch_size;

        RAISE NOTICE 'Committed batch of % rows', v_processed;
    END LOOP;

    RAISE NOTICE 'Batch processing complete';
END;
$$;

-- Call with CALL (not SELECT)
CALL process_overdue_batch(50);

Key Takeaway

EXECUTE FORMAT('%I', ...) is the safe way to build dynamic SQL with identifiers — never concatenate user-supplied strings directly. %L for literals, %I for identifiers. Stored procedures (CREATE PROCEDURE) can COMMIT mid-execution; functions cannot. Use procedures for long-running maintenance tasks that need to commit in chunks to avoid locking the whole table. Use functions for computations that must be atomic with the caller's transaction.