Postgres Pro Enterprise : Документация: 15: 45.13. Советы по разработке на PL/pgSQL : Компания Postgres Professional

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

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

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

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

45.13.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)

Пред.	Наверх	След.
45.12. PL/pgSQL изнутри	Начало	45.14. Портирование из Oracle PL/SQL

45.13. Tips for Developing in PL/pgSQL
Prev	Up	Chapter 45. PL/pgSQL — SQL Procedural Language	Home	Next

45.13. Tips for Developing in PL/pgSQL

45.13.1. Handling of Quotation Marks
45.13.2. Additional Compile-Time and Run-Time Checks

One good way to develop in PL/pgSQL is to use the text editor of your choice to create your functions, and in another window, use psql to load and test those functions. If you are doing it this way, it is a good idea to write the function using CREATE OR REPLACE FUNCTION. That way you can just reload the file to update the function definition. For example:

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

While running psql, you can load or reload such a function definition file with:

\i filename.sql

and then immediately issue SQL commands to test the function.

Another good way to develop in PL/pgSQL is with a GUI database access tool that facilitates development in a procedural language. One example of such a tool is pgAdmin, although others exist. These tools often provide convenient features such as escaping single quotes and making it easier to recreate and debug functions.

45.13.1. Handling of Quotation Marks

The code of a PL/pgSQL function is specified in CREATE FUNCTION as a string literal. If you write the string literal in the ordinary way with surrounding single quotes, then any single quotes inside the function body must be doubled; likewise any backslashes must be doubled (assuming escape string syntax is used). Doubling quotes is at best tedious, and in more complicated cases the code can become downright incomprehensible, because you can easily find yourself needing half a dozen or more adjacent quote marks. It's recommended that you instead write the function body as a “dollar-quoted” string literal (see Section 4.1.2.4). In the dollar-quoting approach, you never double any quote marks, but instead take care to choose a different dollar-quoting delimiter for each level of nesting you need. For example, you might write the CREATE FUNCTION command as:

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

Within this, you might use quote marks for simple literal strings in SQL commands and $$ to delimit fragments of SQL commands that you are assembling as strings. If you need to quote text that includes $$, you could use $Q$ , and so on.

The following chart shows what you have to do when writing quote marks without dollar quoting. It might be useful when translating pre-dollar quoting code into something more comprehensible.

1 quotation mark

To begin and end the function body, for example:

CREATE FUNCTION foo() RETURNS integer AS '
          ....
' LANGUAGE plpgsql;

Anywhere within a single-quoted function body, quote marks must appear in pairs.

2 quotation marks

For string literals inside the function body, for example:

a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';

In the dollar-quoting approach, you'd just write:

a_output := 'Blah';
SELECT * FROM users WHERE f_name='foobar';

which is exactly what the PL/pgSQL parser would see in either case.

4 quotation marks

When you need a single quotation mark in a string constant inside the function body, for example:

a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''

The value actually appended to a_output would be: AND name LIKE 'foobar' AND xyz.

In the dollar-quoting approach, you'd write:

a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$

being careful that any dollar-quote delimiters around this are not just $$.

6 quotation marks

When a single quotation mark in a string inside the function body is adjacent to the end of that string constant, for example:

a_output := a_output || '' AND name LIKE ''''foobar''''''

The value appended to a_output would then be: AND name LIKE 'foobar'.

In the dollar-quoting approach, this becomes:

a_output := a_output || $$ AND name LIKE 'foobar'$$

10 quotation marks

When you want two single quotation marks in a string constant (which accounts for 8 quotation marks) and this is adjacent to the end of that string constant (2 more). You will probably only need that if you are writing a function that generates other functions, as in Example 45.10. For example:

a_output := a_output || '' if v_'' ||
    referrer_keys.kind || '' like ''''''''''
    || referrer_keys.key_string || ''''''''''
    then return ''''''  || referrer_keys.referrer_type
    || ''''''; end if;'';

The value of a_output would then be:

if v_... like ''...'' then return ''...''; end if;

In the dollar-quoting approach, this becomes:

a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
    || referrer_keys.key_string || $$'
    then return '$$  || referrer_keys.referrer_type
    || $$'; end if;$$;

where we assume we only need to put single quote marks into a_output, because it will be re-quoted before use.

45.13.2. Additional Compile-Time and Run-Time Checks

To aid the user in finding instances of simple but common problems before they cause harm, PL/pgSQL provides additional checks. When enabled, depending on the configuration, they can be used to emit either a WARNING or an ERROR during the compilation of a function. A function which has received a WARNING can be executed without producing further messages, so you are advised to test in a separate development environment.

Setting plpgsql.extra_warnings, or plpgsql.extra_errors, as appropriate, to "all" is encouraged in development and/or testing environments.

These additional checks are enabled through the configuration variables plpgsql.extra_warnings for warnings and plpgsql.extra_errors for errors. Both can be set either to a comma-separated list of checks, "none" or "all". The default is "none". Currently the list of available checks includes:

shadowed_variables: Checks if a declaration shadows a previously defined variable.
strict_multi_assignment: Some PL/PgSQL commands allow assigning values to more than one variable at a time, such as SELECT INTO. Typically, the number of target variables and the number of source variables should match, though PL/PgSQL will use NULL for missing values and extra variables are ignored. Enabling this check will cause PL/PgSQL to throw a WARNING or ERROR whenever the number of target variables and the number of source variables are different.
too_many_rows: Enabling this check will cause PL/PgSQL to check if a given query returns more than one row when an INTO clause is used. As an INTO statement will only ever use one row, having a query return multiple rows is generally either inefficient and/or nondeterministic and therefore is likely an error.

The following example shows the effect of plpgsql.extra_warnings set to 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

The below example shows the effects of setting plpgsql.extra_warnings to 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)

Prev	Up	Next
45.12. PL/pgSQL under the Hood	Home	45.14. Porting from Oracle PL/SQL