Советы по разработке в PL/pgSQL

примечание

Эта страница переведена при помощи нейросети GigaChat.

Один хороший способ разработки на 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). Вероятно, это понадобится только в функции, которая генерирует другие функции, как в примере «Перенос функции, которая создает другую функцию из PL/SQL в PL/pgSQL». Например:

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