40.12. Советы по разработке в PL/pgSQL#
40.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 - использовать инструмент доступа к базе данных с графическим интерфейсом, который облегчает разработку на процедурном языке. Один из примеров такого инструмента - pgAdmin, хотя существуют и другие. Эти инструменты часто предоставляют удобные функции, такие как экранирование апострофов и упрощение воссоздания и отладки функций.
40.12.1. Обработка кавычек #
Код функции PL/pgSQL указывается в команде CREATE FUNCTION в виде строкового литерала. Если вы записываете строковый литерал обычным способом с окружающими апострофами, то любые апострофы внутри тела функции должны быть удвоены; аналогично, любые обратные косые черты должны быть удвоены (если используется синтаксис экранирования строк).
Удвоение кавычек является, в лучшем случае, утомительным, а в более сложных случаях код может стать совершенно непонятным, потому что вам может понадобиться полдюжины или более соседних кавычек.
Рекомендуется вместо этого записывать тело функции в виде строкового литерала с использованием “dollar-quoted” (см. Раздел 4.1.2.4). При использовании dollar-quoted подхода вы никогда не удваиваете кавычки, а вместо этого следите за тем, чтобы выбрать разные разделители для каждого уровня вложенности, который вам нужен. Например, вы можете записать команду CREATE FUNCTION следующим образом:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
....
$PROC$ LANGUAGE plpgsql;
Внутри этого вы можете использовать кавычки для простых литеральных строк в SQL-командах и $$ для ограничения фрагментов SQL-команд, которые вы собираете в виде строк. Если вам нужно заключить в кавычки текст, который содержит $$, вы можете использовать $Q$ и так далее.
Следующая диаграмма показывает, что нужно делать при написании кавычек без использования долларовой кавычки. Это может быть полезно при переводе кода без использования долларовой кавычки в более понятный вид.
- 1 quotation mark #
Для начала и окончания тела функции, например:
CREATE FUNCTION foo() RETURNS integer AS ' .... ' LANGUAGE plpgsql;Где бы ни находились одиночные кавычки внутри тела функции, кавычки должны быть в парах.
- 2 quotation marks #
Для строковых литералов внутри тела функции, например:
a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar'';
В подходе с использованием долларовых кавычек вы просто напишете:
a_output := 'Blah'; SELECT * FROM users WHERE f_name='foobar';
что именно увидит парсер PL/pgSQL в обоих случаях.
- 4 quotation marks #
Когда вам нужен апостроф в строковой константе внутри тела функции, например:
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$$
будьте осторожны, чтобы любые разделители dollar-quote вокруг этого не были просто
$$.- 6 quotation marks #
Когда апостроф внутри строки в теле функции находится рядом с концом этой строковой константы, например:
a_output := a_output || '' AND name LIKE ''''foobar''''''
Значение, добавленное к
a_output, будет следующим:AND name LIKE 'foobar'.В подходе с использованием долларовых кавычек это становится:
a_output := a_output || $$ AND name LIKE 'foobar'$$
- 10 quotation marks #
Когда вам нужно две одиночные кавычки в строковой константе (что составляет 8 кавычек) и это находится рядом с концом этой строковой константы (еще 2). Вам, вероятно, понадобится это только в том случае, если вы пишете функцию, которая генерирует другие функции, как в Пример 40.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, потому что они будут повторно обрамлены кавычками перед использованием.
40.12.2. Дополнительные проверки времени компиляции и времени выполнения #
Для помощи пользователю в обнаружении простых, но распространенных проблем до того, как они причинят вред, 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)