Dynamic SQL with EXECUTE in PostgreSQL - Mini Project: Build & Apply

Dynamic SQL with EXECUTE in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to create a flexible way to query the books table based on different columns and values without writing many fixed queries.

🎯 Goal: Build a simple PostgreSQL function that uses dynamic SQL with EXECUTE to select books from the books table based on a column name and a value passed as parameters.

📋 What You'll Learn

Create a books table with columns id, title, author, and year.

Insert sample data into the books table.

Create a function called get_books_by_column that takes two parameters: col_name (text) and col_value (text).

Use dynamic SQL with EXECUTE inside the function to select rows where the column named col_name equals col_value.

Return the matching rows from the function.

💡 Why This Matters

🌍 Real World

Dynamic SQL is useful when you want flexible queries that depend on user input or variable conditions, such as filtering reports or searching tables by different columns.

💼 Career

Database developers and administrators often write dynamic SQL functions to create reusable, adaptable database operations that reduce repetitive code and improve maintainability.

1

Create the books table and insert sample data

Write SQL statements to create a table called books with columns id (integer primary key), title (text), author (text), and year (integer). Then insert these exact rows: (1, 'The Hobbit', 'J.R.R. Tolkien', 1937), (2, '1984', 'George Orwell', 1949), (3, 'To Kill a Mockingbird', 'Harper Lee', 1960).

PostgreSQL

-- Create the books table
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Declare the function header with parameters

Write the first line of a PostgreSQL function called get_books_by_column that takes two parameters: col_name of type text and col_value of type text. The function should return a set of rows from the books table. Use RETURNS SETOF books.

PostgreSQL

CREATE FUNCTION get_books_by_column(col_name text, col_value text) RETURNS SETOF books AS $$
-- Your code here

Hint

Start the function with CREATE FUNCTION and specify the parameters and return type exactly.

3

Write the dynamic SQL query using EXECUTE

Inside the function body, declare a variable sql_query of type text. Assign to sql_query a string that selects all columns from books where the column named by col_name equals the parameter col_value. Use format() to safely build the query string with %I for the column name and %L for the value. Then use RETURN QUERY EXECUTE sql_query; to run the query and return the results.

PostgreSQL

DECLARE
    sql_query text;
BEGIN
    -- Your code here

Hint

Use format() with %I for identifiers and %L for literals to build the query safely.

4

Complete the function with proper ending

Add the END; and $$ LANGUAGE plpgsql; lines to properly close the function definition.

PostgreSQL

-- Your code here to end the function

Hint

Close the function with END; and specify the language as plpgsql.

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