How to Use EXECUTE in PL/pgSQL: Syntax and Examples

In PL/pgSQL, use the EXECUTE statement to run dynamic SQL commands constructed as strings at runtime. This allows you to execute queries that cannot be hardcoded, such as those with variable table names or columns. The syntax is EXECUTE sql_string; where sql_string is a text containing the SQL command.

Syntax

The EXECUTE statement runs a dynamically created SQL command stored as a string. You write the SQL command as text, then EXECUTE runs it.

• EXECUTE: keyword to run dynamic SQL.
• sql_string: a text string containing the SQL command to execute.
• You can use USING to safely pass parameters to avoid SQL injection.

sql

EXECUTE sql_string [ USING expression [, ...] ];

Example

This example shows how to use EXECUTE to select from a table with a dynamic name and return the count of rows.

sql

CREATE OR REPLACE FUNCTION count_rows(tablename text) RETURNS integer AS $$
DECLARE
  result integer;
  sql_cmd text;
BEGIN
  sql_cmd := 'SELECT count(*) FROM ' || quote_ident(tablename);
  EXECUTE sql_cmd INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Usage example:
SELECT count_rows('pg_class');

Output

 count_rows 
------------
         123
(1 row)

Common Pitfalls

Common mistakes when using EXECUTE include:

• Not using quote_ident() or quote_literal() to safely include identifiers or values, risking SQL injection.
• Trying to use variables directly inside the SQL string without concatenation.
• Forgetting to use INTO to capture query results.

Wrong example (unsafe):

sql

EXECUTE 'SELECT * FROM ' || tablename || ' WHERE id = ' || id_var;

Common Pitfalls

Right way using quote_ident() and USING to safely pass parameters:

sql

EXECUTE 'SELECT * FROM ' || quote_ident(tablename) || ' WHERE id = $1' USING id_var;

Quick Reference

Feature	Description
EXECUTE sql_string;	Runs the SQL command in the string.
EXECUTE sql_string INTO target;	Runs SQL and stores result into variable.
EXECUTE sql_string USING var1, var2;	Passes variables safely to SQL.
quote_ident(text)	Safely quotes SQL identifiers like table names.
quote_literal(text)	Safely quotes literal values for SQL.

Key Takeaways

Use EXECUTE to run SQL commands built as strings at runtime in PL/pgSQL.

Always use quote_ident() for identifiers and USING for parameters to avoid SQL injection.

Capture query results with INTO when executing SELECT statements.

Avoid embedding variables directly in SQL strings without proper quoting.

EXECUTE is essential for dynamic SQL where table or column names vary.