Dynamic SQL with EXECUTE in PostgreSQL - Deep Dive

Overview - Dynamic SQL with EXECUTE

What is it?

Dynamic SQL is a way to build and run SQL commands on the fly, instead of writing fixed queries. In PostgreSQL, the EXECUTE statement inside a PL/pgSQL function lets you run these dynamic commands. This helps when you don't know the exact table name, column, or condition until the program runs. It makes your database code flexible and adaptable.

Why it matters

Without dynamic SQL, you would have to write many fixed queries for every possible case, which is slow and hard to maintain. Dynamic SQL solves this by letting you create queries as needed, saving time and reducing errors. It is especially useful for applications that work with different tables or user inputs. Without it, your database code would be rigid and less powerful.

Where it fits

Before learning dynamic SQL, you should understand basic SQL queries and PL/pgSQL functions. After mastering dynamic SQL, you can explore advanced topics like query optimization, security with parameterization, and building complex database applications.

Mental Model

Core Idea

Dynamic SQL with EXECUTE lets you build and run SQL commands as text strings during program execution, making queries flexible and adaptable.

Think of it like...

Imagine writing a letter where you leave some blanks to fill in later, like the recipient's name or address. Dynamic SQL is like writing that letter template and filling in the blanks just before sending it.

┌───────────────────────────────┐
│  PL/pgSQL Function            │
│  ┌─────────────────────────┐  │
│  │ Build SQL command string │  │
│  └─────────────┬───────────┘  │
│                │              │
│          ┌─────▼─────┐        │
│          │ EXECUTE   │        │
│          │ command   │        │
│          └─────┬─────┘        │
│                │              │
│       Runs SQL command         │
└───────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Static SQL Queries

Concept: Learn what fixed SQL queries look like and how they run in PostgreSQL.

A static SQL query is written directly in your code, like: SELECT * FROM users WHERE id = 1;. This query always asks for the same data and does not change unless you edit the code.

Result

The database returns the rows matching the fixed query every time.

Understanding static queries is essential because dynamic SQL builds on this by changing the query text during execution.

2

Foundation

Introduction to PL/pgSQL Functions

Concept: Learn how to write simple functions in PostgreSQL to run SQL commands.

PL/pgSQL lets you write functions that can run SQL commands and return results. For example, a function can SELECT data and return it to the caller.

Result

You can reuse SQL logic inside functions and call them with parameters.

Knowing how functions work is key because dynamic SQL is often used inside these functions to build flexible queries.

3

Intermediate

Building Dynamic SQL Strings

🤔Before reading on: do you think you can safely build a SQL query by just joining strings with user input? Commit to yes or no.

Concept: Learn how to create SQL commands as text strings that change based on input or conditions.

You can create a text variable in PL/pgSQL and assign it a SQL command as a string. For example: sql_command := 'SELECT * FROM ' || table_name || ' WHERE id = ' || id_value || ';'; This builds a query using variables.

Result

You get a complete SQL command as a string that can be run later.

Understanding how to build query strings is the foundation of dynamic SQL, but it also introduces risks if not done carefully.

4

Intermediate

Executing Dynamic SQL with EXECUTE

🤔Before reading on: do you think EXECUTE runs the SQL string immediately or just stores it? Commit to your answer.

Concept: Learn how to run the dynamic SQL string inside a PL/pgSQL function using EXECUTE.

The EXECUTE statement takes a text string containing a SQL command and runs it immediately. For example: EXECUTE sql_command; This runs the query built as a string.

Result

The database executes the dynamic query and returns or affects data as if it was a fixed query.

Knowing that EXECUTE runs the command immediately helps you understand how dynamic SQL integrates with your function logic.

5

Intermediate

Using Parameters Safely in Dynamic SQL

🤔Before reading on: do you think concatenating user input directly into SQL strings is safe? Commit yes or no.

Concept: Learn how to use parameter placeholders with EXECUTE to avoid SQL injection risks.

Instead of joining user input directly, use EXECUTE with USING clause: EXECUTE 'SELECT * FROM users WHERE id = $1' USING user_id;. This safely passes parameters without risk.

Result

Dynamic queries run safely without allowing malicious input to change the command structure.

Understanding parameterization is critical for writing secure dynamic SQL and preventing common security bugs.

6

Advanced

Returning Query Results from EXECUTE

🤔Before reading on: do you think EXECUTE can return query results directly or only run commands? Commit your guess.

Concept: Learn how to capture results from dynamic queries using EXECUTE INTO or FOR loops.

You can store the result of a dynamic SELECT into variables: EXECUTE sql_command INTO result_var;. Or loop over rows: FOR rec IN EXECUTE sql_command LOOP ... END LOOP;

Result

Your function can process data returned by dynamic queries just like static ones.

Knowing how to get results from EXECUTE lets you build powerful, flexible functions that adapt to different data.

7

Expert

Performance and Caching Considerations

🤔Before reading on: do you think dynamic SQL queries are always as fast as static ones? Commit yes or no.

Concept: Understand how dynamic SQL affects query planning and caching in PostgreSQL.

Static queries are planned once and cached, but dynamic SQL is planned every time EXECUTE runs, which can add overhead. Using PREPARE statements or careful design can reduce this cost.

Result

You learn to balance flexibility with performance by minimizing repeated planning.

Knowing the internal cost of dynamic SQL helps you write efficient database functions and avoid slowdowns in production.

Under the Hood

