Dynamic SQL with EXECUTE in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of Dynamic SQL with EXECUTE in a Function

Consider the following PostgreSQL function that uses dynamic SQL with EXECUTE to select data from a table:

CREATE OR REPLACE FUNCTION get_table_count(tablename text) RETURNS integer AS $$
DECLARE
  result integer;
BEGIN
  EXECUTE 'SELECT count(*) FROM ' || quote_ident(tablename) INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

Assuming there is a table named employees with 5 rows, what will be the output of SELECT get_table_count('employees');?

A 5

B count(*)

C tablename

D Error: relation "employees" does not exist

💡 Hint

Think about how EXECUTE runs the SQL string and stores the result into the variable.

The function builds a SQL query dynamically using the table name, then executes it and stores the count into result. Since the table employees has 5 rows, the function returns 5.

📝 Syntax

intermediate

Identify the Syntax Error in Dynamic SQL EXECUTE

Which of the following dynamic SQL statements in a PL/pgSQL block will cause a syntax error when using EXECUTE?

DECLARE
  sql_text text;
BEGIN
  sql_text := 'SELECT * FROM ' || tablename;
  EXECUTE sql_text;
END;

A EXECUTE 'SELECT * FROM ' + tablename;

B EXECUTE 'SELECT * FROM ' || tablename;

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

D EXECUTE 'SELECT * FROM ' || quote_ident(tablename);

💡 Hint

Remember how string concatenation works in PostgreSQL.

In PostgreSQL, string concatenation uses ||, not +. Using + causes a syntax error.

❓ optimization

advanced

Optimizing Dynamic SQL with EXECUTE for Repeated Queries

You have a PL/pgSQL function that runs dynamic SQL with EXECUTE multiple times for the same table name. Which approach improves performance by avoiding repeated parsing?

A Use <code>EXECUTE</code> with a dynamically built SQL string every time inside the loop.

B Use <code>EXECUTE</code> with concatenated strings without quoting identifiers.

C Use static SQL queries instead of dynamic SQL.

D Use <code>PREPARE</code> once with the dynamic SQL, then <code>EXECUTE</code> the prepared statement multiple times.

💡 Hint

Think about how PostgreSQL handles query planning and execution.

Preparing a statement once and executing it multiple times avoids repeated parsing and planning, improving performance for repeated dynamic queries.

🔧 Debug

advanced

Debugging an EXECUTE Statement with Variable Substitution

Given the following PL/pgSQL code snippet, what is the cause of the error?

DECLARE
  tablename text := 'users';
  result integer;
BEGIN
  EXECUTE 'SELECT count(*) FROM tablename' INTO result;
  RETURN result;
END;

A The <code>INTO</code> clause is not allowed with <code>EXECUTE</code>.

B The variable <code>result</code> is not declared properly.

C The variable <code>tablename</code> is not substituted inside the string, so the query looks for a table literally named 'tablename'.

D The <code>EXECUTE</code> statement requires double quotes around the table name.

💡 Hint

Check how variables are included in dynamic SQL strings.

The string 'SELECT count(*) FROM tablename' is taken literally, so PostgreSQL looks for a table named 'tablename', which likely does not exist. The variable must be concatenated or formatted into the string.

🧠 Conceptual

expert

Understanding Security Risks of Dynamic SQL with EXECUTE

Which of the following statements best explains a security risk when using dynamic SQL with EXECUTE in PostgreSQL?

A Dynamic SQL always runs slower than static SQL, so it is a security risk.

B Dynamic SQL with EXECUTE can lead to SQL injection if user input is concatenated directly without proper quoting or validation.

C EXECUTE statements cannot be logged, so auditing is impossible.

D Dynamic SQL disables all permission checks on tables.

💡 Hint

Think about how user input can affect SQL commands.

If user input is concatenated directly into dynamic SQL without using functions like quote_ident or quote_literal, attackers can inject malicious SQL code, causing security breaches.