In this lesson, you will learn how to construct and execute SQL statements as strings within your procedures, allowing your code to adapt to changing table names or column structures at runtime.
Why Use Dynamic SQL?
Normally, PL/pgSQL code is prepared at compile time. However, sometimes you don't know which table or column you are targeting until the code actually runs—perhaps you are building a generic logging tool or a dynamic partitioning maintenance script. In these cases, hard-coding table names won't work. Dynamic SQL lets you build a query as a text string and command PostgreSQL to "execute" that string as a valid command.
Executing with EXECUTE
In PL/pgSQL, you use the EXECUTE statement. Imagine you want to count the rows in a table passed as an argument. Since you cannot use a parameter for a table name in a standard SELECT, you use dynamic building:
CREATE OR REPLACE FUNCTION count_rows(table_name text)
RETURNS bigint AS $$
DECLARE
result bigint;
BEGIN
-- We build the string, then execute it
EXECUTE 'SELECT count(*) FROM ' || quote_ident(table_name) INTO result;
RETURN result;
END;
$$ LANGUAGE plpgsql;
The Golden Rule: Preventing Injection
The most dangerous mistake in dynamic SQL is concatenating user input directly into a string. If a user provides a table name like users; DROP TABLE users;, your procedure will execute that malicious command.
To prevent this, you must use quote_ident() for identifiers (table or column names) and quote_literal() or USING clauses for data values. The USING clause is the safest approach because it treats the data as a parameter rather than part of the command structure:
-- Using USING to safely pass a parameter
EXECUTE 'SELECT count(*) FROM ' || quote_ident(table_name) || ' WHERE status = $1'
INTO result
USING 'active';
By separating the query structure from the data, you ensure that the database engine treats the input as a literal value rather than executable code, keeping your database secure even when your queries are dynamic.
Key Takeaway: Use EXECUTE to run dynamically generated queries, but always sanitize identifiers with quote_ident() and pass data values using the USING clause to prevent SQL injection.