Советы по разработке на 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 заключается в применении инструмента доступа к базе данных с ГПИ, упрощающего разработку на процедурном языке. Одним из примеров такого инструмента является pgAdmin, хотя существуют и другие. Эти инструменты часто предоставляют удобные функции, например, экранирование апострофов и упрощение воссоздания и отладки функций.

Обработка кавычек

Код функции PL/pgSQL задается в CREATE FUNCTION как строковый литерал. Если вы пишете строковый литерал обычным способом, заключая его в апострофы, то все апострофы внутри тела функции следует дублировать; аналогичным образом нужно дублировать все обратные слэши (при условии использования синтаксиса с экранированием строки). Дублирование кавычек в лучшем случае утомительно, а в более сложных случаях код может стать совершенно непонятным, потому что нередко может потребоваться поставить пять или более кавычек подряд. Вместо этого рекомендуется написать тело функции в виде строкового литерала, заключенного в «долларовые кавычки» (см. подраздел Строковые константы, заключенные в знаки доллара). При подходе с заключением в знаки доллара кавычки не нужно дублировать; вместо этого следует выбирать разные долларовые разделители для каждого необходимого уровня вложенности. Например, можно написать команду CREATE FUNCTION как:

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
          ....
$PROC$ LANGUAGE plpgsql;

В рамках этого можно использовать кавычки для простых литеральных строк в командах SQL и $$ для разделения фрагментов команд 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). Вероятно, подобное может понадобиться только при написании функции, генерирующей другие функции, как в Примере 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, потому что она будет повторно взята в кавычки перед использованием.

Дополнительные проверки во время компиляции и во время выполнения

Чтобы помочь пользователю найти отдельные случаи простых, но распространенных проблем до того, как они причинят вред, PL/pgSQL предоставляет дополнительные проверки. Когда они включены (в зависимости от конфигурации) их можно использовать для выдачи сообщения WARNING или ERROR во время компиляции функции. Функция, получившая WARNING, может быть выполнена без выдачи дополнительных сообщений, поэтому рекомендуется тестировать ее в отдельной среде разработки.

При необходимости в средах разработки и/или тестирования рекомендуется установить plpgsql.extra_warnings или plpgsql.extra_errors в значение "all".

Эти дополнительные проверки включаются через переменные конфигурации 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
-- ПРЕДУПРЕЖДЕНИЕ: переменная "f1" скрывает переменную, определенную ранее
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.
-- ПРЕДУПРЕЖДЕНИЕ: количество исходных и целевых полей в assignment не совпадает
-- ДЕТАЛИЗАЦИЯ: проверка strict_multi_assignment в extra_warnings активна.
-- ПОДСКАЗКА: Убедитесь, что запрос возвращает точный список столбцов.

 foo
-----

(1 row)