Dynamic SQL with EXECUTE in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to execute a dynamic SQL query stored in the variable.

PostgreSQL

EXECUTE [1];

Drag options to blanks, or click blank then click option'

Aquery_string

B'query_string'

CEXEC query_string

DEXECUTE query_string()

Attempts:

3 left

💡 Hint

Common Mistakes

Putting quotes around the variable name, which makes it a string literal.

Trying to call EXECUTE as a function.

The EXECUTE command runs the SQL command stored in the variable query_string. You do not put quotes around the variable name.

2fill in blank

medium

Complete the code to build a dynamic SQL query that selects all columns from a table named in the variable.

PostgreSQL

query_string := 'SELECT * FROM ' || [1] || ';';

Drag options to blanks, or click blank then click option'

A'TABLE_NAME'

B'table_name'

Ctable_name

DTABLE_NAME

Attempts:

3 left

💡 Hint

Common Mistakes

Adding quotes around the variable name, which breaks the query.

Using uppercase variable names that are not defined.

To build the query, concatenate the variable table_name without quotes so the table name is inserted dynamically.

3fill in blank

hard

Fix the error in the dynamic SQL execution by completing the code.

PostgreSQL

EXECUTE [1] USING user_id;

Drag options to blanks, or click blank then click option'

A'SELECT * FROM users WHERE id = $1'

Bquery_string

C'query_string'

DEXEC query_string

Attempts:

3 left

💡 Hint

Common Mistakes

Putting quotes around the query variable.

Trying to use EXEC instead of EXECUTE.

The variable query_string should be passed directly to EXECUTE. The query string itself should already contain the placeholder $1 for the parameter.

4fill in blank

hard

Fill both blanks to create a dynamic SQL query that selects a column and table dynamically.

PostgreSQL

query_string := 'SELECT ' || [1] || ' FROM ' || [2] || ';';

Drag options to blanks, or click blank then click option'

Acolumn_name

B'column_name'

Ctable_name

D'table_name'

Attempts:

3 left

💡 Hint

Common Mistakes

Adding quotes around variable names.

Mixing up column and table variable names.

Use the variables column_name and table_name directly to build the query dynamically without quotes.

5fill in blank

hard

Fill all three blanks to build and execute a dynamic SQL query with a parameter.

PostgreSQL

query_string := 'SELECT * FROM ' || [1] || ' WHERE ' || [2] || ' = $1'; EXECUTE [3] USING param_value;

Drag options to blanks, or click blank then click option'

Atable_name

Bcolumn_name

Cquery_string

D'table_name'

Attempts:

3 left

💡 Hint

Common Mistakes

Quoting variable names in the query string.

Passing a quoted string instead of the query variable to EXECUTE.

Use the variables table_name and column_name to build the query string, then execute the query_string with the parameter param_value.

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