41.12. Советы по разработке на PL/pgSQL #
Хороший способ разрабатывать на PL/pgSQL заключается в том, чтобы в одном окне с текстовым редактором по выбору создавать тексты функций, а в другом окне с psql загружать и тестировать эти функции. В таком случае удобно записывать функцию, используя CREATE OR REPLACE FUNCTION
. Таким образом, можно легко загрузить файл для обновления определения функции. Например:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$ .... $$ LANGUAGE plpgsql;
В psql, можно загрузить или перезагрузить такой файл определения функции, выполнив:
\i filename.sql
а затем сразу выполнять команды SQL для тестирования функции.
Ещё один хороший способ разрабатывать на PL/pgSQL связан с использованием GUI инструментов, облегчающих разработку на процедурном языке. Один из примеров такого инструмента pgAdmin, хотя есть и другие. Такие инструменты часто предоставляют удобные возможности, такие как экранирование одинарных кавычек, отладка и повторное создание функций.
41.12.1. Обработка кавычек #
Код функции на PL/pgSQL указывается в команде CREATE FUNCTION
в виде строки. Если записывать строку как обычно, внутри одинарных кавычек, то любой символ одинарной кавычки должен дублироваться, так же как и должен дублироваться каждый знак обратной косой черты (если используется синтаксис с экранированием в строках). Дублирование кавычек в лучшем случае утомительно, а в более сложных случаях код может стать совершенно непонятным, так как легко может потребоваться четыре или более идущих подряд кавычек. Вместо этого при создании тела функции рекомендуется использовать знаки доллара в качестве кавычек (см. Подраздел 4.1.2.4). При таком подходе никогда не потребуется дублировать кавычки, но придётся позаботиться о том, чтобы иметь разные долларовые разделители для каждого уровня вложенности. Например, команду CREATE FUNCTION
можно записать так:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$ .... $PROC$ LANGUAGE plpgsql;
Внутри можно использовать кавычки для простых текстовых строк и $$
для разграничения фрагментов SQL-команды, собираемой из отдельных строк. Если нужно взять в кавычки текст, который включает $$
, можно использовать $Q$
, и так далее.
Следующая таблица показывает, как применяются знаки кавычек, если не используется экранирование долларами. Это может быть полезно при переводе кода, не использующего экранирование знаками доллара, в нечто более понятное.
- 1 кавычка #
В начале и конце тела функции, например:
CREATE FUNCTION foo() RETURNS integer AS ' .... ' LANGUAGE plpgsql;
Внутри такой функции любая кавычка должна дублироваться.
- 2 кавычки #
Для строковых литералов внутри тела функции, например:
a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar'';
При использовании знаков доллара можно просто написать:
a_output := 'Blah'; SELECT * FROM users WHERE f_name='foobar';
и именно это увидит исполнитель PL/pgSQL в обоих случаях.
- 4 кавычки #
Когда нужны одинарные кавычки в строковой константе внутри тела функции, например:
a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
К
a_output
будет добавлено:AND name LIKE 'foobar' AND xyz
При использовании знаков доллара это записывается так:
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
будьте внимательны, при этом не должно быть внешнего долларового разделителя
$$
.- 6 кавычек #
Когда нужны одинарные кавычки в строковой константе внутри тела функции, при этом кавычки находятся в конце строковой константы. Например:
a_output := a_output || '' AND name LIKE ''''foobar''''''
К
a_output
будет добавлено:AND name LIKE 'foobar'
.При использовании знаков доллара это записывается так:
a_output := a_output || $$ AND name LIKE 'foobar'$$
- 10 кавычек #
Когда нужны две одиночные кавычки в строковой константе (это уже 8 кавычек), примыкающие к концу строковой константы (ещё 2). Вероятно, такое может понадобиться при разработке функции, которая генерирует другие функции, как показано в Примере 41.10. Например:
a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' || referrer_keys.key_string || '''''''''' then return '''''' || referrer_keys.referrer_type || ''''''; end if;'';
Значение
a_output
затем будет:if v_... like ''...'' then return ''...''; end if;
При использовании знаков доллара:
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ || referrer_keys.key_string || $$' then return '$$ || referrer_keys.referrer_type || $$'; end if;$$;
где предполагается, что нужны только одиночные кавычки в
a_output
, так как потребуется повторное взятие в кавычки перед использованием.
41.12.2. Дополнительные проверки во время компиляции и во время выполнения #
Чтобы помочь найти и предупредить простые, но часто встречающиеся проблемы, PL/PgSQL предоставляет дополнительные проверки
. Если они включены в конфигурации, то во время компиляции функций будут выдаваться дополнительные сообщения WARNING
или ошибки ERROR
. Функция, при компиляции которой выдавалось WARNING
, при последующем выполнении не будет выдавать это сообщение и её можно протестировать в отдельной среде разработки.
В среде разработки и/или тестирования имеет смысл установить значение "all"
для параметра plpgsql.extra_warnings
или plpgsql.extra_errors
.
Для включения этих проверок предназначены параметры plpgsql.extra_warnings
(для предупреждений) и plpgsql.extra_errors
(для ошибок). Каждому из параметров можно присвоить список значений, разделённых запятыми, значение "none"
или "all"
. По умолчанию используется "none"
. В настоящий момент доступны следующие дополнительные проверки:
shadowed_variables
#Проверяет, что объявление новой переменной не скрывает ранее объявленную переменную.
strict_multi_assignment
#Некоторые команды PL/pgSQL допускают присваивание значений сразу нескольким переменным, например
SELECT INTO
. Обычно количество целевых переменных должно совпадать с количеством исходных, хотя PL/pgSQL будет использоватьNULL
вместо пропущенных значений, а дополнительные переменные игнорировать. При включении этой проверки PL/pgSQL будет выдавать предупреждение (WARNING
) или ошибку (ERROR
) при несовпадении количества целевых переменных с количеством исходных.too_many_rows
#При включении этой проверки PL/pgSQL будет контролировать случаи, когда запрос с предложением
INTO
возвращает больше одной строки. ПредложениеINTO
может обработать только одну строку, поэтому запрос, возвращающий несколько строк, как правило оказывается неэффективным и/или недетерминированным, а следовательно, скорее всего, является ошибочным.
Следующий пример показывает эффект присваивания plpgsql.extra_warnings
значения shadowed_variables
:
SET plpgsql.extra_warnings TO 'shadowed_variables'; CREATE FUNCTION foo(f1 int) RETURNS int AS $$ DECLARE f1 int; BEGIN RETURN f1; END; $$ LANGUAGE plpgsql; WARNING: variable "f1" shadows a previously defined variable LINE 3: f1 int; ^ CREATE FUNCTION
Пример ниже показывает эффект присваивания plpgsql.extra_warnings
значения strict_multi_assignment
:
SET plpgsql.extra_warnings TO 'strict_multi_assignment'; CREATE OR REPLACE FUNCTION public.foo() RETURNS void LANGUAGE plpgsql AS $$ DECLARE x int; y int; BEGIN SELECT 1 INTO x, y; SELECT 1, 2 INTO x, y; SELECT 1, 2, 3 INTO x, y; END; $$; SELECT foo(); WARNING: number of source and target fields in assignment does not match DETAIL: strict_multi_assignment check of extra_warnings is active. HINT: Make sure the query returns the exact list of columns. WARNING: number of source and target fields in assignment does not match DETAIL: strict_multi_assignment check of extra_warnings is active. HINT: Make sure the query returns the exact list of columns. foo ----- (1 row)