PL/pgSQL
Динамические команды
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Причины использования
Выполнение динамического запроса
Способы формирования динамического запроса
3
Динамический SQL
Текст SQL-команды формируется в момент выполнения
Причины использования
дополнительная гибкость в приложении
формирование нескольких конкретных запросов
вместо одного универсального для оптимизации
Цена
операторы не подготавливаются
возрастает риск внедрения SQL-кода
возрастает сложность сопровождения
Под динамическими командами понимаются команды SQL, текст
которых формируется и затем выполняется внутри PL/pgSQL-блока
в подпрограммах или в анонимных блоках.
В большинстве случаев можно обойтись без динамических команд,
но иногда они могут предоставить дополнительную гибкость. Например,
можно встроить в определенные места приложения возможность
выполнять команды, считанные из настроек системы. Управлять такими
настройками могут специалисты поддержки во время эксплуатации
приложения, а не программисты в момент разработки.
Иногда, при формировании отчета с большим количеством
необязательных параметров, бывает проще формировать текст запроса
прямо во время выполнения только для указанных параметров, чем
заранее, при разработке, писать сложный запрос, учитывающий все
возможные комбинации параметров.
Платой за использование динамических команд будет отказ
от подготовленных операторов, которые по умолчанию используются
в PL/pgSQL. Также нужно следить за безопасностью динамических
команд с точки зрения возможности внедрения SQL-кода.
Следует отметить и существенное возрастание сложности
сопровождения. В частности, в исходном коде приложения невозможно
будет текстовым поиском обнаружить все выполняемые команды.
4
Оператор 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.
Тонкий момент: процедура не может управлять транзакциями, если она
вызывается оператором EXECUTE.
6
Формирование команды
Подстановка значений параметров
предложение 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.
8
Итоги
Динамические команды дают дополнительную гибкость
Позволяют формировать отдельные запросы для разных
значений параметров с целью оптимизации
Не подходят для коротких, частых запросов
Увеличивается сложность поддержки
9
Практика
1. Измените функцию get_catalog так, чтобы запрос
к представлению catalog_v формировался динамически
и содержал условия только на те поля, которые заполнены
на форме поиска в «Магазине».
Убедитесь, что реализация не допускает возможности
внедрения SQL-кода.
Проверьте работу функции в приложении.
1. Скажем, если на форме поиска не заполнены поля «Название книги»
и «Имя автора», но установлен флажок «Есть на складе», должен быть
сформирован такой, например, запрос:
SELECT ... FROM catalog_v WHERE onhand_qty > 0;
Учтите, что поиск при такой реализации вовсе не обязательно будет
работать эффективнее, но поддерживать его совершенно точно будет
сложнее. Поэтому в реальной работе не стоит прибегать к такому
приему, если для того нет веских оснований. Тема оптимизации
запросов рассматривается в курсе QPT.
10
Практика+
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.
