Why Dynamic SQL with EXECUTE in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if you could write one query that magically adapts to any table you want?

The Scenario

Imagine you have many tables with similar structures, and you need to run slightly different queries on each one manually.

You open your SQL editor and start writing the same query again and again, just changing table names or conditions.

This feels like copying and pasting hundreds of times, which is boring and tiring.

The Problem

Manually writing queries for each table wastes time and causes mistakes.

One typo can break your query, and fixing hundreds of queries is painful.

Also, if you want to change the query logic, you must update every single query manually.

The Solution

Dynamic SQL with EXECUTE lets you build queries as text inside your code and run them on the fly.

You write one flexible query that adapts to different tables or conditions automatically.

This saves time, reduces errors, and makes your code easier to maintain.

Before vs After

✗ Before

SELECT * FROM sales_2022;
SELECT * FROM sales_2023;

✓ After

EXECUTE format('SELECT * FROM %I', table_name);

What It Enables

You can write one smart query that works for many tables or situations, making your database work faster and smarter.

Real Life Example

A company stores monthly sales data in separate tables like sales_jan, sales_feb, etc.

Using dynamic SQL, they write one query to get data from any month by just changing the table name in the code.

Key Takeaways

Manual queries for many tables are slow and error-prone.

Dynamic SQL builds and runs queries on the fly.

This makes your database tasks faster, safer, and easier to update.

Practice

1. What is the main purpose of using EXECUTE in PostgreSQL dynamic SQL?

easy

A. To run SQL commands that are created during the execution of a program

B. To permanently store SQL commands in the database

C. To create new tables automatically

D. To optimize static SQL queries for faster execution

Solution

1. Step 1: Understand dynamic SQL concept

   Dynamic SQL means building SQL commands as text during program run time, not fixed in advance.

2. Step 2: Role of EXECUTE

   EXECUTE runs these dynamically created SQL commands inside PostgreSQL.

3. Final Answer:

   To run SQL commands that are created during the execution of a program -> Option A

4. Quick Check:

   Dynamic SQL = EXECUTE runs built queries [OK]

Hint: EXECUTE runs SQL built as text during program run [OK]

Common Mistakes:

• Thinking EXECUTE stores queries permanently
• Confusing EXECUTE with static SQL execution
• Assuming EXECUTE creates tables automatically

2. Which of the following is the correct syntax to execute a dynamic SQL query stored in a variable sql_query inside a PL/pgSQL function?

easy

A. EXECUTE sql_query;

B. RUN sql_query;

C. EXEC sql_query;

D. PERFORM sql_query;

Solution

1. Step 1: Recall EXECUTE syntax in PL/pgSQL

   In PL/pgSQL, the correct command to run a dynamic SQL string is EXECUTE followed by the variable.

2. Step 2: Check other options

   RUN and EXEC are not valid PostgreSQL commands; PERFORM runs a static query, not dynamic SQL string.

3. Final Answer:

   EXECUTE sql_query; -> Option A

4. Quick Check:

   EXECUTE runs dynamic SQL string [OK]

Hint: Use EXECUTE to run dynamic SQL strings in PL/pgSQL [OK]

Common Mistakes:

• Using RUN or EXEC which are invalid in PostgreSQL
• Confusing PERFORM with EXECUTE for dynamic SQL
• Missing semicolon after EXECUTE statement

3. Consider this PL/pgSQL snippet:

DECLARE
  table_name text := 'users';
  rec_count int;
BEGIN
  EXECUTE 'SELECT count(*) FROM ' || table_name INTO rec_count;
  RETURN rec_count;
END;

What will this function return when called if the users table has 10 rows?

medium

A. NULL

B. An error because of missing quotes

C. 0

D. 10

Solution

1. Step 1: Understand dynamic SQL concatenation

   The query string becomes 'SELECT count(*) FROM users', which is valid SQL.

2. Step 2: EXECUTE runs the query and stores result

   EXECUTE runs the query and puts the count into rec_count variable.

3. Final Answer:

   10 -> Option D

4. Quick Check:

   Count rows in users = 10 [OK]

Hint: Concatenate table name safely, EXECUTE runs query, INTO stores result [OK]

Common Mistakes:

• Expecting error due to missing quotes around table name
• Assuming rec_count stays NULL without assignment
• Confusing dynamic SQL with static SQL syntax

4. What is the error in this PL/pgSQL code snippet?

DECLARE
  col_name text := 'age';
  val int := 30;
BEGIN
  EXECUTE 'SELECT * FROM people WHERE ' || col_name || ' = val';
END;

medium

A. Incorrect variable declaration syntax

B. Missing semicolon after EXECUTE statement

C. The variable val is not substituted inside the dynamic SQL string

D. EXECUTE cannot be used inside BEGIN...END blocks

Solution

1. Step 1: Analyze dynamic SQL string construction

   The string becomes 'SELECT * FROM people WHERE age = val', where val is literal text, not variable value.

2. Step 2: Understand variable substitution in EXECUTE

   Variables must be concatenated or passed using USING clause to substitute values properly.

3. Final Answer:

   The variable val is not substituted inside the dynamic SQL string -> Option C

4. Quick Check:

   Variables need USING or concatenation in EXECUTE [OK]

Hint: Use USING or concatenate variables in EXECUTE strings [OK]

Common Mistakes:

• Assuming variables auto-substitute inside strings
• Forgetting to use USING clause with EXECUTE
• Thinking EXECUTE can't be inside BEGIN...END

5. You want to write a PL/pgSQL function that takes a table name and a column name as inputs and returns the maximum value in that column. Which approach correctly uses dynamic SQL with EXECUTE to achieve this safely?

hard

A. EXECUTE 'SELECT max(' || col_name || ') FROM ' || table_name INTO result;

B. EXECUTE format('SELECT max(%I) FROM %I', col_name, table_name) INTO result;

C. EXECUTE 'SELECT max($1) FROM $2' USING col_name, table_name INTO result;

D. EXECUTE 'SELECT max(:col_name) FROM :table_name' INTO result;

Solution

1. Step 1: Understand safe dynamic SQL construction

   Using format() with %I safely quotes identifiers like column and table names to avoid SQL injection.

2. Step 2: Check other options for safety and correctness

   EXECUTE 'SELECT max(' || col_name || ') FROM ' || table_name INTO result; concatenates strings directly (unsafe). EXECUTE 'SELECT max($1) FROM $2' USING col_name, table_name INTO result; uses placeholders incorrectly for identifiers. EXECUTE 'SELECT max(:col_name) FROM :table_name' INTO result; uses invalid syntax.

3. Final Answer:

   EXECUTE format('SELECT max(%I) FROM %I', col_name, table_name) INTO result; -> Option B

4. Quick Check:

   Use format() with %I for identifiers in EXECUTE [OK]

Hint: Use format() with %I to safely insert identifiers in EXECUTE [OK]

Common Mistakes:

• Concatenating identifiers without quoting
• Using placeholders for table/column names
• Incorrect EXECUTE syntax with named parameters