Nested Procedure Calls with EXECUTE: Dynamic SQL Execution

Before we jump into hands-on stuff, let's answer the question: what the heck is dynamic SQL? Imagine you need to create a table with a unique name that's passed in as a parameter. Or you want to query a table whose name is only known at runtime. Static SQL just won't cut it here — that's where dynamic execution comes to the rescue.

PL/pgSQL gives you the EXECUTE command, which runs an SQL query passed as a string. This lets you build and run SQL code "on the fly," creating queries that change depending on your parameters.

Why dynamic SQL can be super useful:

1. Flexibility: You can build queries dynamically based on input data. For example, working with tables or columns whose names you don't know ahead of time.
2. Automation: Creating tables or indexes with unique names.
3. Versatility: You can work with different data structures without having to rewrite your procedure every time.

Real-life example: imagine you're building an analytics system, and for every new client you need to create a separate table to store their data. You can totally automate this with EXECUTE.

Syntax of EXECUTE

Using dynamic SQL with EXECUTE looks like this:

EXECUTE 'SQL-string';

Here's a simple example:

DO $$
BEGIN
  EXECUTE 'CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)';
END $$;

This code block will create a table called test_table. Pretty straightforward, but let's check out some more advanced scenarios.

Examples of Using EXECUTE

1. Creating a Table with a Dynamic Name

Let's say you need to create tables with names based on the current date. Here's how you can do it:

DO $$
DECLARE
  table_name TEXT;
BEGIN
  -- Generate the table name
  table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');

  -- Create the table with a dynamic name
  EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';

  -- Output a message for checking
  RAISE NOTICE 'Table % created successfully', table_name;
END $$;

Here, the dynamic name is generated from the current date, and the final SQL string is passed to EXECUTE.

2. Running a Query with Dynamic Parameters

Suppose you need to fetch data from a table whose name is passed as a parameter. Let's write a function for that:

CREATE OR REPLACE FUNCTION get_data_from_table(table_name TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT id, name FROM ' || table_name || ' WHERE id < 10';
END $$ LANGUAGE plpgsql;

Calling the function:

SELECT * FROM get_data_from_table('employees');

This approach is awesome for building universal utilities, like dynamic reporting systems.

Problems and Limitations of Dynamic SQL

Dynamic SQL execution gives you a ton of freedom, but just like in real life, freedom comes with responsibility. Here are some gotchas:

1. SQL Injection: If you pass string parameters into your query without handling them, you might let a bad actor run any SQL code they want.

   Example of vulnerable code:

   EXECUTE 'SELECT * FROM users WHERE name = ''' || user_input || '''';
   

   If user_input contains '; DROP TABLE users; --, the query will nuke the users table.

2. Debugging is Harder: Dynamic code is trickier to analyze and debug, since the query is built and run at execution time.

3. Performance Hit: Dynamic queries bypass PostgreSQL's execution plan caching, which can slow things down.

How to Protect Against SQL Injection

To avoid SQL injection attacks, use parameterization in your dynamic queries instead of just string concatenation. In PL/pgSQL, you do this with quote_literal() for string parameters and quote_ident() for identifiers (like table or column names).

Example of safe code:

DO $$
DECLARE
  table_name TEXT;
  user_input TEXT := 'John';
BEGIN
  table_name := 'employees';

  EXECUTE 'SELECT * FROM ' || quote_ident(table_name) ||
          ' WHERE name = ' || quote_literal(user_input);
END $$;

Implementation: Dynamically Updating Tables

Here's an example of a procedure that updates values in a table whose name is passed as a parameter:

CREATE OR REPLACE FUNCTION update_table_data(table_name TEXT, id_value INT, new_data TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE 'UPDATE ' || quote_ident(table_name) ||
          ' SET data = ' || quote_literal(new_data) ||
          ' WHERE id = ' || id_value;
END $$ LANGUAGE plpgsql;

Calling the function:

SELECT update_table_data('test_table', 1, 'Updated Value');

Example: Creating a Report for a Client

Let's say you're tracking orders by client and want to automate the process of creating a report table for each client.

CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  -- Build the report table name
  table_name := 'client_report_' || client_id;

  -- Create the report table
  EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';

  -- Fill the table with data
  EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
          ' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;

  RAISE NOTICE 'Report for client % created: table %', client_id, table_name;
END $$ LANGUAGE plpgsql;

Dynamic SQL with EXECUTE is a powerful tool that opens up insane possibilities for automation and flexibility in PL/pgSQL. Use it with care, and always keep SQL injection risks in mind. If you want your queries to be solid and safe, use quote_ident() and quote_literal() functions.

In the next lecture, we'll dive into building complex procedures that include data validation, record updates, and operation logging. Get ready — working with dynamic queries is gonna be the foundation for making all that happen!

2

Task

SQL SELF, level 53, lesson 4

Locked

Creating a Table with a Dynamic Name

Creating a Table with a Dynamic Name

Open

1

Survey/quiz

Nested Transactions, level 53, lesson 4

Unavailable

Nested Transactions

Nested Transactions

Open