43.12. Советы по разработке на PL/pgSQL
Пред. НаверхГлава 43. PL/pgSQL — процедурный язык SQLНачало След.

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

43.12.1. Обработка кавычек
43.12.2. Дополнительные проверки во время компиляции и во время выполнения

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

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

43.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)
Пред. Наверх След.
43.11. PL/pgSQL изнутри Начало 43.13. Портирование из Oracle PL/SQL