Dynamic SQL with EXECUTE in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is Dynamic SQL in PostgreSQL?

Dynamic SQL is a way to build and run SQL commands as text strings at runtime, allowing flexible queries that can change based on input or conditions.

beginner

How do you execute a dynamic SQL command inside a PL/pgSQL function?

You use the EXECUTE statement followed by a string containing the SQL command you want to run dynamically.

intermediate

Why should you use quote_ident() or quote_literal() when building dynamic SQL?

These functions safely add identifiers or values to the SQL string, preventing errors and SQL injection by properly quoting them.

intermediate

What happens if you try to use EXECUTE with a variable that is not a string?

EXECUTE requires a string argument. If you pass a non-string variable, you must convert it to text first, or it will cause an error.

intermediate

Can you return results directly from EXECUTE in PL/pgSQL?

Yes, you can use EXECUTE with INTO to store the result of a dynamic query into a variable.

Which PostgreSQL statement runs a dynamic SQL command stored in a string variable?

A EXECUTE

B RUN

C CALL

D PERFORM

EXECUTE runs a SQL command given as a string in PL/pgSQL.

What function should you use to safely add a table name to a dynamic SQL string?

A quote_literal()

B quote_ident()

C to_char()

D cast()

quote_ident() safely quotes identifiers like table or column names.

How do you capture the result of a dynamic SELECT query in PL/pgSQL?

A EXECUTE ... INTO variable

B PERFORM ... INTO variable

C CALL ... INTO variable

D SELECT ... INTO variable

EXECUTE ... INTO stores the result of a dynamic query into a variable.

What is a risk if you build dynamic SQL without quoting identifiers or literals?

A No risk at all

B Faster query execution

C Automatic optimization

D Syntax errors and SQL injection

Not quoting can cause syntax errors and security risks like SQL injection.

Which data type must EXECUTE receive as input?

A Integer

B Boolean

C Text or string

D Date

EXECUTE requires a string containing the SQL command.

Explain how to safely build and run a dynamic SQL query in PostgreSQL using EXECUTE.

Think about how to avoid errors and injection risks.

Describe a scenario where dynamic SQL with EXECUTE is useful and how you would implement it.

Imagine you want to query different tables based on user input.

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