PL/pgSQL

Динамические команды

Авторские права

Авторы: Егор Рогов, Павел Лузанов

Использование материалов курса

Некоммерческое использование материалов курса (презентации,

демонстрации) разрешается без ограничений. Коммерческое

использование возможно только с письменного разрешения компании

Postgres Professional. Запрещается внесение изменений в материалы

курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу:

edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за

любые повреждения и убытки, включая потерю дохода, нанесенные

прямым или непрямым, специальным или случайным использованием

материалов курса. Компания Postgres Professional не предоставляет

каких-либо гарантий на материалы курса. Материалы курса

предоставляются на основе принципа «как есть» и компания Postgres

Professional не обязана предоставлять сопровождение, поддержку,

обновления, расширения и изменения.

Темы

Причины использования

Выполнение динамического запроса

Способы формирования динамического запроса

Динамический SQL

Текст SQL-команды формируется в момент выполнения

Причины использования

дополнительная гибкость в приложении

формирование нескольких конкретных запросов

вместо одного универсального для оптимизации

Цена

операторы не подготавливаются

возрастает риск внедрения SQL-кода

возрастает сложность сопровождения

Под динамическими командами понимаются команды SQL, текст

которых формируется и затем выполняется внутри PL/pgSQL-блока

в подпрограммах или в анонимных блоках.

В большинстве случаев можно обойтись без динамических команд,

но иногда они могут предоставить дополнительную гибкость. Например,

можно встроить в определенные места приложения возможность

выполнять команды, считанные из настроек системы. Управлять такими

настройками могут специалисты поддержки во время эксплуатации

приложения, а не программисты в момент разработки.

Иногда, при формировании отчета с большим количеством

необязательных параметров, бывает проще формировать текст запроса

прямо во время выполнения только для указанных параметров, чем

заранее, при разработке, писать сложный запрос, учитывающий все

возможные комбинации параметров.

Ценой за использование динамических команд будет отказ от

подготовленных операторов, которые по умолчанию используются

в PL/pgSQL. Также нужно следить за безопасностью кода динамических

команд с точки зрения возможности внедрения SQL-кода.

Следует отметить и существенное возрастание сложности

сопровождения. В том числе в исходном коде приложения невозможно

будет текстовым поиском обнаружить все выполняемые команды.

Оператор EXECUTE

выполняет строковое представление SQL-запроса

позволяет использовать параметры

переменные PL/pgSQL не становятся неявными параметрами

Может использоваться вместо SQL-запроса

сам по себе

при открытии курсора

в цикле по запросу

в предложении RETURN QUERY

Выполнение запроса

Для выполнения динамических команд в PL/pgSQL используется

команда EXECUTE, которая выполняет SQL-оператор, заданный в виде

текстовой строки.

Динамический запрос может содержать явно заданные параметры.

В тексте команды параметры обозначаются как $1, $2 и т. д., а значения

параметров указываются в предложении USING. Параметры работают

точно так же, как в подготовленных операторах (что рассматривалось

в теме «Архитектура. Общее устройство PostgreSQL»). Однако

переменные PL/pgSQL не становятся неявными параметрами, как это

происходит при обычном, не динамическом, использовании SQL

в PL/pgSQL.

Оператор EXECUTE может использоваться как самостоятельный

оператор (в этом случае просто выполняется динамическая команда).

Он также может использоваться в циклах по запросу, при открытии

курсора, в команде RETURN QUERY — во всех этих случаях EXECUTE

заменяет собой оператор SQL.

https://postgrespro.ru/docs/postgresql/12/plpgsql-statements#PLPGSQL-ST

ATEMENTS-EXECUTING-DYN

Тонкий момент: процедура не может управлять транзакциями, если она

вызывается оператором EXECUTE.

Формирование команды

Подстановка значений параметров

предложение USING

гарантируется невозможность внедрения SQL-кода

Экранирование значений

идентификаторы: format('%I'), quote_ident

литералы: format('%L'), quote_literal, quote_nullable

внедрение SQL-кода невозможно при правильном использовании

Обычные строковые функции

конкатенация и др.

возможно внедрение SQL-кода!

Использование оператора EXECUTE имеет смысл, если команда

формируется динамически. Приведенные ранее примеры можно было

бы записать и без оператора EXECUTE.

Поскольку команда представляется текстовой строкой, ее можно

сформировать конкатенацией и другими строковыми функциями.

В этом случае надо проявлять крайнюю осторожность, поскольку

возможно внедрение SQL-кода.

Внедрение SQL-кода невозможно в принципе, если значения

передаются как параметры с помощью предложения USING.

Однако параметры применимы не всегда: может понадобиться

конкатенировать отдельные части запроса или подставлять в запрос

имя таблицы. В этом случае для защиты от внедрений следует

экранировать значения, полученные из ненадежного источника.

Для идентификаторов используется функция format со спецификатором

%I или функция quote_ident. Эти функции формируют правильные

имена идентификаторов, при необходимости заключая их в двойные

кавычки и экранируя специальные символы.

Для подстановки литералов внутрь текста команды можно

использовать функции quote_literal, quote_nullable или функцию format

со спецификатором %L.

https://postgrespro.ru/docs/postgresql/12/functions-string

Итоги

Динамические команды дают дополнительную гибкость

Формирование отдельных запросов для разных значений

параметров с целью оптимизации

Не подходят для коротких, частых запросов

Увеличивается сложность поддержки

Практика

1. Измените функцию get_catalog так, чтобы запрос

к представлению catalog_v формировался динамически

и содержал условия только на те поля, которые заполнены

на форме поиска в «Магазине».

Убедитесь, что реализация не допускает возможности

внедрения SQL-кода.

Проверьте работу функции в приложении.

1. Например, если на форме поиска не заполнены поля «Название

книги» и «Имя автора», но установлен флажок «Есть на складе»,

должен быть сформирован запрос вида:

SELECT ... FROM catalog_v WHERE onhand_qty > 0;

Учтите, что поиск при такой реализации вовсе не обязательно будет

работать эффективнее, но поддерживать его совершенно точно будет

сложнее. Поэтому в реальной работе не стоит прибегать к такому

приему, если для того нет веских оснований. Тема оптимизации

запросов рассматривается в курсе QPT.

Практика

1. Создайте функцию, которая возвращает строки матричного

отчета по функциям в базе данных.

Столбцы должны содержать имена владельцев функций,

строки — названия схем, а ячейки — количество функций

данного владельца в данной схеме.

Как можно вызвать такую функцию?

1. Примерный вид результата:

schema total postgres student ...

information_schema 12 12 0

pg_catalog 2811 2811 0

public 3 0 3

...

Количество столбцов в запросе заранее не известно. Поэтому

необходимо сконструировать запрос и затем динамически его

выполнить. Текст запроса может быть таким:

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

Выделенные строки — динамическая часть — необходимо

сформировать дополнительным запросом. Начало и конец запроса —

статические.

Столбец proowner имеет тип oid, для получения имени владельца

можно воспользоваться конструкцией proowner::regrole::text.