When EXECUTE runs, PostgreSQL takes the SQL command string, parses it, plans the query, and executes it immediately. Unlike static SQL, which is parsed and planned once at function creation, dynamic SQL is parsed and planned every time EXECUTE runs. Parameters passed with USING are safely substituted to prevent injection. The results can be fetched into variables or processed row by row.

Why designed this way?

Dynamic SQL was designed to allow flexible queries that cannot be known at compile time. Parsing and planning at runtime trades some speed for flexibility. The USING clause was added to improve security by separating code from data. Alternatives like static SQL are faster but less flexible, so dynamic SQL fills an important gap.

┌───────────────┐
│ PL/pgSQL Code │
└──────┬────────┘
       │ Builds SQL string
       ▼
┌───────────────┐
│ SQL Command   │
│ (text string) │
└──────┬────────┘
       │ EXECUTE runs
       ▼
┌───────────────┐
│ Parser &      │
│ Planner       │
└──────┬────────┘
       │ Executes query
       ▼
┌───────────────┐
│ Query Result  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does EXECUTE automatically protect against SQL injection? Commit yes or no.

Common Belief: EXECUTE automatically makes dynamic SQL safe from SQL injection.

Reality: EXECUTE runs whatever SQL string you give it, so if you build that string by concatenating user input directly, it is vulnerable to injection.

Why it matters: Believing EXECUTE is safe without parameterization can lead to serious security breaches exposing or damaging data.

Quick: Is dynamic SQL always slower than static SQL? Commit yes or no.

Common Belief: Dynamic SQL is always slower than static SQL because it is built and planned every time.

Reality: While dynamic SQL has overhead, careful use of PREPARE statements or caching can reduce this. Also, static SQL cannot handle all flexible cases.

Why it matters: Assuming dynamic SQL is too slow may prevent you from using it where it is the best or only solution.

Quick: Can EXECUTE return query results directly without INTO or loops? Commit yes or no.

Common Belief: EXECUTE runs the query but cannot return results directly; you must use static queries for that.

Reality: EXECUTE can return results by using INTO to store a single row or FOR loops to process multiple rows.

Why it matters: Not knowing this limits your ability to write flexible functions that handle dynamic queries fully.

Quick: Does dynamic SQL replace the need for all static queries? Commit yes or no.

Common Belief: Dynamic SQL should be used everywhere instead of static queries for maximum flexibility.

Reality: Static SQL is simpler, safer, and faster for fixed queries. Dynamic SQL is best only when flexibility is needed.

Why it matters: Overusing dynamic SQL can make code complex, harder to maintain, and less performant.

Expert Zone

1

Dynamic SQL planning happens at runtime, so repeated EXECUTE calls with different queries can cause performance hits due to lack of plan caching.

2

Using the USING clause with EXECUTE not only prevents SQL injection but also allows PostgreSQL to optimize parameter handling better.

3

Dynamic SQL inside functions can complicate debugging because errors occur at runtime and may depend on input values, requiring careful logging and testing.

When NOT to use

Avoid dynamic SQL when queries are fixed or can be handled with static SQL and parameters. Use static SQL for better performance and security. For complex query building, consider ORMs or query builders outside the database.

Production Patterns

In production, dynamic SQL is often used for multi-tenant applications where table names vary, or for administrative scripts that operate on different schemas. It is combined with strict input validation and parameterization to maintain security and performance.

Connections

Prepared Statements

Builds-on

Understanding dynamic SQL helps grasp prepared statements, which pre-compile queries for repeated execution, improving performance.

Code Injection in Web Security

Opposite

Knowing how dynamic SQL can be vulnerable to injection attacks parallels understanding code injection in web apps, highlighting the importance of input sanitization.

Template Engines in Web Development

Similar pattern

Dynamic SQL is like template engines that build HTML pages from templates and data, showing how text generation with placeholders is a common programming pattern.

Common Pitfalls

#1 Concatenating user input directly into SQL strings causing SQL injection.

Wrong approach:

sql_command := 'SELECT * FROM users WHERE name = ''' || user_input || ''';'; EXECUTE sql_command;

Correct approach:

EXECUTE 'SELECT * FROM users WHERE name = $1' USING user_input;

Root cause:

Misunderstanding that string concatenation mixes code and data, allowing malicious input to alter query structure.

#2 Expecting EXECUTE to return query results without INTO or loops.

Wrong approach:

EXECUTE 'SELECT id FROM users WHERE active = true'; -- no INTO or loop

Correct approach:

EXECUTE 'SELECT id FROM users WHERE active = true' INTO some_variable; -- or use FOR loop

Root cause:

Not knowing how to capture results from dynamic queries inside PL/pgSQL.

#3 Using dynamic SQL for simple fixed queries unnecessarily.

Wrong approach:

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

Correct approach:

SELECT * FROM users WHERE id = 1;

Root cause:

Overcomplicating code by using dynamic SQL when static SQL is simpler and more efficient.

Key Takeaways

Dynamic SQL with EXECUTE lets you build and run flexible SQL commands as text strings during function execution.

Always use parameterization with EXECUTE to prevent SQL injection and keep your database secure.

Dynamic SQL queries are parsed and planned at runtime, which can affect performance compared to static SQL.

You can capture results from dynamic queries using INTO or loops, enabling full data processing inside functions.

Use dynamic SQL only when necessary; prefer static SQL for fixed queries to keep code simple and efficient.

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