Dynamic SQL with EXECUTE in PostgreSQL

Introduction

Dynamic SQL lets you build and run SQL commands on the fly. This helps when you don't know the exact query until your program runs.

When you want to choose table names or columns based on user input.

When you need to run different queries depending on conditions in your program.

When you want to automate tasks that require flexible SQL commands.

When writing stored procedures that must adapt to varying inputs.

When you want to avoid writing many similar queries manually.

Syntax

PostgreSQL

EXECUTE 'sql_command_string';

The SQL command must be a string inside single quotes.

Use EXECUTE inside PL/pgSQL functions or DO blocks to run dynamic SQL.

Examples

Runs a fixed SQL query dynamically.

PostgreSQL

EXECUTE 'SELECT * FROM users WHERE id = 1';

Builds a safe dynamic query using format() to insert table, column, and value.

PostgreSQL

EXECUTE format('SELECT * FROM %I WHERE %I = %L', table_name, column_name, value);

Concatenates strings to create a dynamic UPDATE statement.

PostgreSQL

EXECUTE 'UPDATE ' || table_name || ' SET name = ''John'' WHERE id = 5';

Sample Program

This block runs a dynamic SELECT query on the 'employees' table to find someone in the 'Sales' department. It uses EXECUTE with format() for safety. It then prints a message depending on the result.

PostgreSQL

DO $$
DECLARE
  table_name text := 'employees';
  column_name text := 'department';
  value text := 'Sales';
  result record;
BEGIN
  EXECUTE format('SELECT * FROM %I WHERE %I = %L', table_name, column_name, value) INTO result;
  IF result IS NOT NULL THEN
    RAISE NOTICE 'Found employee in %', result.department;
  ELSE
    RAISE NOTICE 'No employee found in %', value;
  END IF;
END $$;

Important Notes

Always use format() with %I and %L to safely insert identifiers and literals to avoid SQL injection.

EXECUTE runs inside PL/pgSQL blocks, not in plain SQL queries.

Dynamic SQL is powerful but can be harder to debug, so test carefully.

Summary

Dynamic SQL with EXECUTE lets you run SQL commands built during program execution.

Use it when queries need to change based on input or conditions.

Always build dynamic queries safely to avoid errors and security risks.