# Lesson 14: PL/pgSQL / Dynamic Commands > Native Markdown source page: PL/pgSQL / Dynamic Commands --- LLMS index: [llms.txt](/en/llms.txt) --- > [!NOTE] > This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. > **HTML source:** [dev1_14_plpgsql_dynamic.html](https://edu.postgrespro.com/16/dev1-16-en/dev1_14_plpgsql_dynamic.html) > **Archive:** [DEV1-handouts-16-en.zip](https://edu.postgrespro.com/16/DEV1-handouts-16-en.zip) > **PDF pages:** 16 ## Page 1: PL/pgSQL / Dynamic Commands {#source-page-001} ### Copyright © Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova Photo by: Oleg Bartunov (Phu monastery, Bhrikuti summit, Nepal) ### Use of Course Materials Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials. ### Feedback Please send your feedback, comments and suggestions to: edu@postgrespro.ru ### Disclaimer Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications. ## Page 2: Topics {#source-page-002} - Objectives - Executing Dynamic Queries - Constructing Dynamic Commands ## Page 3: Dynamic SQL {#source-page-003} **The text of an SQL query is constructed at run time** - Objectives - provide additional flexibility for the application - construct several specific queries for optimization purposes - instead of using a single query that covers all possible cases - Trade-off - statements are not prepared - the risk of SQL injection rises - maintenance gets more complicated ### Speaker notes An SQL command is considered dynamic if its text is constructed and then executed within PL/pgSQL routine blocks or in anonymous blocks. In most cases, you can do without dynamic commands, but sometimes they can provide additional flexibility. For example, an application can have a built-in capability to execute commands provided via system settings. Instead of being hard-coded by developers, these settings can be tuned by the support team in production. When creating reports with a large number of optional parameters, it is sometimes easier to construct the text of a query at run time for the provided arguments only, instead of creating a complex query that includes all possible parameter combinations while developing the application. The price you pay for using dynamic commands is inability to take advantage of prepared statements, which are used in PL/pgSQL by default. Besides, you have to take care of dynamic commands’ security as they are vulnerable to SQL injection. We should also mention that maintenance gets more complicated. In particular, it will be impossible to scan the source code quickly for executable commands with tools like grep. ## Page 4: Query Execution {#source-page-004} **EXECUTE construct** - runs a text representation of an SQL query - allows using parameters - PL/pgSQL variables do not become implicit parameters - Can be used instead of an SQL query - independently - when opening a cursor - in a loop over a query - in the RETURN QUERY statement ### Speaker notes To run dynamic commands, PL/pgSQL uses the EXECUTE construct which launches the SQL command provided as a text string. A dynamic query can contain explicit parameters. In the command’s text representation, parameters are denoted by $1, $2, etc.; their actual values are provided in the USING clause. Parameters are handled in the same way as in prepared statements (which is covered in Architecture. PostgreSQL Fundamentals). However, PL/pgSQL variables do not become implicit parameters, as it happens in the case of regular (as opposed to dynamic) use of SQL in PL/pgSQL. The EXECUTE construct can be used as an independent statement (it will simply execute a dynamic command). It can also be used in loops over queries, when opening a cursor, or in the RETURN QUERY statement: in all these cases, EXECUTE replaces the SQL statement. Note that a procedure cannot perform transaction control if it is called by an EXECUTE construct. ## Page 5: Executing Dynamic Queries {#source-page-005} The EXECUTE statement runs SQL commands provided as text. ```sql => DO $$ DECLARE cmd CONSTANT text := 'CREATE TABLE city_london( name text, architect text, founded integer )'; BEGIN EXECUTE cmd; -- a table that lists examples of contemporary architecture in London END $$; ``` ```text DO ``` The INTO clause of the EXECUTE statement enables saving a single row of the result (the first returned row) into a variable of a composite type or into several scalar variables. As with static commands, you can check the result of a dynamic command using GET DIAGNOSTICS (but not the FOUND variable). ```sql => DO $$ DECLARE rec record; cnt bigint; BEGIN EXECUTE 'INSERT INTO city_london (name, architect, founded) VALUES (''The Shard'', ''Renzo Piano'', 2009), (''The Scalpel'', ''Kohn Pedersen Fox'', 2018), (''London Aquatics Centre'', ''Zaha Hadid'', 2011), (''30 St Mary Axe'', ''Norman Foster'', 2001), (''London City Hall'', ''Norman Foster'', 2000) RETURNING name, architect, founded' INTO rec; RAISE NOTICE '%', rec; GET DIAGNOSTICS cnt := ROW_COUNT; RAISE NOTICE 'Added rows: %', cnt; END $$; ``` ```text NOTICE: ("The Shard","Renzo Piano",2009) NOTICE: Added rows: 5 DO ``` You can use STRICT to ensure that the command processes only one row. The result of a dynamic query can be processed in a FOR loop. ```sql => DO $$ DECLARE rec record; BEGIN FOR rec IN EXECUTE 'SELECT * FROM city_london WHERE architect = ''Norman Foster'' ORDER BY founded' LOOP RAISE NOTICE '%', rec; END LOOP; END $$; ``` ```text NOTICE: ("London City Hall","Norman Foster",2000) NOTICE: ("30 St Mary Axe","Norman Foster",2001) DO ``` Here is the same example using a cursor: ## Page 6: Page 6 {#source-page-006} ```sql => DO $$ DECLARE cur refcursor; rec record; BEGIN OPEN cur FOR EXECUTE 'SELECT * FROM city_london WHERE architect = ''Norman Foster'' ORDER BY founded'; LOOP FETCH cur INTO rec; EXIT WHEN NOT FOUND; RAISE NOTICE '%', rec; END LOOP; END ``` ```text $$; ``` ```text NOTICE: ("London City Hall","Norman Foster",2000) NOTICE: ("30 St Mary Axe","Norman Foster",2001) DO ``` The RETURN QUERY statement can also use dynamic queries to return rows from functions. Let’s create a function that retrieves all buildings constructed by a particular architect and up to the specified year. We will have to use parameters for this purpose: ```sql => CREATE FUNCTION sel_london(architect text, founded integer DEFAULT NULL) RETURNS SETOF text AS $$ DECLARE -- parameters are numbered: $1, $2... cmd text := ' SELECT name FROM city_london WHERE architect = $1 AND ($2 IS NULL OR founded <= $2)'; BEGIN RETURN QUERY EXECUTE cmd USING architect, founded; -- provide parameters in the order of their declaration END $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT * FROM sel_london('Norman Foster'); ``` ```text sel_london ------------------ 30 St Mary Axe London City Hall (2 rows) ``` ```sql => SELECT * FROM sel_london('Norman Foster', 2000); ``` ```text sel_london ------------------ London City Hall (1 row) ``` ## Page 7: Constructing Commands {#source-page-007} **Parameter values binding** - USING clause - guarantees protection against SQL injection - Escaping values - identifiers: format('%I'), quote\_ident - literals: format('%L'), quote\_literal, quote\_nullable - SQL injection is impossible if implemented correctly - Regular string functions - concatenation, etc. - risk of SQL injection! ### Speaker notes Using the EXECUTE construct makes sense if the command is constructed dynamically. The previous examples could also do without EXECUTE. Since the command is represented by a text string, it can be constructed using regular string functions that perform such operations as concatenation, etc. This should be done with great care as there is a risk of SQL injection. If the values are passed as parameters in the USING clause, SQL injection is technically impossible. However, it is not always possible to use parameters: you may have to concatenate specific parts of the query or insert a table name into the query. In this case, you should escape the values received from an unreliable source to protect your application against injections. Identifiers are generated by either the format function with the %l specifier or the quote\_ident function. These functions ensure that identifiers have valid names by double-quoting them and escaping special characters, if required. ```sql To insert literals into the command text, you can use either quote_literal and quote_nullable functions or the format function with the %L specifier. ``` ## Page 8: Dealing with SQL Injection {#source-page-008} Let’s rewrite the function returning buildings and add one more parameter: the name of the city. The idea is to allow this function to access tables only if their names start with city\_. ```sql => CREATE FUNCTION sel_city( city_code text, architect text, founded integer DEFAULT NULL ) RETURNS SETOF text AS $$ DECLARE cmd text := ' SELECT name FROM city_' || city_code || ' WHERE architect = $1 AND ($2 IS NULL OR founded < $2)'; BEGIN RAISE NOTICE '%', cmd; RETURN QUERY EXECUTE cmd USING architect, founded; END $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` The function works fine if its parameter values are “correct”: ```sql => SELECT * FROM sel_city('london', 'Renzo Piano'); ``` ```sql NOTICE: SELECT name FROM city_london WHERE architect = $1 AND ($2 IS NULL OR founded < $2) sel_city ----------- The Shard (1 row) ``` But a malicious user can pick a value that will change the syntactic structure of the query and enable unauthorized access to data: ```sql => SELECT * FROM sel_city('london WHERE false UNION ALL SELECT usename FROM pg_user UNION ALL SELECT name FROM city_london', ''); ``` ```sql NOTICE: SELECT name FROM city_london WHERE false UNION ALL SELECT usename FROM pg_user UNION ALL SELECT name FROM city_london WHERE architect = $1 AND ($2 IS NULL OR founded < $2) sel_city ---------- student postgres (2 rows) ``` When you are using prepared statements or dynamic commands with parameters, such a situation is technically impossible because the structure of the SQL query is locked while the statement is parsed. An expression will always remain an expression; it is impossible to convert it, say, into a table name. ### Constructing a Dynamic Command It is impossible to provide the names of objects (such as tables or columns) as parameters of the USING clause in a dynamic command. Such identifiers must be escaped, so that it is impossible to modify the query structure: ```sql => SELECT format('%I', 'foo'), format('%I', 'foo bar'), format('%I', 'foo"bar'); ``` ## Page 9: Page 9 {#source-page-009} ```text format | format | format --------+-----------+------------ foo | "foo bar" | "foo""bar" (1 row) ``` The following function does the same thing: ```sql => SELECT quote_ident('foo'), quote_ident('foo bar'), quote_ident('foo"bar'); ``` ```text quote_ident | quote_ident | quote_ident -------------+-------------+------------- foo | "foo bar" | "foo""bar" (1 row) ``` Here is an example of creating a table: ```sql => DO $$ DECLARE cmd CONSTANT text := 'CREATE TABLE %I( name text, architect text, founded integer )'; BEGIN EXECUTE format(cmd, 'city_paris'); -- a table for Paris EXECUTE format(cmd, 'city_milan'); -- a table for Milan END $$; ``` ```text DO ``` Instead of using parameters, you can insert literals into a string. It also requires escaping, but in a bit different way: ```sql => SELECT format('%L', 'foo bar'), format('%L', 'foo''bar'), format('%L', NULL); ``` ```text format | format | format -----------+------------+-------- 'foo bar' | 'foo''bar' | NULL (1 row) ``` The quote\_nullable function also does the same thing: ```sql => SELECT quote_nullable('foo bar'), quote_nullable('foo''bar'), quote_nullable(NULL); ``` ```text quote_nullable | quote_nullable | quote_nullable ----------------+----------------+---------------- 'foo bar' | 'foo''bar' | NULL (1 row) ``` The quote\_literal function is quite similar, but it does not convert NULL values into literals: ```sql => SELECT quote_literal(NULL); ``` ```text quote_literal --------------- (1 row) ``` As an example, let’s rewrite the function that returns the list of buildings of a particular city, so that it does not use any parameters, but still remains safe. ## Page 10: Page 10 {#source-page-010} ```sql => CREATE OR REPLACE FUNCTION sel_city( city_code text, architect text, founded integer DEFAULT NULL ) RETURNS SETOF text AS $$ DECLARE cmd text := ' SELECT name FROM %I WHERE architect = %L AND (%L IS NULL OR founded < %L::integer)'; BEGIN ``` ```text RETURN QUERY EXECUTE format( cmd, 'city_'||city_code, architect, founded, founded ); END $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` > [!NOTE] > **Note** > Note that we perform two extra type casting operations: first, the integer parameter is converted into a string, and then it is cast back to integer at run time (this can be avoided with USING parameters): ```sql => SELECT * FROM sel_city('london', 'Renzo Piano', 2009); ``` ```text sel_city ---------- (0 rows) ``` An attempt to pass an invalid value will not succeed: ```sql => SELECT * FROM sel_city('london WHERE false UNION ALL SELECT usename FROM pg_user UNION ALL SELECT name FROM city_london', ''); ``` ```sql NOTICE: identifier "city_london WHERE false UNION ALL SELECT usename FROM pg_user UNION ALL SELECT name FROM city_london" will be truncated to "city_london WHERE false UNION ALL SELECT usenam" ERROR: relation "city_london WHERE false UNION ALL SELECT usenam" does not exist LINE 2: SELECT name FROM "city_london WHERE false ^ QUERY: SELECT name FROM "city_london WHERE false UNION ALL SELECT usename FROM pg_user UNION ALL SELECT name FROM city_london" WHERE architect = '' AND (NULL IS NULL OR founded < NULL::integer) CONTEXT: PL/pgSQL function sel_city(text,text,integer) line 7 at RETURN QUERY ``` ## Page 11: Takeaways {#source-page-011} **Dynamic commands provide additional flexibility** - Constructing separate queries for different arguments can improve performance - Dynamic commands are not suitable for short, frequent queries - Maintenance gets more complicated ## Page 12: Practice {#source-page-012} 1. Modify the get\_catalog function so that the query to the catalog\_v view is constructed dynamically and takes into account only those fields that are filled out in the search form of the Store tab. Make sure that your implementation is protected against SQL injection. Check your function in the application. ### Speaker notes 1. Suppose we have to generate the following query if these conditions are met: the “In stock” option is selected in the search form, but “Book Title” and “Author” fields are empty. ```sql SELECT ... FROM catalog_v WHERE onhand_qty > 0; ``` You should keep in mind that this implementation will not necessarily speed up search, but it will certainly be harder to maintain. Avoid such solutions in production environments unless you have a solid reason to use this technique. To learn more about query performance tuning, check out the QPT course. ## Page 13: 1. The get_catalog Function {#source-page-013} ```sql => CREATE OR REPLACE FUNCTION get_catalog( author_name text, book_title text, in_stock boolean ) RETURNS TABLE(book_id integer, display_name text, onhand_qty integer) AS $$ DECLARE title_cond text := ''; author_cond text := ''; qty_cond text := ''; cmd text; BEGIN IF book_title != '' THEN title_cond := format( ' AND cv.title ILIKE %L', '%'||book_title||'%' ); END IF; IF author_name != '' THEN author_cond := format( ' AND cv.authors ILIKE %L', '%'||author_name||'%' ); END IF; IF in_stock THEN qty_cond := ' AND cv.onhand_qty > 0'; END IF; cmd := 'SELECT cv.book_id, cv.display_name, cv.onhand_qty FROM catalog_v cv WHERE true' || title_cond || author_cond || qty_cond || ' ORDER BY display_name'; RAISE NOTICE '%', cmd; RETURN QUERY EXECUTE cmd; END $$ STABLE LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ## Page 14: Practice+ {#source-page-014} 1. Create a function that returns a matrix report on functions available in the database. - The columns must contain names of function owners, the rows - must provide schema names, while the cells must display the - number of functions that belong to a particular owner in a - particular schema. - What statement should you write to invoke this function? ### Speaker notes 1. Here is a possible output: ```text schema total postgres student ... information_schema 12 12 0 pg_catalog 2811 2811 0 public 3 0 3 ... ``` The number of columns returned by a query is unknown beforehand. You need to construct a query and then execute it dynamically. The query text can be as follows: ```sql SELECT pronamespace::regnamespace::text AS schema, COUNT(*) AS total ,SUM(CASE WHEN proowner = 10 THEN 1 ELSE 0 END) postgres ,SUM(CASE WHEN proowner = 16384 THEN 1 ELSE 0 END) student FROM pg_proc GROUP BY pronamespace::regnamespace ORDER BY schema ``` The highlighted lines are a dynamic part that has to be constructed by a separate query. The start and the end of the query are static. The proowner column has the oid type. To get the name of the owner, you can use the following construct: proowner::regrole::text. ## Page 15: Building a Matrix Report {#source-page-015} ```sql => CREATE DATABASE plpgsql_dynamic; ``` ```sql CREATE DATABASE ``` ```sql => \c plpgsql_dynamic ``` ```text You are now connected to database "plpgsql_dynamic" as user "student". ``` A function for generating dynamic query text: ```sql => CREATE FUNCTION form_query() RETURNS text AS $$ DECLARE query_text text; columns text := ''; r record; BEGIN -- Static part of the query. -- First two columns: schema name and number of functions query_text := $query$ SELECT pronamespace::regnamespace::text AS schema , count(*) AS total{{columns}} FROM pg_proc GROUP BY pronamespace::regnamespace ORDER BY schema $query$; ``` ```sql -- Dynamic part of the query. -- Get a list of function owners, a separate column for each FOR r IN SELECT DISTINCT proowner AS owner FROM pg_proc ORDER BY 1 LOOP columns := columns || format( E'\n , sum(CASE WHEN proowner = %s THEN 1 ELSE 0 END) AS %I', r.owner, r.owner::regrole ); END LOOP; ``` ```text RETURN replace(query_text, '{{columns}}', columns); END $$ STABLE LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` The resulting query text: ```sql => SELECT form_query(); ``` ```sql form_query --------------------------------------------------------------------- + SELECT pronamespace::regnamespace::text AS schema + , count(*) AS total + , sum(CASE WHEN proowner = 10 THEN 1 ELSE 0 END) AS postgres + , sum(CASE WHEN proowner = 16384 THEN 1 ELSE 0 END) AS student+ FROM pg_proc + GROUP BY pronamespace::regnamespace + ORDER BY schema + (1 row) ``` Now create a matrix report function: ```sql => CREATE FUNCTION matrix() RETURNS SETOF record AS $$ BEGIN RETURN QUERY EXECUTE form_query(); END $$ STABLE LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` Simply executing the query will result in an error, as the structure of the returned records is not specified: ## Page 16: Page 16 {#source-page-016} ```sql => SELECT * FROM matrix(); ``` ```sql ERROR: a column definition list is required for functions returning "record" LINE 1: SELECT * FROM matrix(); ^ ``` This is an important limitation on using functions that return an arbitrary set of values. You need to know and specify the structure of the record to be returned as the function is being called. In general, the returned record structure may be unknown, but for our matrix report, we can run another query that will show you how to call the matrix function correctly. Prepare the query text: ```sql => CREATE FUNCTION matrix_call() RETURNS text AS $$ DECLARE cmd text; r record; BEGIN cmd := 'SELECT * FROM matrix() AS ( schema text, total bigint'; ``` ```sql FOR r IN SELECT DISTINCT proowner AS owner FROM pg_proc ORDER BY 1 LOOP cmd := cmd || format(', %I bigint', r.owner::regrole::text); END LOOP; cmd := cmd || E'\n)'; ``` ```text RAISE NOTICE '%', cmd; RETURN cmd; END $$ STABLE LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` Now we can use matrix\_call to get a query that reflects the structure of the matrix report, and then execute this query and get the report (psql allows you to do all this with single \\\\gexec command): ```text => BEGIN ISOLATION LEVEL REPEATABLE READ; ``` ```text BEGIN ``` ```sql => SELECT matrix_call() \gexec ``` ```sql NOTICE: SELECT * FROM matrix() AS ( schema text, total bigint, postgres bigint, student bigint ) schema | total | postgres | student --------------------+-------+----------+--------- information_schema | 11 | 11 | 0 pg_catalog | 3286 | 3286 | 0 public | 3 | 0 | 3 (3 rows) ``` ```text => COMMIT; ``` ```text COMMIT ``` The matrix report is generated correctly. Repeatable Read isolation level guarantees that the report will be generated, even if a function with a new owner will come up between two queries. The query returned by form\_query could have been run manually. However, you still need to return the list of columns to the client. The matrix\_call function demonstrates how to solve this with an extra query. Another solution is to return a set of strings of a semi-structured type (such as JSON or XML) instead of an arbitrary structured set. These types are covered in the DEV2 course. ```sql => \c postgres ``` ```text You are now connected to database "postgres" as user "student". ``` ```sql => DROP DATABASE plpgsql_dynamic; ``` ```sql DROP DATABASE ```