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

Need a 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

Need a 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

Need a 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

Need a hint?

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