Dynamic SQL with EXECUTE in PostgreSQL - Step-by-Step Execution

Concept Flow - Dynamic SQL with EXECUTE

Start

↓

Build SQL string dynamically

↓

Use EXECUTE to run SQL string

↓

Process results or finish

↓

End

Dynamic SQL is built as a text string and then run using EXECUTE inside a PL/pgSQL block.

Execution Sample

PostgreSQL

DO $$
DECLARE
  tbl_name text := 'users';
  sql_cmd text;
  result_count integer;
BEGIN
  sql_cmd := 'SELECT count(*) FROM ' || tbl_name;
  EXECUTE sql_cmd INTO result_count;
  RAISE NOTICE 'Count: %', result_count;
END $$;

This code counts rows in a table named dynamically and prints the count.

Execution Table

Step	Action	Variable Values	Result/Output
1	Declare tbl_name = 'users'	tbl_name='users'	No output
2	Build sql_cmd = 'SELECT count(*) FROM users'	sql_cmd='SELECT count(*) FROM users'	No output
3	EXECUTE sql_cmd INTO result_count	result_count=number of rows in users	No output
4	RAISE NOTICE 'Count: %', result_count	result_count=42 (example)	Output: 'Count: 42'
5	End block	All variables finalized	Execution ends

💡 Execution stops after the DO block finishes running all statements.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
tbl_name	NULL	'users'	'users'	'users'
sql_cmd	NULL	'SELECT count(*) FROM users'	'SELECT count(*) FROM users'	'SELECT count(*) FROM users'
result_count	NULL	NULL	42	42

Key Moments - 3 Insights

Why do we build the SQL command as a string before executing?

Because EXECUTE runs a string as SQL, so we must create the full SQL command text first (see execution_table step 2).

How does EXECUTE put the query result into a variable?

Using 'INTO' after EXECUTE stores the query result into a variable (see execution_table step 3).

Can we use variables directly in SQL without building a string?

No, EXECUTE requires a full SQL string; variables must be concatenated into that string first.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what does EXECUTE do?

A Prints the SQL string to the screen

B Runs the SQL string and stores the result into result_count

C Declares a new variable

D Ends the program

💡 Hint

Check the 'Action' and 'Result/Output' columns at step 3 in execution_table.

At which step is the SQL command string created?

A Step 4

B Step 1

C Step 2

D Step 5

💡 Hint

Look at the 'Build sql_cmd' action in execution_table.

If tbl_name was changed to 'orders', what changes in the execution_table?

A sql_cmd would be 'SELECT count(*) FROM orders'

B result_count would be NULL

C RAISE NOTICE would not run

D No changes at all

💡 Hint

See how sql_cmd is built by concatenating tbl_name in execution_table step 2.

Concept Snapshot

Dynamic SQL with EXECUTE in PostgreSQL:
- Build SQL command as a text string
- Use EXECUTE to run the string inside PL/pgSQL
- Use INTO to capture query results
- Allows flexible queries with variable table or column names
- Must carefully construct valid SQL strings

Full Transcript

Dynamic SQL with EXECUTE in PostgreSQL means creating a SQL command as a text string and then running it inside a PL/pgSQL block using EXECUTE. First, you declare variables like the table name. Then you build the SQL command by joining strings and variables. Next, EXECUTE runs this command and can store the result into a variable using INTO. Finally, you can use the result, for example, printing it with RAISE NOTICE. This lets you run flexible queries where parts like table names change at runtime. The execution table shows each step: declaring variables, building the command string, running EXECUTE, and outputting results. Key points are that EXECUTE needs a full SQL string and INTO captures the output. If you change the table name variable, the SQL command string changes accordingly. This method is useful for dynamic queries in PostgreSQL.