PL/pgSQL — процедурный язык SQL

Обзор

PL/pgSQL — это загружаемый процедурный язык для системы управления базами данных QHB. Цели разработки PL/pgSQL заключались в создании загружаемого процедурного языка, который

может быть использован для создания функций, процедур и триггеров,
добавляет структуры управления к языку SQL,
может выполнять сложные вычисления,
наследует все пользовательские типы, функции, процедуры и операторы,
может быть определен для сервера как доверенный язык,
прост в использовании.

Функции, созданные с помощью PL/pgSQL, можно использовать везде, где могут использоваться встроенные функции. Например, можно создавать сложные функции условного вычисления, а затем использовать их для определения операторов или в индексных выражениях.

В QHB PL/pgSQL устанавливается по умолчанию. Однако это все еще загружаемый модуль, поэтому администраторы, особенно заботящиеся о безопасности, могут при желании его удалить.

Преимущества использования PL/pgSQL

SQL — это язык QHB, и большинство других реляционных баз данных используют его в качестве языка запросов. Он переносим и легок в изучении. Но каждый оператор SQL должен выполняться сервером баз данных индивидуально.

Это означает, что ваше клиентское приложение должно отправлять каждый запрос на сервер баз данных, ждать его обработки, получать и обрабатывать результаты, выполнять некоторые вычисления, а затем отправлять дополнительные запросы на сервер. Все это требует межпроцессного взаимодействия, а также повлечет за собой нагрузку на сеть, если ваш клиент находится не на одном компьютере с сервером баз данных.

С PL/pgSQL вы можете сгруппировать блок вычислений и серию запросов внутри сервера баз данных, тем самым получая мощь процедурного языка и простоту использования SQL, но при этом значительно экономя накладные расходы на клиент- серверное взаимодействие.

Дополнительные обходы между клиентом и сервером исключены
Промежуточные результаты, которые не нужны клиенту, необязательно маршализовать или передавать между сервером и клиентом
Можно избежать нескольких циклов синтаксического анализа запроса

Это может дать значительное увеличение производительности по сравнению с приложением, которое не использует хранимые функции.

Кроме того, с PL/pgSQL можно использовать все типы данных, операторы и функции SQL.

Поддерживаемые типы данных аргумента и результата

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

С помощью маркера VARIADIC функции PL/pgSQL можно объявить как принимающие переменное число аргументов. Это работает точно так же, как и для функций SQL, как описано в разделе Функции SQL с переменным числом аргументов.

Также функции PL/pgSQL можно объявить как принимающие и возвращающие полиморфные типы, описанные в подразделе Полиморфные типы, вследствие чего фактические типы данных, обрабатываемые подобной функцией, могут варьироваться от вызова к вызову. Пример приводится в подразделе Объявление параметров функции.

Кроме того, функции PL/pgSQL можно объявить как возвращающие «множество» (или таблицу) любого типа данных, который может быть возвращен как один экземпляр. Такая функция генерирует свои выходные данные, выполняя RETURN NEXT для каждого желаемого элемента результирующего набора или используя RETURN QUERY для вывода результата вычисления запроса.

Наконец, можно объявить функцию PL/pgSQL как возвращающую void, если она не имеет полезного возвращаемого значения. (Как вариант, в этом случае ее можно написать в виде процедуры.)

Функции PL/pgSQL также могут быть объявлены с выходными параметрами вместо явной спецификации возвращаемого типа. Это не добавляет данному языку никаких фундаментальных возможностей, но часто бывает удобно, особенно для возврата нескольких значений. Кроме того, вместо RETURNS SETOF можно использовать нотацию RETURNS TABLE.

Конкретные примеры приведены в подразделах Объявление параметров функции и Возврат из функции.

Структура PL/pgSQL

Функции, написанные на PL/pgSQL, определяются на сервере путем выполнения команд CREATE FUNCTION. Такая команда обычно выглядит примерно так:

CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'текст тела функции'
LANGUAGE plpgsql;

Если рассматривать тело функции относительно CREATE FUNCTION, то это просто строковый литерал. Часто для написания тела функции полезно использовать знаки доллара (см. подраздел Строковые константы с экранированием знаками доллара), а не обычный синтаксис с апострофами. Без использования знаков доллара любые апострофы или обратные слэши в теле функции следует экранировать путем их удвоения. Почти во всех примерах в этой главе для тел функций используются литералы в долларовых кавычках.

PL/pgSQL — это блочно-структурированный язык. Полный текст тела функции должен быть блоком. Блок определяется как:

[ <<метка>> ]
[ DECLARE
    объявления ]
BEGIN
    операторы
END [ метка ];

Каждое объявление и каждый оператор в блоке заканчиваются точкой с запятой. Блок, находящийся внутри другого блока, должен иметь точку с запятой после END, как показано выше; однако последний END, завершающий тело функции, точки с запятой не требует.

Совет
Распространенная ошибка — писать точку с запятой сразу после BEGIN. Это неверно и приведет к синтаксической ошибке.

метка нужна только в том случае, если вы хотите идентифицировать блок для использования в операторе EXIT или указать имена переменных, объявленных в этом блоке. Если метка задается после END, она должна совпадать с меткой в начале блока.

Все ключевые слова нечувствительны к регистру. Идентификаторы неявно переводятся в нижний регистр, если они не заключены в кавычки, как в обычных командах SQL.

Комментарии в коде PL/pgSQL работают так же, как и в обычном SQL. Комментарий начинается после двойного дефиса (--) и продолжается до конца строки. Блочный комментарий начинается с символов /* и продолжается до зеркальных символов */. Блочные комментарии могут быть вложенными.

Любой оператор в разделе операторов блока может быть субблоком. Субблоки можно использовать для логической группировки или для локализации переменных в небольшой группе операторов. Переменные, объявленные в субблоке, скрывают на время его выполнения любые переменные внешних блоков с теми же именами; но ко внешним переменным все равно можно обратиться, если дополнить их имена меткой их блока. Например:

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Выводится 30
    quantity := 50;
    --
    -- Создать субблок
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Выводится 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Выводится 50
    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Выводится 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;

Примечание
На самом деле существует скрытый «внешний блок», окружающий тело любой функции PL/pgSQL. Этот блок предоставляет объявления параметров функции (если таковые имеются), а также некоторые специальные переменные, например FOUND (см. подраздел Получение статуса результата). Внешний блок помечен именем функции, подразумевая, что параметры и специальные переменные могут быть дополнены именем функции.

Важно не путать использование BEGIN/END для группировки операторов в PL/pgSQL с одноименными командами SQL для управления транзакциями. В PL/pgSQL BEGIN/END предназначены только для группировки; они не начинают и не заканчивают транзакцию. Информацию об управлении транзакциями в PL/pgSQL см. в разделе Управление транзакциями. Кроме того, блок, содержащий предложение EXCEPTION, по сути образует субтранзакцию, которую можно откатить, не затрагивая внешнюю транзакцию. Подробнее это описано в подразделе Перехват ошибок.

Объявления

Все переменные, используемые в блоке, должны быть объявлены в разделе объявлений этого блока. (Единственное исключение состоит в том, что переменная цикла FOR, повторяющегося по диапазону целочисленных значений, автоматически объявляется как целочисленная переменная, а переменная цикла FOR, повторяющегося по результату курсора, аналогично автоматически объявляется как переменная записи.)

Переменные PL/pgSQL могут иметь любой тип данных SQL, например, integer, varchar и char.

Несколько примеров объявлений переменных:

user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;

Общий синтаксис объявления переменной:

имя [ CONSTANT ] тип [ COLLATE имя_правила_сортировки ] [ NOT NULL ] [ { DEFAULT | := | = } выражение ];

Предложение DEFAULT, если таковое имеется, задает начальное значение, назначенное переменной при вводе блока. Если предложение DEFAULT не задано, переменная инициализируется SQL-значением NULL. Параметр CONSTANT предотвращает присвоение переменной после инициализации, чтобы ее значение оставалось постоянным на протяжении всего блока. Параметр COLLATE задает правило сортировки, которое будет применяться для переменной (см. подраздел Сортировка переменных PL/pgSQL). Если указано NOT NULL, то присвоение значения NULL вызовет ошибку во время выполнения. Все переменные, объявленные как NOT NULL должны иметь значение по умолчанию, отличное от NULL. Вместо PL/SQL-совместимого сочетания := можно использовать просто знак равенства (=).

Значение переменной по умолчанию вычисляется и присваивается переменной каждый раз, когда вводится блок (а не только один раз за вызов функции). Так, например, присвоение функции now() переменной типа timestamp приведет к тому, что переменная будет содержать время текущего вызова функции, а не время, когда функция была предварительно скомпилирована.

Примеры:

quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;

Объявление параметров функции

Параметры, передаваемые в функции, именуются с помощью идентификаторов $1, $2 и т. д. При желании с целью повышения удобочитаемости для $n имен параметров могут быть объявлены псевдонимы. Затем для обращения к значению параметра можно использовать псевдоним или числовой идентификатор.

Есть два способа создать псевдоним. Предпочтительным способом является присвоение имени параметру в команде CREATE FUNCTION, например:

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Другой способ — явно объявить псевдоним, используя следующий синтаксис объявления:

имя ALIAS FOR $n;

Тот же пример в этом стиле выглядит так:

CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Примечание
Эти два примера не вполне равнозначны. В первом случае на subtotal можно ссылаться как на sales_tax.subtotal, но во втором случае это невозможно. (Если бы мы прикрепили метку к внутреннему блоку, subtotal можно было бы дополнить этой меткой).

Еще несколько примеров:

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- ряд вычислений, использующих v_string и index
END;
$$ LANGUAGE plpgsql;


CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE plpgsql;

Когда функция PL/pgSQL объявляется с выходными параметрами, им даются имена $n и необязательные псевдонимы точно так же, как обычным входным параметрам. Выходной параметр по сути является переменной, которая начинается с NULL; значение ей должно присваиваться во время выполнения функции. Возвращается конечное значение этого параметра. Например, расчет налога с продаж также можно сделать следующим образом:

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Обратите внимание, что мы пропустили RETURNS real — мы могли бы включить его, но это было бы излишним.

Чтобы вызвать функцию с параметрами OUT, при ее вызове опустите выходные параметры:

SELECT sales_tax(100.00);

Выходные параметры наиболее полезны при возврате нескольких значений. Тривиальный пример:

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM sum_n_product(2, 4);
 sum | prod
-----+------
   6 |    8

Как было сказано в подразделе Функции SQL с выходными параметрами, это по сути создает анонимный тип записи для результатов функции. Если задано предложение RETURNS, оно должно выглядеть как RETURNS record.

Это работает и для процедур, например:

CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

При вызове процедуры следует задавать все параметры. Вызывая процедуру из простого SQL, вместо выходных параметров можно указать NULL:

CALL sum_n_product(2, 4, NULL, NULL);
 sum | prod
-----+------
   6 |    8

Однако при вызове процедуры из PL/pgSQL для каждого выходного параметра нужно написать переменную; эта переменная будет получать результат вызова. Подробную информацию см. в подразделе Вызов процедуры.

Другой способ объявить функцию PL/pgSQL — использовать RETURNS TABLE, например:

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

Это в точности эквивалентно объявлению одного или нескольких параметров OUT и указанию RETURNS SETOF некий_тип.

Когда возвращаемый тип функции PL/pgSQL объявлен как полиморфный (см. подраздел Полиморфные типы), создается специальный параметр $0. Его тип данных является фактическим типом возвращаемого значения функции, выведенным из фактических типов входных параметров. Это позволяет функции получить доступ к ее фактическому возвращаемому типу, как показано в подразделе Копирование типов. $0 инициализируется значением NULL и может быть изменен функцией, поэтому при желании его можно использовать для хранения возвращаемого значения, хотя это необязательно. $0 также можно присвоить псевдоним. Например, эта функция работает с любым типом данных, который имеет оператор +:

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

Тот же эффект можно получить, объявив один или несколько выходных параметров как имеющие полиморфный тип. В этом случае специальный параметр $0 не используется; той же цели служат сами выходные параметры. Например:

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
                                 OUT sum anyelement)
AS $$
BEGIN
    sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

На практике может быть более полезно объявить полиморфную функцию, используя семейство типов anycompatible, чтобы входные аргументы автоматически преобразовывались в общий тип. Например:

CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible)
RETURNS anycompatible AS $$
BEGIN
    RETURN v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

С функцией из этого примера сработает следующий вызов:

SELECT add_three_values(1, 2, 4.7);

автоматически преобразовав целочисленные аргументы в числовые. В функции, использующей anyelement, потребовалось бы приводить все три аргумента к одному вручную.

ALIAS

новое_имя ALIAS FOR старое_имя;

Синтаксис ALIAS является более общим, чем предполагается в предыдущем подразделе: псевдоним можно объявить для любой переменной, а не только для параметров функции. Основное практическое применение этого состоит в назначении другого имени для переменных с предопределенными именами, например, NEW или OLD в триггерной функции.

Примеры:

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

Поскольку ALIAS создает два разных способа именования одного объекта, неограниченное использование может привести к путанице. Лучше всего использовать его только с целью замещения предопределенных имен.

Копирование типов

переменная%TYPE

%TYPE предоставляет тип данных переменной или столбца таблицы. Это можно использовать для объявления переменных, которые будут содержать значения базы данных. Например, предположим, что у вас есть столбец с именем user_id в таблице users. Чтобы объявить переменную с тем же типом данных, что и users.user_id вы пишете:

user_id users.user_id%TYPE;

Используя %TYPE, необязательно знать тип данных структуры, на которую вы ссылаетесь, и, самое главное, если тип данных ссылочного элемента в будущем изменится (например, вы поменяете тип user_id с integer на real), возможно, вам не понадобится менять определение функции.

Конструкция %TYPE особенно полезна в полиморфных функциях, поскольку типы данных, необходимые для внутренних переменных, могут меняться от вызова к вызову. Подходящие переменные можно создать путем применения %TYPE к аргументам функции или местозаполнителям результата.

Типы строки

имя имя_таблицы%ROWTYPE;
имя имя_составного_типа;

Переменная составного типа называется переменной строки (или переменной типа строки). Такая переменная может содержать целую строку результата запроса SELECT или FOR, если набор столбцов этого запроса соответствует объявленному типу переменной. Доступ к отдельным полям значения строки осуществляется с помощью обычной записи с точкой, например rowvar.field.

Переменную строки можно объявить с тем же типом, что и строки существующей таблицы или представления, используя нотацию имя_таблицы%ROWTYPE, либо путем указания имени составного типа. (Поскольку каждая таблица имеет связанный составной тип с тем же именем, в QHB не имеет значения, пишете ли вы %ROWTYPE или нет. Но форма с %ROWTYPE лучше переносится.)

Параметрами функции могут быть составные типы (полные строки таблицы). В этом случае переменной строки будет соответствующий идентификатор $n, и из него можно выбрать поля, например $1.user_id.

Далее идет пример использования составных типов. table1 и table2 являются существующими таблицами, имеющими как минимум упомянутые поля:

CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;

Типы записи

имя RECORD;

Переменные записи аналогичны переменным типа строки, но не имеют предопределенной структуры. Они принимают фактическую структуру строки, назначенной им при выполнении команды SELECT или FOR. Подструктура переменной записи может меняться каждый раз, когда ей присваивается значение. Вследствие этого до тех пор, пока переменной записи не будет присвоено первое значение, она не имеет подструктуры, и любая попытка доступа к полю в ней вызовет ошибку во время выполнения.

Обратите внимание, что RECORD не является истинным типом данных, а только местозаполнителем. Следует также понимать, что когда функция PL/pgSQL объявляется как возвращающая тип record, это не совсем то же самое, что и переменная записи, хотя такая функция может использовать переменную записи для хранения своего результата. В обоих случаях фактическая структура строки неизвестна при написании функции, но для функции, возвращающей record, фактическая структура определяется при синтаксическом анализе вызывающего запроса, тогда как переменная записи может изменить свою структуру строки на ходу.

Сортировка переменных PL/pgSQL

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

CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b;
END;
$$ LANGUAGE plpgsql;

SELECT less_than(text_field_1, text_field_2) FROM table1;
SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;

В первом случае less_than будет использовать для сравнения text_field_1 и text_field_2 общее правило сортировки, в то время как во втором случае этот оператор будет использовать правило сортировки C.

Более того, предполагается, что идентифицированное правило сортировки также будет использоваться при сортировке любых локальных переменных сортируемых типов. Таким образом, эта функция не будет работать по-другому, если написать ее как

CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
DECLARE
    local_a text := a;
    local_b text := b;
BEGIN
    RETURN local_a < local_b;
END;
$$ LANGUAGE plpgsql;

Если параметры с сортируемыми типами данных отсутствуют или для них нельзя определить общее правило сортировки, то параметры и локальные переменные используют правила сортировки по умолчанию для своего типа данных (обычно это правила сортировки по умолчанию для базы данных, но могут отличаться для переменных типа домена).

Локальная переменная сортируемого типа данных может иметь другое правило сортировки, связываемое с ней путем включения параметра COLLATE в ее объявление, например:

DECLARE
    local_a text COLLATE "en_US";

Этот параметр переопределяет правило сортировки, которое в противном случае было бы установлено для этой переменной в соответствии с приведенными выше правилами.

Кроме того, разумеется, если требуется применить в конкретной операции конкретное правило сортировки, то внутри функции можно написать явные предложения COLLATE. Например:

CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b COLLATE "C";
END;
$$ LANGUAGE plpgsql;

Это переопределяет правила сортировки, связанные со столбцами таблицы, параметрами или локальными переменными, используемыми в выражении, аналогично тому, как это происходит в простой команде SQL.

Выражения

Все выражения, используемые в операторах PL/pgSQL, обрабатываются с использованием основного SQL-исполнителя сервера. Например, когда вы пишете оператор PL/pgSQL как

IF выражение THEN ...

то PL/pgSQL вычислит выражение, отправив на основной движок SQL следующий запрос:

SELECT выражение

При формировании команды SELECT любые вхождения имен переменных PL/pgSQL заменяются параметрами запроса, как описано в подразделе Подстановка переменных. Это позволяет составлять план запроса для SELECT только один раз, а затем повторно использовать его для последующих вычислений с различными значениями переменных. Таким образом, на самом деле при первом использовании выражения по сути происходит выполнение команды PREPARE. Например, если мы объявили две целочисленные переменные x и y, и мы напишем

IF x < y THEN ...

то, что происходит за кулисами, равнозначно

PREPARE имя_оператора(integer, integer) AS SELECT $1 < $2;

и затем этот подготовленный оператор выполняется с EXECUTE при каждом выполнении оператора IF с текущими значениями переменных PL/pgSQL, подставляемыми в качестве значений параметров. Обычно эти детали неважны для пользователя PL/pgSQL, но их полезно знать при попытке диагностировать проблему. Более подробная информация представлена в подразделе Кэширование плана.

Поскольку выражение преобразуется в команду SELECT, оно может содержать те же предложения, что и обычная команда SELECT, за исключением предложений UNION, INTERSECT и EXCEPT верхнего уровня. Таким образом, например, можно проверить, является ли таблица непустой:

IF count(*) > 0 FROM my_table THEN ...

так как выражение между IF и THEN анализируется, как если бы это была команда SELECT count(*) > 0 FROM my_table. SELECT должна выдавать один столбец и не более одной строки. (Если она не выдает ни одной строки, считается, что результат равен NULL.)

Основные операторы

В этом и следующих разделах описываются все типы операторов, которые явно понятны PL/pgSQL. Все, что не распознается как один из этих типов операторов, считается командой SQL и отправляется на выполнение главному движку базы данных, как описано в подразделе Выполнение команд SQL.

Присваивание

Присвоение значения переменной PL/pgSQL записывается следующим образом:

переменная { := | = } выражение;

Как говорилось ранее, выражение в таком операторе вычисляется с помощью команды SQL SELECT, передаваемой основному движку базы данных. Выражение должно выдавать одно значение (возможно, значение строки, если это переменная строки или записи). Целевая переменная может быть простой переменной (необязательно дополненной именем блока), полем строки или переменной записи либо элементом или срезом целевого массива. Вместо PL/SQL-совместимого сочетания := можно использовать просто знак равенства (=).

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

Примеры:

tax := subtotal * 0.06;
my_record.user_id := 20;
my_array[j] := 20;
my_array[1:3] := array[1,2,3];
complex_array[n].realpart = 12.3;

Выполнение команд SQL

В целом, любую команду SQL, которая не возвращает строки, можно выполнить в функции PL/pgSQL, просто написав эту команду. Например, можно создать и заполнить таблицу, написав

CREATE TABLE mytable (id int primary key, data text);
INSERT INTO mytable VALUES (1,'one'), (2,'two');

Если же команда возвращает строки (например SELECT или INSERT/UPDATE/ DELETE с RETURNING), есть два способа ее выполнить. Если команда возвращает максимум одну строку или вам интересна только первая строка вывода, напишите команду как обычно, но добавьте предложение INTO для захвата вывода, как описано в подразделе Выполнение команды с результатом в одну строку. Чтобы обработать все строки вывода, напишите команду как источник данных для цикла FOR, как описано в подразделе Цикл по результатам запроса.

Обычно недостаточно просто выполнить статически определенные команды SQL. Как правило, желательно, чтобы команда использовала разные значения данных или даже менялась в более фундаментальном смысле, например каждый раз использовала разные имена таблиц. Опять же, это можно сделать двумя способами, в зависимости от ситуации.

Значения переменных PL/pgSQL могут автоматически вставляться в оптимизированные команды SQL: SELECT, INSERT, UPDATE, DELETE и определенные служебные команды, содержащие одну из вышеперечисленных команды, например EXPLAIN и CREATE TABLE ... AS SELECT. Любое имя переменной PL/pgSQL, встречающееся в тексте этих команд, заменяется параметром запроса, а затем текущее значение этой переменной подставляется в качестве значения параметра во время выполнения. Это полностью совпадает с описанной ранее обработкой для выражений; подробную информацию см. в подразделе Подстановка переменных.

При выполнении оптимизируемой команды SQL таким способом PL/pgSQL может кэшировать и повторно использовать план выполнения команды, как описано в подразделе Кэширование плана.

Неоптимизируемые команды SQL (также называемые служебными командами) неспособны принимать параметры запроса. Поэтому в таких командах автоматическая подстановка переменных PL/pgSQL не работает. Чтобы включить непостоянный текст в служебную команду, выполняемую из PL/pgSQL, следует собрать служебную команду в виде строки, а затем выполнить ее с EXECUTE, как описано в подразделе Выполнение динамических команд.

Также EXECUTE следует использовать, если вы хотите модифицировать команду иным способом, нежели просто передавая ей значения данных, например, изменить имя таблицы.

Иногда бывает полезно вычислить выражение или запрос SELECT, но отбросить результат, например, при вызове функции с побочными эффектами, не имеющей полезного значения результата. Чтобы сделать это в PL/pgSQL, воспользуйтесь оператором PERFORM:

PERFORM запрос;

Эта команда выполняет запрос и отбрасывает результат. Напишите запрос тем же способом, что и в команде SQL SELECT, но замените исходное ключевое слово SELECT на PERFORM. Для запросов WITH используйте PERFORM, а затем поместите запрос в скобки. (В этом случае запрос может вернуть только одну строку.) Переменные PL/pgSQL будут подставляться в запрос так же, как описано выше, и план кэшируется таким же образом. Кроме того, специальная переменная FOUND устанавливается в true, если запрос выдал хотя бы одну строку, или в false, если он не выдал ни одной строки (см. подраздел Получение статуса результата).

Примечание
Можно было бы ожидать, что к этому же результату приведет написание SELECT напрямую, но в настоящее время единственным приемлемым способом сделать это является PERFORM. Команда SQL, которая может возвращать строки, например SELECT, будет отклонена с ошибкой, если в ней нет предложения INTO, как описано в следующем подразделе.

Пример:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

Выполнение команды с результатом в одну строку

Результат команды SQL, выдающей одну строку (возможно, из нескольких столбцов), может быть присвоен переменной записи, переменной типа строки или списку скалярных переменных. Это делается путем написания базовой команды SQL и добавления предложения INTO. Например,

SELECT выражения_select INTO [STRICT] цель FROM ...;
INSERT ... RETURNING выражения INTO [STRICT] цель;
UPDATE ... RETURNING выражения INTO [STRICT] цель;
DELETE ... RETURNING выражения INTO [STRICT] цель;

где цель может быть переменной записи, переменной строки или разделенным запятыми списком простых переменных и полей записи/строки. В остальную часть запроса (то есть везде, кроме предложения INTO) будут подставляться переменные PL/pgSQL, как описано выше, и план будет кэширован таким же образом. Это работает для SELECT, INSERT/UPDATE/DELETE с RETURNING и определенных служебных команд, которые возвращают наборы строк (например EXPLAIN). За исключением предложения INTO, это такая же команда SQL, как если бы она была написана вне PL/pgSQL.

Совет
Обратите внимание, что эта интерпретация SELECT с INTO сильно отличается от обычной команды SELECT INTO QHB, в которой целью INTO является вновь созданная таблица. Если вы хотите создать таблицу из результата SELECT внутри функции PL/pgSQL, используйте синтаксис CREATE TABLE... AS SELECT.

Если в качестве цели используется строка или список переменных, столбцы результата команды должны точно соответствовать структуре цели в отношении количества и типов данных, иначе произойдет ошибка во время выполнения. Когда целью является переменная записи, она автоматически конфигурируется в тип строки столбцов результата команды.

Предложение INTO может находиться практически в любом месте команды SQL. Обычно оно записывается либо непосредственно перед или сразу после списка выражения_select в команде SELECT, либо в конце команды для других типов команд. Рекомендуется следовать этому соглашению на случай, если анализатор PL/pgSQL в будущих версиях станет более строгим.

Если в предложении INTO не указан STRICT, то цели будет присвоена первая строка, возвращаемая запросом, или на NULL, если команда не вернула ни одной строки. (Обратите внимание, что «первая строка» не определяется четко, если вы не использовали ORDER BY). Все строки результата после первой отбрасываются. Чтобы определить, была ли возвращена строка, можно проверить специальную переменную FOUND (см. подраздел Получение статуса результата):

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;

Если указан параметр STRICT, команда должна возвращать ровно одну строку, иначе будет выдана ошибка во время выполнения: либо NO_DATA_FOUND (нет строк), либо TOO_MANY_ROWS (более одной строки). Если хотите найти эту ошибку, можно использовать блок исключения, например:

BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

Успешное выполнение команды с помощью STRICT всегда устанавливает FOUND в true.

Для INSERT/UPDATE/DELETE с RETURNING PL/pgSQL выдает ошибку при более чем одной возвращенной строки, даже если STRICT не указан. Это связано с тем, что здесь нет параметра вроде ORDER BY, с помощью которого можно определить, какая затронутая строка должна быть возвращена.

Если для функции включен print_strict_params, то при возникновении ошибки из-за несоблюдения требований STRICT часть DETAIL в сообщении об ошибке будет содержать информацию о параметрах, переданных в команду. Изменить значение print_strict_params для всех функций можно, установив параметр plpgsql.print_strict_params, хотя это подействует только на последующие компиляции функций. Также его можно включить для каждой функции по отдельности, используя параметр компилятора, например:

CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END
$$ LANGUAGE plpgsql;

При сбое эта функция может выдавать сообщение об ошибке, например:

ERROR:  query returned no rows
DETAIL:  parameters: $1 = 'nosuchuser'
CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement
-- ОШИБКА: параметр не вернул ни одной строки
-- ДЕТАЛИЗАЦИЯ: параметры: $1 = 'пользователь не найден'
-- КОНТЕКСТ: строка 6 функции PL/pgSQL get_userid(text) в операторе SQL

Примечание
Параметр STRICT соответствует поведению SELECT INTO и связанных операторов в Oracle PL/SQL.

Выполнение динамических команд

Зачастую возникает необходимость генерировать динамические команды внутри функций PL/pgSQL, то есть команды, которые при каждом выполнении будут затрагивать разные таблицы или разные типы данных. Обычные попытки PL/pgSQL кэшировать планы для команд (как описано в подразделе Кэширование плана) в таких сценариях работать не будут. Для решения такого рода проблем предусмотрен оператор EXECUTE:

EXECUTE командная-строка [ INTO [STRICT] цель ] [ USING выражение [, ... ] ];

где командная-строка представляет собой выражение, выдающее строку (типа text), содержащую выполняемую команду. Необязательная цель является переменной записи, переменной строки или разделенным запятыми списком простых переменных и полей записи/строки, в которых будут сохраняться результаты команды. Необязательные выражения USING предоставляют значения для вставки в команду.

Подстановка переменных PL/pgSQL в вычисляемой командной строке не производится. Все требуемые значения переменных должны быть вставлены в командную строку при ее создании, либо можно использовать параметры, как описано ниже.

Также для команд, выполняемых через EXECUTE, не проводится кэширования плана. Вместо этого команда всегда планируется при каждом выполнении оператора. Таким образом, командная строка может быть динамически создана внутри функции для выполнения действий над различными таблицами и столбцами.

Предложение INTO указывает, куда должны быть назначены результаты команды SQL, возвращающей строки. Если указана переменная строки или список переменных, они должны точно соответствовать структуре результатов команды; если используется переменная записи, она автоматически конфигурируется, чтобы соответствовать структуре результатов. Если возвращается несколько строк, только первая из них будет назначена переменной INTO. Если не возвращается ни одной строки, переменной (или переменным) INTO присваивается NULL. Если предложение INTO не указано, результаты запроса отбрасываются.

Если задан параметр STRICT, то команда должна создать ровно одну строку, иначе выдается сообщение об ошибке.

В командной строке можно использовать значения параметров, ссылки на которые в команде обозначаются как $1, $2 и т. д. Эти символы обращаются к значениям, предоставленным в предложении USING. Этот метод часто предпочтительнее вставки значений данных в командную строку в виде текста: он позволяет избежать издержек во время выполнения при преобразовании значений в текст и обратно, и он гораздо менее подвержен атакам с использованием SQL-инъекций, поскольку нет необходимости использовать кавычки или экранирующие символы. Пример:

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Обратите внимание, что символы параметров могут использоваться только для значений данных — если вы хотите использовать динамически определяемые имена таблиц или столбцов, их следует вставлять в командную строку в текстовом виде. Например, если предыдущий запрос необходимо выполнить для динамически выбранной таблицы, можно сделать следующее:

EXECUTE 'SELECT count(*) FROM '
    || quote_ident(tabname)
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Более чистый подход заключается в использовании указания %I функции format(), чтобы вставить имена таблиц или столбцов, которые будут автоматически заключаться в кавычки:

EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

(Этот пример основывается на правиле SQL, гласящем, что строковые литералы, разделенные символом перевода строки, неявно конкатенируются.)

Другое ограничение на символы параметров заключается в том, что они работают только в оптимизируемых командах SQL (SELECT, INSERT, UPDATE, DELETE и некоторые другие команды, содержащие одну из вышеперечисленных.) В других типах операторов (обычно называемых служебными операторами) следует вставлять значения в текстовом виде, даже если это просто значения данных.

EXECUTE с простой константой командной строки и некоторыми параметрами USING, как в первом примере выше, функционально равнозначна написанию команды непосредственно в PL/pgSQL и позволяет автоматической заменять значениями переменные PL/pgSQL. Важное отличие заключается в том, что EXECUTE будет заново планировать команду при каждом выполнении, генерируя план, специфичный для текущих значений параметров, тогда как PL/pgSQL может создать общий план и кэшировать его для повторного использования. В ситуациях, когда лучший план сильно зависит от значений параметров, может быть полезно использовать EXECUTE чтобы общий план гарантированно не выбирался.

В настоящее время команда SELECT INTO в EXECUTE не поддерживается; вместо этого выполните простую команду SELECT и укажите INTO как часть самой команды EXECUTE.

Примечание
Оператор PL/pgSQL EXECUTE не имеет отношения к оператору SQL EXECUTE, поддерживаемому сервером QHB. Оператор EXECUTE сервера нельзя напрямую использовать в функциях PL/pgSQL (и не нужно).

Пример 1. Заключение в кавычки значений в динамических запросах

При работе с динамическими командами часто приходится иметь дело с экранированием апострофов. Рекомендованный метод заключения в кавычки фиксированного текста в теле функции — это экранирование знаками доллара. (Если у вас есть устаревший код, который не использует долларовые кавычки, обратитесь к обзору в подразделе Обработка кавычек, который может сэкономить некоторые усилия при переводе этого кода в более разумную схему.)

Динамические значения требуют аккуратного обращения, поскольку могут содержать символы кавычек. Пример использования функции format() (предполагается, что вы заключили тело функции в долларовые кавычки, поэтому апострофы не нужно удваивать):

EXECUTE format('UPDATE tbl SET %I = $1 '
   'WHERE key = $2', colname) USING newvalue, keyvalue;

Также можно напрямую вызывать функции заключения в кавычки:

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);

Этот пример демонстрирует использование функций quote_ident и quote_literal (см. раздел Строковые функции и операторы). В целях безопасности выражения, содержащие идентификаторы столбцов или таблиц, должны перед вставкой в динамический запрос передаваться через quote_ident. Выражения, содержащие значения, которые должны быть литеральными строками в построенной команде, должны передаваться через quote_literal. Эти функции предпринимают соответствующие шаги для возврата входного текста, заключенного в кавычки или апострофы соответственно, с правильным экранированием всех содержащихся в нем специальных символов.

Поскольку функция quote_literal помечена как STRICT, она всегда будет возвращать NULL при вызове с аргументом NULL. Если бы в приведенном выше примере newvalue или keyvalue были равны NULL, то вся строка динамического запроса стала бы равна NULL, что привело бы к ошибке EXECUTE. Этой проблемы можно избежать, применив функцию quote_nullable, которая работает так же, как quote_literal, но при вызове с аргументом NULL возвращает строку NULL. Например:

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_nullable(newvalue)
        || ' WHERE key = '
        || quote_nullable(keyvalue);

Если вы имеете дело со значениями, которые могут быть равны NULL, то, как правило, вместо quote_literal стоит использовать quote_nullable.

Как всегда, нужно позаботиться о том, чтобы значения NULL в запросе не давали непредвиденных результатов. Например, предложение WHERE

'WHERE key = ' || quote_nullable(keyvalue)

никогда не будет успешным, если keyvalue равно NULL, потому что результат использования оператора равенства = с операндом NULL всегда равен NULL. Если вы хотите, чтобы значение NULL работало как обычное значение ключа, необходимо переписать условие выше как

'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

(В настоящее время IS NOT DISTINCT FROM обрабатывается гораздо менее эффективно, чем =, поэтому не делайте этого без необходимости. Дополнительную информации о NULL и IS DISTINCT см. в разделе Функции и операторы сравнения.)

Обратите внимание, что знаки доллара полезны только для экранирования фиксированного текста. Было бы очень плохой идеей написать этот пример так:

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = $$'
        || newvalue
        || '$$ WHERE key = '
        || quote_literal(keyvalue);

потому что он бы сломался, если бы содержимое newvalue содержало $$. Та же проблема касается и любого другого разделителя в знаках доллара, который вы можете выбрать. Таким образом, для безопасного заключения к в кавычки текста, который не известен заранее, нужно использовать quote_literal, quote_nullable или quote_ident, в зависимости от ситуации.

Также динамические операторы SQL можно безопасно создавать с помощью функции format (см. подраздел format). Например:

EXECUTE format('UPDATE tbl SET %I = %L '
   'WHERE key = %L', colname, newvalue, keyvalue);

Указание %I равнозначно вызову quote_ident, а %L — quote_nullable. Функцию format можно использовать вместе с предложением USING:

EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
   USING newvalue, keyvalue;

Эта форма лучше, потому что переменные обрабатываются в их собственном формате типа данных, а не преобразуются безусловно в текст и затем заключаются в кавычки посредством %L Кроме того, она более эффективна.

Гораздо более объемный пример динамической команды и EXECUTE можно увидеть в Примере 10, где создается и выполняется команда CREATE FUNCTION для определения новой функции.

Получение статуса результата

Существует несколько способов определить эффект команды. Первый метод заключается в использовании команды GET DIAGNOSTICS, которая имеет вид:

GET [ CURRENT ] DIAGNOSTICS переменная { = | := } элемент [, ... ];

Эта команда позволяет получить системные индикаторы состояния. CURRENT — неучитываемое слово (но см. также описание GET STACKED DIAGNOSTICS в подразделе Получение информации об ошибке). Каждый элемент является ключевым словом, определяющим значение состояния, назначаемое указанной переменной (которая должна иметь правильный тип данных для его получения). Доступные в настоящее время элементы состояния показаны в Таблице 1. Вместо принятой в стандарте SQL синтаксической единицы = можно использовать сочетание двоеточия и знака равенства (:=). Пример:

GET DIAGNOSTICS integer_var = ROW_COUNT;

Таблица 1. Доступные элементы диагностики

Имя	Тип	Описание
ROW_COUNT	bigint	количество строк, обработанных самой последней командой SQL
PG_CONTEXT	text	строка(и) текста, описывающего текущий стек вызовов (см. подраздел Получение информации о месте выполнения)

Второй метод определения эффектов команды — это проверка специальной переменной с именем FOUND, которая имеет тип boolean. FOUND запускается с false при каждом вызове функции PL/pgSQL. Она устанавливается каждым из следующих типов операторов:

Оператор SELECT INTO устанавливает FOUND в true, если строка назначена, и в false, если не возвращается ни одной строки.
Оператор PERFORM устанавливает FOUND в true, если он создает (и отбрасывает) одну и более строк, и false, если не создается ни одной строки.
Операторы UPDATE, INSERT и DELETE устанавливают FOUND в true, если затронута хотя бы одна строка, и false, если не затрагивается ни одной строки.
Оператор FETCH устанавливает FOUND в true, если возвращает строку, и false, если не возвращается ни одной строки.
Оператор MOVE устанавливает FOUND в true, если он успешно перемещает курсор, в противном случае — false.
Оператор FOR или FOREACH устанавливает FOUND в true, если он повторяется один и более раз, в противном случае - false. FOUND устанавливается таким образом при выходе из цикла; внутри выполнения цикла оператор цикла не изменяет FOUND, хотя ее значение может изменить выполнение других операторов в теле цикла.
Операторы RETURN QUERY и RETURN QUERY EXECUTE устанавливают FOUND в true, если запрос возвращает хотя бы одну строку, и false, если не возвращается ни одной строки.

Другие операторы PL/pgSQL не изменяют состояние FOUND. В частности, обратите внимание, что EXECUTE изменяет вывод GET DIAGNOSTICS, но не меняет FOUND.

FOUND — локальная переменная в каждой функции PL/pgSQL; любые изменения в ней затрагивают только текущую функцию.

Вообще ничего не делать

Иногда бывает полезен оператор-заполнитель, который ничего не делает. Например, он может показать, что одна ветвь цепочки если/то/иначе намеренно оставлена пустой. Для этого используется оператор NULL:

NULL;

Например, следующие два фрагмента кода равнозначны:

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        NULL;  -- ошибка игнорируется
END;

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN  -- ошибка игнорируется
END;

Что предпочесть — дело вкуса.

Примечание
В Oracle PL/SQL пустые списки операторов недопустимы, поэтому для таких ситуаций требуются операторы NULL. PL/pgSQL позволяет просто ничего не писать.

Управляющие структуры

Управляющие структуры, вероятно, наиболее полезная (и важная) часть PL/pgSQL. С управляющими структурами PL/pgSQL можно манипулировать данными QHB очень гибким и высокопроизводительным образом.

Возврат из функции

Имеются две команды, которые позволяют возвращать данные из функции: RETURN и RETURN NEXT.

RETURN

RETURN выражение;

RETURN с выражением завершает функцию и возвращает значение выражения вызывающей функции. Эта форма используется для функций PL/pgSQL, не возвращающих множества.

В функции, которая возвращает скалярный тип, результат выражения будет автоматически преобразован в возвращаемый тип, как описано для назначений. Но чтобы вернуть составное (строковое) значение, следует написать выражение, доставляющее точно запрошенный набор столбцов. Это может потребовать использования явного приведения.

Если вы объявили функцию с выходными параметрами, напишите просто RETURN без выражения. Будут возвращены текущие значения переменных выходного параметра.

Если вы объявили функцию как возвращающую void, оператор RETURN можно использовать для досрочного выхода из функции; но не пишите выражение после RETURN.

Возвращаемое значение функции нельзя оставлять неопределенным. Если управление достигает конца блока верхнего уровня функции, не встретив оператор RETURN, произойдет ошибка времени выполнения. Однако это ограничение не распространяется на функции с выходными параметрами и на функции, возвращающие void. В этих случаях оператор RETURN выполняется автоматически по окончании блока верхнего уровня.

Несколько примеров:

-- функции, возвращающие скалярный тип
RETURN 1 + 2;
RETURN scalar_var;

-- функции, возвращающие составной тип
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- нужно привести столбцы к корректным типам

RETURN NEXT и RETURN QUERY

RETURN NEXT выражение;
RETURN QUERY запрос;
RETURN QUERY EXECUTE командная-строка [ USING выражение [, ... ] ];

Когда функция PL/pgSQL объявляется как возвращающая SETOF некий_тип, процедура, которой нужно следовать, немного отличается. В этом случае отдельные возвращаемые элементы задаются с помощью последовательности команд RETURN NEXT или RETURN QUERY, а затем используется последняя команда RETURN без аргументов, указывающая, что функция завершилась. Команду RETURN NEXT можно использовать как со скалярными, так и с составными типами данных; с составным типом результата будет возвращена вся «таблица» результатов. Команда RETURN QUERY добавляет результаты выполнения запроса к результирующему набору функции. RETURN NEXT и RETURN QUERY можно свободно смешивать в одной функции, возвращающей множество, и в этом случае их результаты будут конкатенированы.

На самом деле RETURN NEXT и RETURN QUERY не делают возврат из функции — они просто добавляют ноль или более строк в результирующий набор функции. Затем выполнение продолжается со следующего оператора в функции PL/pgSQL. По мере выполнения последовательных команд RETURN NEXT или RETURN QUERY формируется результирующий набор. Финальная команда RETURN, которая не должна иметь аргументов, заставляет элемент управления выйти из функции (или можно просто позволить элементу управления достигнуть конца функции).

У команды RETURN QUERY есть вариант RETURN QUERY EXECUTE, который указывает, что запрос должен выполняться динамически. Выражения параметров можно вставить в вычисляемую строку запроса через USING, точно так же, как в команде EXECUTE.

Если вы объявили функцию с выходными параметрами, напишите просто RETURN NEXT без выражения. При каждом выполнении текущие значения переменных выходных параметров будут сохраняться для последующего возврата в виде строки результата. Обратите внимание, что для создания функции, возвращающей множество, с выходными параметрами нужно объявить функцию как возвращающую SETOF record, когда есть несколько выходных параметров, или SETOF некий_тип, когда есть только один выходной параметр типа некий_тип.

Пример функции, использующей RETURN NEXT:

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM foo WHERE fooid > 0
    LOOP
        -- здесь можно выполнить обработку данных
        RETURN NEXT r; -- возвращается текущая строка SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();

Пример функции, использующей RETURN QUERY:

CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY SELECT flightid
                   FROM flight
                  WHERE flightdate >= $1
                    AND flightdate < ($1 + 1);

    -- Поскольку выполнение еще не закончено, можно проверить, были ли возвращены
    -- строки, и выдать исключение, если нет.
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No flight at %.', $1;
    END IF;

    RETURN;
 END
$BODY$
LANGUAGE plpgsql;

-- Возвращает доступные рейсы или выдает исключение, если доступные рейсы
-- отсутствуют.
SELECT * FROM get_available_flightid(CURRENT_DATE);

Примечание
Текущая реализация RETURN NEXT и RETURN QUERY сохраняет весь результирующий набор перед возвратом из функции, как говорилось выше. Это означает, что если функция PL/pgSQL выдает очень большой результирующий набор, производительность может быть низкой: данные будут записаны на диск во избежание исчерпания памяти, но сама функция не вернется, пока не будет сгенерирован весь результирующий набор. Будущая версия PL/pgSQL может позволить пользователям определять функции, возвращающие множества, у которых нет этого ограничения. В настоящее время точка, с которой данные начинают записываться на диск, управляется переменной конфигурации work_mem. Администраторам, которые имеют в распоряжении достаточно памяти для хранения больших результирующих наборов, стоит рассмотреть возможность увеличения этого параметра.

Возврат из процедуры

Процедура не имеет возвращаемого значения. Поэтому она может завершиться без оператора RETURN. Если вы хотите использовать оператор RETURN для досрочного выхода из кода, напишите просто RETURN без выражения.

Если процедура имеет выходные параметры, итоговые значения переменных выходных параметров будут возвращены вызывающей функции.

Вызов процедуры

Функция, процедура или блок DO PL/pgSQL могут вызывать процедуру, используя оператор CALL. Выходные параметры обрабатываются не так, как CALL работает в простом SQL. Каждый параметр OUT или INOUT процедуры должен соответствовать переменной в операторе CALL, и все, что возвращает процедура, присваивается этой переменной после ее возврата. Например:

CREATE PROCEDURE triple(INOUT x int)
LANGUAGE plpgsql
AS $$
BEGIN
    x := x * 3;
END;
$$;

DO $$
DECLARE myvar int := 5;
BEGIN
  CALL triple(myvar);
  RAISE NOTICE 'myvar = %', myvar;  -- выводится 15
END
$$;

Переменная, соответствующая выходному параметру, может быть простой переменной или полем переменной составного типа. На данный момент она не может быть элементом массива.

Условные операторы

Операторы IF и CASE позволяют выполнять альтернативные команды в зависимости от определенных условий. В PL/pgSQL имеется три формы IF:

IF ... THEN ... END IF
IF ... THEN ... ELSE ... END IF
IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF

и две формы CASE:

CASE ... WHEN ... THEN ... ELSE ... END CASE
CASE WHEN ... THEN ... ELSE ... END CASE

IF-THEN

IF логическое-выражение THEN
    операторы
END IF;

Оператор IF-THEN является простейшей формой оператора IF. Операторы между THEN и END IF будут выполнены, если условие равно true. В противном случае они пропускаются.

Пример:

IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

IF-THEN-ELSE

IF логическое-выражение THEN
    операторы
ELSE
    операторы
END IF;

Операторы IF-THEN-ELSE добавляются к IF-THEN, позволяя задать альтернативный набор операторов, которые должны выполняться, если условие не равно true. (Обратите внимание, что это включает случай, когда условие вычисляется как равное NULL).

Примеры:

IF parentid IS NULL OR parentid = ''
THEN
    RETURN fullname;
ELSE
    RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;

IF v_count > 0 THEN
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;

IF-THEN-ELSIF

IF логическое-выражение THEN
    операторы
[ ELSIF логическое-выражение THEN
    операторы
[ ELSIF логическое-выражение THEN
    операторы
    ...
]
]
[ ELSE
    операторы ]
END IF;

Иногда альтернатив бывает больше двух. IF-THEN-ELSIF предоставляет удобный метод поочередной проверки нескольких альтернатив. Условия IF проверяются последовательно, пока не будет найдено первое условие, равное true. Затем выполняются связанные операторы, после чего управление переходит к следующему оператору после END IF. (Любые последующие условия IF не проверяются). Если ни одно из условий IF не является истинным, выполняется блок ELSE (если таковой имеется).

Пример:

IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    -- остается только один вариант — number равен NULL
    result := 'NULL';
END IF;

Ключевое слово ELSIF также можно написать как ELSEIF.

Альтернативный способ выполнения той же задачи — вложение операторов IF-THEN-ELSE, как в следующем примере:

IF demo_row.sex = 'm' THEN
    pretty_sex := 'man';
ELSE
    IF demo_row.sex = 'f' THEN
        pretty_sex := 'woman';
    END IF;
END IF;

Однако этот метод требует написания соответствующего END IF для каждого IF, поэтому при наличии нескольких альтернативах он будет гораздо более громоздким, чем использование ELSIF.

Простой CASE

CASE выражение-поиска
    WHEN выражение [, выражение [ ... ]] THEN
      операторы
  [ WHEN выражение [, выражение [ ... ]] THEN
      операторы
    ... ]
  [ ELSE
      операторы ]
END CASE;

Простая форма CASE обеспечивает условное выполнение, основанное на равенстве операндов. выражение-поиска вычисляется (однократно) и последовательно сравнивается с каждым выражением в предложениях WHEN. Если совпадение найдено, то выполняются соответствующие операторы, а затем управление переходит к следующему оператору после END CASE. (Последующие выражения WHEN не вычисляются.) Если совпадений не найдено, выполняются ELSE операторы, но если ELSE нет, то возникает исключение CASE_NOT_FOUND.

Простой пример:

CASE x
    WHEN 1, 2 THEN
        msg := 'один или два';
    ELSE
        msg := 'значение, отличное от один или два';
END CASE;

Поисковый CASE

CASE
    WHEN логическое-выражение THEN
      операторы
  [ WHEN логическое-выражение THEN
      операторы
    ... ]
  [ ELSE
      операторы ]
END CASE;

Поисковая форма CASE обеспечивает условное выполнение, основанное на истинности логических выражений. Каждое логическое-выражение предложения WHEN вычисляется по очереди, пока не будет найдено выдающее true. Затем выполняются соответствующие операторы, а затем управление переходит к следующему оператору после END CASE. (Последующие выражения WHEN не вычисляются.) Если истинный результат не найден, выполняются ELSE операторы, но если ELSE нет, то возникает исключение CASE_NOT_FOUND.

Пример:

CASE
    WHEN x BETWEEN 0 AND 10 THEN
        msg := 'значение между нулем и десятью';
    WHEN x BETWEEN 11 AND 20 THEN
        msg := 'значение между одиннадцатью и двадцатью';
END CASE;

Эта форма CASE полностью равнозначна IF-THEN-ELSIF, за исключением правила, согласно которому достижение пропущенного предложения ELSE приводит к ошибке, а не к бездействию.

Простые циклы

С помощью операторов LOOP, EXIT, CONTINUE, WHILE, FOR и FOREACH можно настроить функцию PL/pgSQL на повторение ряда команд.

LOOP

[ <<метка>> ]
LOOP
    операторы
END LOOP [ метка ];

Оператор LOOP определяет безусловный цикл, который повторяется бесконечно, пока не будет завершен с помощью оператора EXIT или RETURN. Необязательная метка может использоваться операторами EXIT и CONTINUE во вложенных циклах, чтобы указать, к какому циклу обращаются эти операторы.

EXIT

EXIT [ метка ] [ WHEN логическое-выражение ];

Если метка не указана, самый внутренний цикл завершается, а затем выполняется оператор, следующий за END LOOP. Если метка указывается, это должна быть метка текущего или некоторого внешнего уровня вложенного цикла или блока. Затем именованный цикл или блок завершается, и управление переходит к оператору после соответствующего END цикла/блока.

Если указано WHEN, выход из цикла происходит только в том случае, если логическое-выражение равно true. В противном случае управление передается оператору после EXIT.

EXIT может использоваться со всеми типами циклов; его использование не ограничено безусловными циклами.

При использовании с блоком BEGIN, EXIT передает управление следующему оператору после конца блока. Обратите внимание, что для этой цели должна использоваться метка; немаркированный EXIT никогда не считается совпадающим с блоком BEGIN.

Примеры:

LOOP
    -- некоторые вычисления
    IF count > 0 THEN
        EXIT;  -- выход из цикла
    END IF;
END LOOP;

LOOP
    -- некоторые вычисления
    EXIT WHEN count > 0;  -- тот же результат, что и в предыдущем примере
END LOOP;

<<ablock>>
BEGIN
    -- некоторые вычисления
    IF stocks > 100000 THEN
        EXIT ablock;  -- выход из блока BEGIN
    END IF;
    -- вычисления будут пропущены, если stocks > 100000
END;

CONTINUE

CONTINUE [ метка ] [ WHEN логическое-выражение ];

Если метка не указана, начинается следующая итерация самого внутреннего цикла. То есть все операторы, оставшиеся в теле цикла, пропускаются, и управление возвращается к управляющему выражению цикла (если оно есть), чтобы определить, нужна ли еще одна итерация цикла. Если метка имеется, она задает метку цикла, выполнение которого будет продолжено.

Если указано WHEN, следующая итерация цикла начинается только в том случае, если логическое-выражение равно true. В противном случае управление переходит к оператору после CONTINUE.

CONTINUE можно использовать со всеми типами циклов; его использование не ограничено безусловными циклами.

Примеры:

LOOP
    -- некоторые вычисления
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- некоторые вычисления для count IN [50 .. 100]
END LOOP;

WHILE

[ <<метка>> ]
WHILE логическое-выражение LOOP
    операторы
END LOOP [ метка ];

Оператор WHILE повторяет последовательность операторов до тех пор, пока логическое-выражение вычисляется как true. Это выражение проверяется перед каждой записью в теле цикла.

Например:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- некоторые вычисления
END LOOP;

WHILE NOT done LOOP
    -- некоторые вычисления
END LOOP;

FOR (целочисленный вариант)

[ <<метка>> ]
FOR имя IN [ REVERSE ] выражение .. выражение [ BY выражение ] LOOP
    операторы
END LOOP [ метка ];

Эта форма FOR создает цикл, который перебирает диапазон целых значений. имя переменной автоматически определяется с типом integer и существует только внутри цикла (любое существующее определение имени переменной внутри цикла игнорируется). Два выражения, выдающие нижнюю и верхнюю границы диапазона, вычисляются однократно при входе в цикл. Если предложение BY не указано, шаг итерации равен 1, в противном случае это значение, заданное в предложении BY, которое, опять же, вычисляется однократно при входе в цикл. Если указано REVERSE, то значение шага после каждой итерации вычитается, а не добавляется.

Некоторые примеры целочисленных циклов FOR:

FOR i IN 1..10 LOOP
    -- внутри цикла переменная i будет принимать значения 1,2,3,4,5,6,7,8,9,10
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- внутри цикла переменная i будет принимать значения 10,9,8,7,6,5,4,3,2,1
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- внутри цикла переменная i будет принимать значения 10,8,6,4,2
END LOOP;

Если нижняя граница больше верхней (или меньше, в случае REVERSE), тело цикла вообще не выполняется. Ошибка при этом не возникает.

Если к циклу FOR привязана метка, то с ее помощью к целочисленной переменной цикла можно обращаться по полному имени.

Цикл по результатам запроса

Используя цикл FOR другого типа, можно перебирать результаты запроса и соответствующим образом манипулировать этими данными. Синтаксис:

[ <<метка>> ]
FOR цель IN запрос LOOP
    операторы
END LOOP [ метка ];

цель — это переменная записи, переменная строки или разделенный запятыми список скалярных переменных. цель назначается последовательно каждой строке, полученной в результате запроса, и тело цикла выполняется для каждой строки. Пример:

CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Refreshing all materialized views...';

    FOR mviews IN
       SELECT n.nspname AS mv_schema,
              c.relname AS mv_name,
              pg_catalog.pg_get_userbyid(c.relowner) AS owner
         FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
        WHERE c.relkind = 'm'
     ORDER BY 1
    LOOP

        -- Теперь "mviews" содержит одну запись с информацией о материализованном
        -- представлении

        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
                     quote_ident(mviews.mv_schema),
                     quote_ident(mviews.mv_name),
                     quote_ident(mviews.owner);
        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
    END LOOP;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Если цикл завершается оператором EXIT, последнее присвоенное значение строки будет доступно и после цикла.

запрос, используемый в операторе FOR этого типа, может быть любой командой SQL, которая возвращает строки вызывающей функции: чаще всего применяется SELECT, но также можно использовать INSERT, UPDATE или DELETE с предложением RETURNING. Некоторые служебные команды, например EXPLAIN, тоже подойдут.

Переменные PL/pgSQL заменяются параметрами запроса, и план запроса кэшируется для возможного повторного использования, как подробно описано в подразделах Подстановка переменных и Кэширование плана.

Еще один способ перебора строк — это оператор FOR-IN-EXECUTE:

[ <<метка>> ]
FOR цель IN EXECUTE текстовое_выражение [ USING выражение [, ... ] ] LOOP
    операторы
END LOOP [ метка ];

Это похоже на предыдущую форму, за исключением того, что исходный запрос указывается как строковое выражение, которое вычисляется и планируется заново для каждой записи в цикле FOR. Это позволяет программисту выбирать между скоростью заранее спланированного запроса и гибкостью динамического запроса, так же, как и с простым оператором EXECUTE. Как и в случае EXECUTE, значения параметров можно вставить в динамическую команду посредством USING.

Еще один способ указать запрос, по результатам которого необходимо выполнить перебор, — объявить его как курсор. Это описывается в подразделе Цикл по результату курсора.

Цикл по массивам

Цикл FOREACH во многом похож на цикл FOR, но вместо перебора по строкам, возвращаемым запросом SQL, он выполняет перебор по элементам значения-массива. (В целом, FOREACH предназначен для циклического прохождения по компонентам выражения составного типа; в будущем могут быть добавлены варианты циклического прохождения по значениям составного типа, отличным от массивов). Оператор FOREACH для цикла по массиву:

[ <<метка>> ]
FOREACH цель [ SLICE количество ] IN ARRAY выражение LOOP
    операторы
END LOOP [ метка ];

Без SLICE или при SLICE 0 цикл перебирает отдельные элементы массива, созданного путем вычисления выражения. Переменная цель присваивается каждому значению элемента в последовательности, и тело цикла выполняется для каждого элемента. Пример зацикливания элементов целочисленного массива:

CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
  s int8 := 0;
  x int;
BEGIN
  FOREACH x IN ARRAY $1
  LOOP
    s := s + x;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql;

Элементы посещаются в порядке хранения, независимо от количества измерений массива. Хотя цель обычно представляет собой всего одну переменную, она может быть и списком переменных при циклическом просмотре массива значений (записей) составного типа. В этом случае для каждого элемента массива переменные назначаются из последовательных столбцов составного значения.

При положительном значении SLICE FOREACH выполняет перебор по фрагментам массива, а не по отдельным элементам. Значение SLICE должно быть целочисленной константой, не превышающей количество измерений массива. Переменная цель должна быть массивом, получающим последовательные срезы значения массива, где каждый срез имеет количество измерений, заданное в SLICE. Пример перебора по одномерным срезам:

CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
  x int[];
BEGIN
  FOREACH x SLICE 1 IN ARRAY $1
  LOOP
    RAISE NOTICE 'row = %', x;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);

NOTICE:  row = {1,2,3}
NOTICE:  row = {4,5,6}
NOTICE:  row = {7,8,9}
NOTICE:  row = {10,11,12}

Перехват ошибок

По умолчанию любая ошибка, возникающая в функции PL/pgSQL, прерывает выполнение этой функции и окружающей ее транзакции. Эти ошибки можно перехватывать и исправлять, используя блок BEGIN с предложением EXCEPTION. Синтаксис представляет собой расширение обычного синтаксиса для блока BEGIN:

[ <<метка>> ]
[ DECLARE
    объявления ]
BEGIN
    операторы
EXCEPTION
    WHEN условие [ OR условие ... ] THEN
        операторы_обработчика
    [ WHEN условие [ OR условие ... ] THEN
          операторы_обработчика
      ... ]
END;

Если ошибка не возникает, эта форма блока просто выполняет все операторы, а затем управление переходит к следующему оператору после END. Но если в операторах возникает ошибка, дальнейшая их обработка прекращается, и управление переходит в список EXCEPTION. В списке ищется первое условие, соответствующее возникшей ошибке. Если совпадение найдено, выполняются соответствующие операторы_обработчика, а затем управление переходит к следующему оператору после END. Если совпадение не найдено, ошибка передается наружу, как если бы предложения EXCEPTION вообще не было: ошибку может перехватить внешний блок с EXCEPTION, или, если этого предложения нет, блок прерывает обработку функции.

В качестве условий могут задаваться любые имена из указанных в разделе Коды ошибок QHB. Название категории соответствует любой ошибке в ее категории. Специальное имя условия OTHERS (другие) соответствует всем типам ошибок, кроме QUERY_CANCELED и ASSERT_FAILURE. (Отлавливать по имени эти два типа ошибок можно, но зачастую неразумно.) Имена условий нечувствительны к регистру. Кроме того, условие ошибки можно указать кодом SQLSTATE; например эти две записи равнозначны:

WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...

Если в выбранных операторах_обработчика возникает новая ошибка, это предложение EXCEPTION не сможет ее перехватить, но ее можно передать наружу. Тогда эту ошибку сможет поймать внешнее предложение EXCEPTION.

Когда предложение EXCEPTION обнаруживает ошибку, локальные переменные функции PL/pgSQL остаются такими же, какими они были на момент возникновения ошибки, но все изменения в постоянном состоянии базы данных, выполненные в блоке, откатываются. В качестве примера рассмотрим этот фрагмент:

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
        RETURN x;
END;

Когда управление доходит до присвоения значения y, произойдет сбой с ошибкой division_by_zero. Она будет поймана предложением EXCEPTION. Значение, возвращаемое в операторе RETURN, будет увеличенным значением x, но действия команды UPDATE будут отменены. Однако команда INSERT, предшествующая блоку, не откатывается, поэтому в результате база данных будет содержать Tom Jones, а не Joe Jones.

Совет
Блок, содержащий предложение EXCEPTION, значительно увеличивает затраты на вход и выход по сравнения с обычным блоком. Поэтому не используйте EXCEPTION без необходимости.

Пример 2. Исключения с UPDATE/INSERT

В этом примере используется обработка исключений, помогающая выполнению UPDATE или INSERT, в зависимости от ситуации. В приложениях рекомендуется применять вместо этого шаблона INSERT с ON CONFLICT DO UPDATE. Этот пример служит главным образом для иллюстрации использования структур потока управления PL/pgSQL:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- сначала попытаемся изменить ключ
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- такого ключа нет, поэтому попытаемся его добавить
        -- если кто-то параллельно добавляет такой же ключ,
        -- можно получить ошибку уникальности ключа
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Ничего не делать, продолжить цикл, чтобы снова попробовать UPDATE.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

В этом коде предполагается, что ошибка unique_violation вызвана самой командой INSERT, а не, скажем, INSERT в триггерной функции таблицы. Некорректную работу также может вызвать наличие в таблице более одного уникального индекса, поскольку команда будет повторять операцию независимо от того, какой индекс вызвал ошибку. Большую надежность можно было бы получить, используя возможности, рассматриваемые далее, которые позволяют проверить, была ли перехваченная ошибка ожидаемой.

Получение информации об ошибке

Обработчикам исключений часто требуется идентифицировать конкретную возникшую ошибку. В PL/pgSQL есть два способа получить информацию о текущем исключении: специальные переменные и команда GET STACKED DIAGNOSTICS.

В обработчике исключений специальная переменная SQLSTATE содержит код ошибки, соответствующий возникшему исключению (список возможных кодов ошибок приведен в Таблице 1 раздела Коды ошибок QHB). Специальная переменная SQLERRM содержит сообщение об ошибке, связанной с этим исключением. Эти переменные не определены вне обработчиков исключений.

Также в обработчике исключений можно получить информацию о текущем исключении с помощью команды GET STACKED DIAGNOSTICS, имеющей вид:

GET STACKED DIAGNOSTICS переменная { = | := } элемент [, ... ];

Каждый элемент является ключевым словом, определяющим значение состояния, присваиваемое указанной переменной (которая должно иметь правильный тип данных, чтобы принять его). Доступные в настоящее время элементы состояния приведены в Таблице 2.

Таблица 2. Элементы диагностики ошибок

Имя	Тип	Описание
RETURNED_SQLSTATE	text	код ошибки SQLSTATE исключения
COLUMN_NAME	text	имя столбца, связанного с исключением
CONSTRAINT_NAME	text	имя ограничения, связанного с исключением
PG_DATATYPE_NAME	text	имя типа данных, связанного с исключением
MESSAGE_TEXT	text	текст основного сообщения исключения
TABLE_NAME	text	имя таблицы, связанной с исключением
SCHEMA_NAME	text	имя схемы, связанной с исключением
PG_EXCEPTION_DETAIL	text	текст подробного сообщения об исключении, если таковое имеется
PG_EXCEPTION_HINT	text	текст сообщения с подсказкой к исключению, если таковое имеется
PG_EXCEPTION_CONTEXT	text	строка(и) текста, описывающего стек вызовов во время исключения (см. подраздел Получение информации о месте выполнения)

Если исключение не установило значение для элемента, будет возвращена пустая строка.

Пример:

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- некая обработка, которая может вызвать исключение
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;

Получение информации о месте выполнения

Команда GET DIAGNOSTICS, ранее описанная в подразделе Получение статуса результата, извлекает информацию о текущем состоянии выполнения (тогда как рассмотренная выше команда GET STACKED DIAGNOSTICS выводит информацию о состоянии выполнения на момент предыдущей ошибки). Ее элемент состояния PG_CONTEXT полезен для определения текущего местоположения выполнения. PG_CONTEXT возвращает текст со строками, описывающими стек вызовов. Первая строка ссылается на текущую функцию и текущую команду GET DIAGNOSTICS. Вторая и любые последующие строки ссылаются на вызывающие функции дальше по стеку вызовов. Например:

CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
BEGIN
  RETURN inner_func();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
DECLARE
  stack text;
BEGIN
  GET DIAGNOSTICS stack = PG_CONTEXT;
  RAISE NOTICE E'--- Стек вызова ---\n%', stack;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT outer_func();

NOTICE:  --- Стек вызова ---
PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
PL/pgSQL function outer_func() line 3 at RETURN
CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
 outer_func
 ------------
           1
(1 row)

GET STACKED DIAGNOSTICS ... PG_EXCEPTION_CONTEXT возвращает похожую трассировку стека, но описывает не текущее местоположение, а то, в котором была обнаружена ошибка.

Курсоры

Вместо выполнения всего запроса целиком можно установить курсор, который инкапсулирует запрос, и затем прочитать результат запроса по несколько строк за раз. Одна из причин сделать это состоит в том, чтобы избежать переполнения памяти, когда результат содержит большое количество строк. (Хотя обычно пользователям PL/pgSQL не нужно беспокоиться об этом, поскольку в циклах FOR автоматически используются курсоры, чтобы избежать проблем с памятью). Более интересным вариантом применения является возврат ссылки на курсор, созданный функцией, что позволяет вызывающей функции прочитать строки запроса. Это обеспечивает эффективный способ возврата больших наборов строк из функций.

Объявление переменных курсора

Весь доступ к курсорам в PL/pgSQL осуществляется через переменные курсора, которые всегда имеют специальный тип данных refcursor. Один из способов создать переменную курсора — просто объявить ее как переменную типа refcursor. Другой способ — использовать синтаксис объявления курсора, который выглядит примерно так:

имя [ [ NO ] SCROLL ] CURSOR [ ( аргументы ) ] FOR запрос;

(Для совместимости с Oracle FOR можно заменить на IS.) Если указан SCROLL, курсор можно будет прокручивать назад; если указан NO SCROLL, выборки в обратном направлении будут отклонены; если этих указаний нет, возможность обратных выборок зависит от запроса. аргументы, если указаны, представляют собой разделенный запятыми список пар, состоящих из имени и типа_данных, которые определяют имена, заменяемые значениями параметров в данном запросе. Фактические значения для замены этих имен будут указаны позже, после открытия курсора.

Несколько примеров:

DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

Все эти три переменные имеют тип данных refcursor, но первую можно использовать с любым запросом, тогда как со второй уже связан полностью определенный запрос, а с последней связан параметризованный запрос. (key будет заменен на целочисленное значение параметра при открытии курсора.) Переменная curs1 называется несвязанной, поскольку она не связана ни с каким конкретным запросом.

Параметр SCROLL нельзя использовать, когда в запросе курсора применяется FOR UPDATE/SHARE. Кроме того, с запросом, включающим изменчивые функции, лучше всего использовать NO SCROLL. Реализация SCROLL предполагает, что повторное чтение вывода запроса даст согласованные результаты, которых изменчивая функция гарантировать не может.

Открытие курсоров

Прежде чем курсор можно будет использовать для извлечения строк, его необходимо открыть. (Это действие равнозначно команде SQL DECLARE CURSOR). В PL/pgSQL имеется три формы оператора OPEN, две из которых используют несвязанные переменные курсора, а третья — связанную.

Примечание
Связанные переменные курсора также можно использовать без явного открытия курсора, с помощью оператора FOR, описанного в подразделе Цикл по результату курсора.

OPEN FOR запрос

OPEN несвязанная_переменная_курсора [ [ NO ] SCROLL ] FOR запрос;

Переменная курсора открывается и получает заданный запрос для выполнения. Курсор уже не может быть открытым и должен быть объявлен как несвязанная переменная курсора (то есть как простая переменная refcursor ). Запрос должен быть командой SELECT или любой другой, что возвращает строки (например EXPLAIN). Запрос обрабатывается так же, как и другие команды SQL в PL/pgSQL: имена переменных PL/pgSQL заменяются, а план запроса кэшируется для возможного повторного использования. В запрос курсора подставляется то значение переменной PL/pgSQL, которое она имеет во время OPEN; последующие изменения в этой переменной на поведение курсора не влияют. Параметры SCROLL и NO SCROLL имеют тот же смысл, что и для связанного курсора.

Пример:

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

OPEN FOR EXECUTE

OPEN несвязанная_переменная_курсора [ [ NO ] SCROLL ] FOR EXECUTE строка_запроса
                                     [ USING выражение [, ... ] ];

Переменная курсора открывается и получает заданный запрос для выполнения. Курсор уже не может быть открытым и должен быть объявлен как несвязанная переменная курсора (то есть как простая переменная refcursor). Запрос задается как строковое выражение, так же, как в команде EXECUTE. Как обычно, это обеспечивает гибкость, поэтому план запроса может варьироваться от прогона к прогону (см. подраздел Кэширование плана), а также означает, что подстановка переменных производится не в командной строке. Как и в случае с EXECUTE, значения параметров можно вставить в динамическую команду посредством format() и USING. Параметры SCROLL и NO SCROLL имеют тот же смысл, что и для связанного курсора.

Пример:

OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

В этом примере имя таблицы вставляется в запрос через format(). Сравниваемое значение для col1 вставляется через параметр USING, поэтому его не нужно заключать в апострофы.

Открытие связанного курсора

OPEN связанная_переменная_курсора [ ( [ имя_аргумента := ] значение_аргумента [, ...] ) ];

Эта форма OPEN используется для открытия переменной курсора, с которой при объявлении был связан ее запрос. Курсор уже не может быть открытым. Список фактических выражений значений аргументов должен присутствовать тогда и только тогда, когда курсор был объявлен как принимающий аргументы. Эти значения будут подставляться в запрос.

План запроса для связанного курсора всегда считается кэшируемым; в этом случае нет эквивалента EXECUTE. Обратите внимание, что в OPEN нельзя указывать SCROLL и NO SCROLL, поскольку поведение прокрутки курсора уже было определено.

Значения аргумента можно передать посредством позиционной или именованной нотации. В позиционной нотации все аргументы указываются по порядку. В именованной нотации имя каждого аргумента отделяется от выражения аргумента с помощью :=. Как и в случае вызова функций, описанного в разделе Вызов функции, также допускается смешивание позиционной и именованной нотации.

Примеры (здесь используются объявления курсора из примеров выше):

OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);

Поскольку подстановка переменных выполняется в запросе связанного курсора, на самом деле существует два способа передачи значений в курсор: либо с явным аргументом в OPEN, либо неявно ссылаясь на переменную PL/pgSQL в запросе. Однако в связанный курсор будут подставлены только те переменные, которые были объявлены до его объявления. В любом случае передаваемое значение определяется во время OPEN. Например, вот другой способ получить тот же эффект, что и в приведенном выше примере с curs3:

DECLARE
    key integer;
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
    key := 42;
    OPEN curs4;

Использование курсоров

Когда курсор открыт, им можно манипулировать с помощью описанных здесь операторов.

Эти манипуляции не обязательно должны выполняться в той же функции, которая изначально открыла курсор. Можно вернуть из функции значение refcursor и позволить вызывающей функции работать с курсором. (Внутренне значение refcursor — это просто строковое имя так называемого портала, содержащего активный запрос для курсора. Это имя можно передавать, присваивать другим переменным refcursor и т. д., не нарушая портал.)

Все порталы неявно закрываются в конце транзакции. Поэтому значение refcursor можно использовать для обращения к открытому курсору только до конца транзакции.

FETCH

FETCH [ направление { FROM | IN } ] курсор INTO цель;

FETCH извлекает следующую строку из курсора в цель, которая может быть переменной строки, переменной записи или списком разделенных запятыми простых переменных, подобно SELECT INTO. Если следующей строки нет, в цели устанавливается NULL. Как и в случае с SELECT INTO, чтобы увидеть, была ли получена строка или нет, можно проверить специальную переменную FOUND.

Предложение направление может быть любым вариантом, разрешенным в команде SQL FETCH, кроме тех, которые могут извлекать более одной строки, а именно: NEXT, PRIOR, FIRST, LAST, ABSOLUTE число, RELATIVE число, FORWARD или BACKWARD. Отсутствие направления означает то же самое, что и указание NEXT. В формах, использующих число, оно может быть любым целочисленным выражением (в отличие от команды SQL FETCH, которая допускает только целочисленную константу). Значения направления, требующие прокрутку назад, могут вызвать ошибку, если курсор не был объявлен или открыт с параметром SCROLL.

курсор должен быть именем переменной типа refcursor, ссылающейся на портал открытого курсора.

Примеры:

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

MOVE

MOVE [ направление { FROM | IN } ] курсор;

MOVE перемещает курсор без извлечения каких-либо данных. MOVE работает точно так же, как команда FETCH, за исключением того, что он только перемещает курсор и не возвращает строку, к которой перемещается. Как и в случае с SELECT INTO, чтобы увидеть, имелась ли следующая строка для перемещения, можно проверить специальную переменную FOUND.

Примеры:

MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

UPDATE/DELETE WHERE CURRENT OF

UPDATE таблица SET ... WHERE CURRENT OF курсор;
DELETE FROM таблица WHERE CURRENT OF курсор;

Когда курсор расположен в строке таблицы, эту строку можно изменить или удалить, используя курсор для идентификации строки. Существуют ограничения на то, каким может быть запрос курсора (в частности, в нем не должно быть группировки), и лучше всего использовать в курсоре FOR UPDATE. Дополнительную информацию см. на справочной странице DECLARE.

Пример:

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

CLOSE

CLOSE курсор;

CLOSE закрывает портал, лежащий в основе открытого курсора. Это можно использовать для освобождения ресурсов до завершения транзакция, или для освобождения переменной курсора, чтобы снова ее открыть.

Пример:

CLOSE curs1;

Возврат курсоров

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

Имя портала, используемое для курсора, может быть указано программистом или сгенерировано автоматически. Чтобы указать имя портала, просто присвойте строку переменной refcursor перед его открытием. Строковое значение переменной refcursor будет использоваться OPEN в качестве имени нижележащего портала. Однако если переменная refcursor имеет значение NULL, OPEN автоматически генерирует имя, которое не конфликтует ни с каким существующим порталом, и присваивает его переменной refcursor.

Примечание
Связанная переменная курсора инициализируется в строковое значение, представляющее ее имя, поэтому имя портала совпадает с именем переменной курсора, если только программист не переопределит его присваиванием перед открытием курсора. Но несвязанная переменная курсора по умолчанию изначально имеет значение NULL, поэтому она получит автоматически сгенерированное уникальное имя, если не будет переопределена.

В следующем примере показан один из способов, которым вызывающая функция может предоставить имя курсора:

CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

В следующем примере используется автоматическая генерация имени курсора:

CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
END;
' LANGUAGE plpgsql;

-- для использования курсоров нужно быть в транзакции.
BEGIN;
SELECT reffunc2();

      reffunc2
--------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;

В следующем примере показан один из способов вернуть несколько курсоров из одной функции:

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- для использования курсоров нужно быть в транзакции.
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;

Цикл по результату курсора

Существует вариант оператора FOR, позволяющий перебирать строки, возвращаемые курсором. Синтаксис:

[ <<метка>> ]
FOR переменная_записи IN связанная_переменная_курсора [ ( [ имя_аргумента := ] значение_аргумента [, ...] ) ] LOOP
    операторы
END LOOP [ метка ];

Переменная курсора при объявлении должна быть привязана к какому-либо запросу, и уже не может быть открытой. Оператор FOR автоматически открывает курсор и снова закрывает его при выходе из цикла. Список фактических выражений значений аргументов должен присутствовать тогда и только тогда, когда курсор был объявлен как принимающий аргументы. Эти значения будут подставлены в запросе точно так же, как во время OPEN (см. подраздел Открытие связанного курсора).

Здесь переменная_записи автоматически определяется как имеющая тип record и существует только внутри цикла (любое существующее определение имени переменной в цикле игнорируется). Каждая строка, возвращаемая курсором, последовательно присваивается этой переменной записи, и выполняется тело цикла.

Управление транзакциями

В процедурах, вызываемых командой CALL, а также в блоках анонимного кода (команда DO), можно завершать транзакции с помощью команд COMMIT и ROLLBACK. После завершения транзакции с этими командами новая транзакция запускается автоматически, поэтому отдельной команды START TRANSACTION нет. (Обратите внимание, что BEGIN и END в PL/pgSQL имеют другой смысл.)

Простой пример:

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END
$$;

CALL transaction_test1();

Новая транзакция начинается с характеристик по умолчанию, таких как уровень изоляции. В случаях, когда транзакции фиксируются в цикле, может быть удобнее автоматически начинать новые транзакции с теми же характеристиками, что и предыдущая. Это реализуется командами COMMIT AND CHAIN и ROLLBACK AND CHAIN.

Управление транзакциями возможно только в вызовах CALL или DO из верхнего уровня или во вложенных CALL или DO без какой-либо промежуточной команды. Например, если стеком вызовов является CALL proc1() → CALL proc2() → CALL proc3(), то вторая и третья процедуры могут выполнять действия по управлению транзакциями. Но если стеком вызовов является CALL proc1() → SELECT func2() → CALL proc3(), то последняя процедура не может осуществлять управление транзакциями из-за промежуточного SELECT.

Циклы с курсорами требуют особого внимания. Рассмотрим этот пример:

CREATE PROCEDURE transaction_test2()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM test2 ORDER BY x LOOP
        INSERT INTO test1 (a) VALUES (r.x);
        COMMIT;
    END LOOP;
END;
$$;

CALL transaction_test2();

Обычно курсоры автоматически закрываются при фиксации транзакции. Однако курсор, созданный как часть цикла, подобного этому, автоматически преобразуется в удерживаемый курсор с помощью первой COMMIT или ROLLBACK. Это означает, что курсор полностью вычисляется в первой COMMIT или ROLLBACK а не построчно. Курсор по-прежнему автоматически удаляется после цикла, поэтому в большинстве случаев невидим для пользователя.

Команды транзакций не допускаются в циклах с курсорами, управляемых командами, которые предназначены не только для чтения (например UPDATE ... RETURNING).

Транзакция не может завершаться внутри блока с обработчиками исключений.

Ошибки и сообщения

Вывод ошибок и сообщений

Для вывода расширенных сообщений и обработки ошибок используйте оператор RAISE.

RAISE [ уровень ] 'формат' [, выражение [, ... ]] [ USING параметр = выражение [, ... ] ];
RAISE [ уровень ] имя_условия [ USING параметр = выражение [, ... ] ];
RAISE [ уровень ] SQLSTATE 'код_ошибки' [ USING параметр = выражение [, ... ] ];
RAISE [ уровень ] USING параметр = выражение [, ... ];
RAISE ;

В параметре уровень задается серьезность ошибки. Допустимые уровни: DEBUG, LOG, INFO, NOTICE, WARNING и EXCEPTION; по умолчанию используется EXCEPTION. EXCEPTION вызывает ошибку (которая обычно прерывает текущую транзакцию); другие уровни просто генерируют сообщения с различными уровнями приоритета. Будут ли сообщения определенного приоритета передаваться клиенту, записываться в журнал сервера, или и то, и другое, зависит от переменных конфигурации log_min_messages и client_min_messages. Дополнительную информацию см. в главе Конфигурация сервера.

После уровня, если он имеется, можно задать строку формата (которая должна быть простым строковым литералом, а не выражением). Строка формата определяет текст выводимого сообщения об ошибке. За строкой формата могут идти необязательные выражения аргументов, которые будут вставлены в сообщение. Внутри строки формата % заменяется строковым представлением значения следующего необязательного аргумента. Чтобы получить буквальный знак %, напишите %%. Количество аргументов должно соответствовать количеству заполнителей % в строке формата, иначе при компиляции функции возникает ошибка.

В этом примере знак % в строке будет заменен на значение v_job_id:

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
           -- Вызов функции cs_create_job(%)

Написав USING, а затем элементы параметр = выражение, можно прикрепить к отчету об ошибке дополнительную информацию. Каждое выражение может быть любым строковым выражением. Допустимые ключевые слова параметра:

MESSAGE
Устанавливает текст сообщения об ошибке. Этот параметр нельзя использовать в форме RAISE, включающей строку формата перед USING.

DETAIL
Предоставляет подробное сообщение об ошибке.

HINT
Предоставляет подсказку.

ERRCODE
Задает выводимый код ошибки (SQLSTATE) либо по имени условия, как показано в разделе Коды ошибок QHB, либо непосредственно в виде пятизначного кода SQLSTATE.

COLUMN
CONSTRAINT
DATATYPE
TABLE SCHEMA
Предоставляют имя объекта, связанного с ошибкой.

Этот пример прервет транзакцию с заданным сообщением об ошибке и подсказкой:

RAISE EXCEPTION 'Nonexistent ID --> %', user_id
              -- Несуществующий ID --> %
      USING HINT = 'Please check your user ID';
                 -- Пожалуйста, проверьте ваш пользовательский ID

Эти два примера показывают равнозначные способы установки SQLSTATE:

RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
-- Дубликат пользовательского ID

Существует второй синтаксис RAISE, в котором основным аргументом является имя условия или выводимый SQLSTATE, например:

RAISE division_by_zero;
RAISE SQLSTATE '22012';

В этом синтаксисе для предоставления пользователю сообщения об ошибке, детализации или подсказки можно использовать USING. Другой способ выполнить предыдущий пример:

RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;

Еще один вариант — написать RAISE USING или RAISE уровень USING и поместить все остальное в список USING.

Последний вариант RAISE вообще не имеет параметров. Эту форму можно использовать только внутри предложения EXCEPTION блока BEGIN; с ее помощью можно повторно вызвать ошибку, обрабатываемую в данный момент.

Если в команде RAISE EXCEPTION не указаны ни имя условия, ни SQLSTATE, по умолчанию используется ERRCODE_RAISE_EXCEPTION (P0001). Если не задан текст сообщения, по умолчанию в качестве текста сообщения используется имя условия или SQLSTATE.

Примечание
При указании кода ошибки с помощью кода SQLSTATE вы не ограничены предопределенными кодами ошибок. Можно выбрать любой код ошибки, состоящий из пяти цифр и/или букв ASCII в верхнем регистре, кроме 00000. Не рекомендуется использовать коды ошибок, заканчивающиеся тремя нулями, потому что это коды категорий, которые можно перехватить, только перехватив всю категорию.

Проверка утверждений

Оператор ASSERT представляет собой удобное сокращение для вставки отладочных проверок в функции PL/pgSQL.

ASSERT условие [, сообщение ];

условие является логическим выражением, которое, как ожидается, всегда вычисляется как true; если это так, оператор ASSERT больше ничего не делает. При результате false или NULL выдается исключение ASSERT_FAILURE. (Если ошибка возникает при вычислении условия, она выводится как обычная ошибка.)

Если предоставляется необязательное сообщение, то результат этого выражения (если это не NULL) заменяет стандартный текст сообщения об ошибке «assertion failed» (утверждение не выполнено), если условие выдает ошибку. В обычном случае, когда утверждение успешно выполняется, выражение сообщения не вычисляется.

Тестирование утверждений можно включить или выключить с помощью параметра конфигурации plpgsql.check_asserts, который принимает логическое значение; значение по умолчанию on (включен). Если этот параметр равен off (выключен), то операторы ASSERT ничего не делают.

Обратите внимание, что ASSERT предназначен для обнаружения программных ошибок, а не для сообщения об обычных ошибках. Для этого используется описанный выше оператор RAISE.

Триггерные функции

PL/pgSQL можно использовать для определения триггерных функций при изменении данных или событиях базы данных. Триггерная функция создается с помощью команды CREATE FUNCTION, объявляющей ее как функцию без аргументов и возвращающей тип trigger (для триггеров изменения данных) или event_trigger (для триггеров событий базы данных). Для описания условия, провоцирующего вызов триггерной функции, автоматически определяются специальные локальные переменные с именами вида TG_некое_имя.

Триггеры при изменении данных

Триггер изменения данных объявляется как функция без аргументов и возвращающая тип trigger. Обратите внимание, что функция должна быть объявлена без аргументов, даже если ожидается, что она будет получать некоторые аргументы, указанные в CREATE TRIGGER, — такие аргументы передаются через TG_ARGV, как описано ниже.

Когда в качестве триггера вызывается функция PL/pgSQL, в блоке верхнего уровня автоматически создается несколько специальных переменных:

NEW
Тип данных RECORD; переменная, содержащая новую строку базы данных для операций INSERT/UPDATE в триггерах уровня строки. Эта переменная равна NULL в триггерах уровня оператора и для операций DELETE.

OLD
Тип данных RECORD; переменная, содержащая старую строку базы данных для операций UPDATE/DELETE в триггерах уровня строки. Эта переменная равна NULL в триггерах уровня оператора и для операций INSERT.

TG_NAME
Тип данных name; переменная, содержащая имя фактически сработавшего триггера.

TG_WHEN
Тип данных text; строка BEFORE, AFTER или INSTEAD OF, в зависимости от определения триггера.

TG_LEVEL
Тип данных text; строка ROW или STATEMENT, в зависимости от определения триггера.

TG_OP
Тип данных text; строка INSERT, UPDATE, DELETE или TRUNCATE, указывающая, для какой операции сработал триггер.

TG_RELID
Тип данных oid; идентификатор объекта таблицы, для которой сработал триггер.

TG_RELNAME
Тип данных name; имя таблицы, для которой сработал триггер. Сейчас эта переменная устарела и в будущих релизах может исчезнуть. Используйте вместо нее TG_TABLE_NAME.

TG_TABLE_NAME
Тип данных name; имя таблицы, для которой сработал триггер.

TG_TABLE_SCHEMA
Тип данных name; имя схемы таблицы, для которой сработал триггер.

TG_NARGS
Тип данных integer; количество аргументов, переданных триггерной функции в операторе CREATE TRIGGER.

TG_ARGV[]
Тип данных массив text; аргументы из оператора CREATE TRIGGER. Индекс массива начинается с 0. При недопустимых индексах (меньше 0 либо больше или равны tg_nargs) возвращается NULL.

Триггерная функция должна возвращать либо NULL, либо значение записи/строки, точно соответствующее структуре таблицы, для которой был запущен триггер.

Триггеры BEFORE уровня строки могут вернуть NULL, чтобы указать менеджеру триггеров пропустить оставшуюся часть операции для этой строки (т. е. последующие триггеры не запускаются, и INSERT/UPDATE/DELETE для этой строки не выполняется). Если возвращается значение, отличное от NULL, то операция продолжается с этим значением строки. Возвращение значения строки, отличного от исходного значения NEW, изменяет строку, которая будет добавлена или изменена. Таким образом, если триггерной функции нужно, чтобы инициирующее действие успешно выполнялось без изменения значения строки, необходимо вернуть NEW (или эквивалентное ей значение). Чтобы изменить сохраняемую строку, можно заменить отдельные значения непосредственно в NEW и вернуть измененную NEW или создать и вернуть полностью новую запись/строку. В случае триггера BEFORE для DELETE возвращаемое значение не имеет прямого эффекта, но оно должно быть отличным от NULL, чтобы триггер мог продолжить обработку. Обратите внимание, что в триггерах DELETE NEW равна NULL, поэтому обычно возвращать ее не имеет смысла. Обычной идиомой в триггерах DELETE является возврат OLD.

Триггеры INSTEAD OF (которые всегда являются триггерами уровня строки и могут использоваться только в представлениях) могут возвращать NULL, показывая, что они не выполняли никаких изменений и что оставшуюся часть операции для этой строки следует пропустить (т. е. последующие триггеры не срабатывают, и строка не учитывается в счетчике затронутых строк для окружающей команды INSERT/UPDATE/ DELETE). В противном случае должно быть возвращено значение, отличное от NULL, показывающее, что триггер выполнил запрошенную операцию. Для операций INSERT и UPDATE возвращаемое значение должно быть NEW, которое триггерная функция может изменить для передачи командам INSERT RETURNING и UPDATE RETURNING (это также повлияет на значение строки, передаваемое любым последующим триггерам или в специальный псевдоним для обращения EXCLUDED в операторе INSERT с предложением ON CONFLICT DO UPDATE). Для операций DELETE возвращаемое значение должно быть OLD.

Возвращаемое значение триггера AFTER уровня строки или триггера BEFORE или AFTER уровня оператора всегда игнорируется; оно тоже может быть равно NULL. Однако любой из этих типов триггеров все равно может прервать всю операцию, вызвав ошибку.

В Примере 3 показан пример триггерной функции в PL/pgSQL.

Пример 3. Триггерная функция PL/pgSQL

Этот пример триггера гарантирует, что каждый раз, когда в таблице добавляется или изменяется строка, в этой строке указываются имя текущего пользователя и метка времени. Также этот триггер проверяет, что имя сотрудника задано, а зарплата является положительным значением.

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Проверить, что указаны имя сотрудника и зарплата
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Кто будет работать на нас, если им придется за это платить?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Запомнить, кто и когда изменил платежную ведомость
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE FUNCTION emp_stamp();

Другой способ регистрации изменений в таблице заключается в создании новой таблицы, содержащей строку для каждого случая добавления, изменения или удаления. Этот подход можно рассматривать как аудит изменений в таблице. В Примере 4 показан пример триггерной функции аудита в PL/pgSQL.

Пример 4. Триггерная функция PL/pgSQL для аудита

Этот пример триггера гарантирует, что любое добавление, изменение или удаление строки в таблице emp записано (т. е. аудировано) в таблице emp_audit. Текущее время и имя пользователя указываются в строке вместе с типом выполняемой операции.

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Создать строку в emp_audit для отображения операции, проведенной в emp,
        -- использовать специальную переменную TG_OP для определения типа операции.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
        END IF;
        RETURN NULL; -- результат игнорируется, поскольку это триггер AFTER
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE FUNCTION process_emp_audit();

Разновидность предыдущего примера использует представление, соединяющее основную таблицу с таблицей аудита, для отображения времени последнего изменения каждой записи. При таком подходе по-прежнему записывается полный маршрут аудита изменений в таблице, но также предоставляется упрощенное представление маршрута аудита, отображая только временную метку последнего изменения, полученную из маршрута аудита для каждой записи. В Примере 5 показан пример триггера с представлением для аудита в PL/pgSQL.

Пример 5. Триггерная функция PL/pgSQL для аудита с представлением

В этом примере используется триггер с представлением, который делает это представление изменяемым и гарантирует, что любое добавление, изменение или удаление строки в представлении записывается (т. е. аудируется) в таблице emp_audit. Текущее время и имя пользователя записываются вместе с типом выполненной операции, и представление отображает время последнего изменения каждой строки.

CREATE TABLE emp (
    empname           text PRIMARY KEY,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary            integer,
    stamp             timestamp NOT NULL
);

CREATE VIEW emp_view AS
    SELECT e.empname,
           e.salary,
           max(ea.stamp) AS last_updated
      FROM emp e
      LEFT JOIN emp_audit ea ON ea.empname = e.empname
     GROUP BY 1, 2;

CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
    BEGIN
        --
        -- Выполнить требуемую операцию в emp и создать строку в emp_audit
        -- для отображения изменения, совершенного в emp.
        --
        IF (TG_OP = 'DELETE') THEN
            DELETE FROM emp WHERE empname = OLD.empname;
            IF NOT FOUND THEN RETURN NULL; END IF;

            OLD.last_updated = now();
            INSERT INTO emp_audit VALUES('D', user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
            IF NOT FOUND THEN RETURN NULL; END IF;

            NEW.last_updated = now();
            INSERT INTO emp_audit VALUES('U', user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp VALUES(NEW.empname, NEW.salary);

            NEW.last_updated = now();
            INSERT INTO emp_audit VALUES('I', user, NEW.*);
            RETURN NEW;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
    FOR EACH ROW EXECUTE FUNCTION update_emp_view();

Одним из применений триггеров является ведение сводной таблицы для другой таблицы. Результирующую сводку можно использовать вместо исходной таблицы для определенных запросов — зачастую со значительным сокращением времени выполнения. Этот метод обычно используется в хранилищах данных, где таблицы измеренных или наблюдаемых данных (так называемые таблицы фактов) могут быть чрезвычайно большими. В Примере 6 показан пример триггерной функции в PL/pgSQL, ведущей сводную таблицу для таблицы фактов в хранилище данных.

Пример 6. Триггерная функция PL/pgSQL для ведения сводной таблицы

Схема, подробно описанная здесь, частично основана на примере Grocery Store из книги «The Data Warehouse Toolkit» Ральфа Кимбалла (Ralph Kimball).

--
-- Основные таблицы - временные периоды и факты продажи.
--
CREATE TABLE time_dimension (
    time_key                    integer NOT NULL,
    day_of_week                 integer NOT NULL,
    day_of_month                integer NOT NULL,
    month                       integer NOT NULL,
    quarter                     integer NOT NULL,
    year                        integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key                    integer NOT NULL,
    product_key                 integer NOT NULL,
    store_key                   integer NOT NULL,
    amount_sold                 numeric(12,2) NOT NULL,
    units_sold                  integer NOT NULL,
    amount_cost                 numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- Сводная таблица - продажи по периодам.
--
CREATE TABLE sales_summary_bytime (
    time_key                    integer NOT NULL,
    amount_sold                 numeric(15,2) NOT NULL,
    units_sold                  numeric(12) NOT NULL,
    amount_cost                 numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- Функция и триггер для корректировки столбцов сводки при выполнении команд
-- UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEGIN

        -- Определить увеличение/уменьшенние сумм(ы).
        IF (TG_OP = 'DELETE') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') THEN

            -- запретить изменения, затрагивающие time_key -
            -- (вероятно, это не слишком обременительно, поскольку большинство
            -- изменений будет выполняться по схеме DELETE + INSERT).
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
                                                      OLD.time_key, NEW.time_key;
            END IF;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        END IF;


        -- Внести новые значения, изменив строку сводки, или добавив новую.
        <<insert_update>>
        LOOP
            UPDATE sales_summary_bytime
                SET amount_sold = amount_sold + delta_amount_sold,
                    units_sold = units_sold + delta_units_sold,
                    amount_cost = amount_cost + delta_amount_cost
                WHERE time_key = delta_time_key;

            EXIT insert_update WHEN found;

            BEGIN
                INSERT INTO sales_summary_bytime (
                            time_key,
                            amount_sold,
                            units_sold,
                            amount_cost)
                    VALUES (
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );

                EXIT insert_update;

            EXCEPTION
                WHEN UNIQUE_VIOLATION THEN
                    -- ничего не делать
            END;
        END LOOP insert_update;

        RETURN NULL;

    END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
    FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();

INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;

Триггеры AFTER также могут использовать таблицы переходов для проверки всего набора строк, измененных оператором, вызвавшим срабатывание триггера. Команда CREATE TRIGGER присваивает имена одной или обеим таблицам переходов, а затем функция может обращаться к ним по этим именам, как если бы они были временными таблицами только для чтения. Это показано в Примере 7.

Пример 7. Аудит с таблицами переходов

Этот пример выдает те же результаты, что и Пример 4, но вместо использования триггера, срабатывающего для каждой строки, здесь используется триггер, срабатывающий один раз для каждого оператора, после сбора соответствующей информации в таблице переходов. Это может быть значительно быстрее способа с триггером строки, когда оператор, вызвавший срабатывание триггера, изменил много строк. Обратите внимание, что нужно сделать отдельное объявление триггера для каждого типа события, так как предложения REFERENCING для каждого случая должны быть разными. Но это не мешает при желании использовать одну функцию триггера. (Возможно, на практике было бы лучше использовать три отдельные функции и избегать проверок TG_OP во время выполнения.)

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Создать строки в emp_audit для отображения операций, проведенных в emp,
        -- использовать специальную переменную TG_OP для определения типа операций.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit
                SELECT 'D', now(), user, o.* FROM old_table o;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit
                SELECT 'U', now(), user, n.* FROM new_table n;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit
                SELECT 'I', now(), user, n.* FROM new_table n;
        END IF;
        RETURN NULL; -- результат игнорируется, поскольку это триггер AFTER
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit_ins
    AFTER INSERT ON emp
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_upd
    AFTER UPDATE ON emp
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
    AFTER DELETE ON emp
    REFERENCING OLD TABLE AS old_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

Триггеры событий

PL/pgSQL можно использовать для определения триггеров событий. QHB требует, чтобы функция, вызываемая как триггер события, была объявлена как функция без аргументов и возвращающая тип event_trigger.

Когда функция PL/pgSQL вызывается как триггер события, в блоке верхнего уровня автоматически создается несколько специальных переменных:

TG_EVENT
Тип данных text; строка, представляющая событие, для которого срабатывает триггер.

TG_TAG
Тип данных text; переменная, содержащая тег команды, для которого срабатывает триггер.

В Примере 8 показан пример триггерной функции события в PL/pgSQL.

Пример 8. Триггерная функция события PL/pgSQL

Этот пример триггера просто вызывает сообщение NOTICE каждый раз, когда выполняется поддерживаемая команда.

CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();

Внутренняя структура PL/pgSQL

В этом разделе рассматриваются некоторые детали реализации, которые зачастую важно знать пользователям PL/pgSQL.

Подстановка переменных

Операторы SQL и выражения в функции PL/pgSQL могут ссылаться на переменные и параметры этой функции. За кулисами PL/pgSQL заменяет параметры запроса для таких ссылок. Параметры будут заменены только в тех местах, где они синтаксически разрешены. В качестве крайнего случая рассмотрим пример плохого стиля программирования:

INSERT INTO foo (foo) VALUES (foo(foo));

Первое вхождение foo синтаксически должно быть именем таблицы, поэтому оно не будет заменено, даже если функция имеет переменную с именем foo. Второе вхождение должно быть именем столбца этой таблицы, поэтому оно также не будет заменено. Аналогичным образом третье вхождение должно быть именем функции, поэтому заменено не будет. Только последнее вхождение является кандидатом на ссылку на переменную функции PL/pgSQL.

Еще один способ понять это — подстановка переменной может вставлять в команду SQL только значение данных; она не может динамически менять объекты базы данных, на которые ссылается команда. (Для этого нужно собрать командную строку динамически, как описано в подразделе Выполнение динамических команд.)

Поскольку имена переменных синтаксически ничем не отличаются от имен столбцов таблицы, в выражениях, которые также ссылаются на таблицы, может возникнуть неоднозначность: данное имя является ссылкой на столбец таблицы или на переменную? Давайте изменим предыдущий пример на

INSERT INTO dest (col) SELECT foo + bar FROM src;

Здесь dest и src должны быть именами таблиц, а col должен быть столбцом dest, но foo и bar вполне могут быть либо переменными функции, либо столбцами src.

По умолчанию PL/pgSQL сообщит об ошибке, если имя в операторе SQL может ссылаться либо на переменную, либо на столбец таблицы. Эту проблему можно решить, переименовав переменную или столбец, или дополнив неоднозначную ссылку, или указав PL/pgSQL, какую интерпретацию выбрать.

Самое простое решение — переименовать переменную или столбец. Общим правилом кодирования является использование для переменных PL/pgSQL соглашения об именах, отличного от соглашения для имен столбцов. Например, если вы последовательно называете переменные функции v_имя, тогда как ни одно из имен столбцов не начинается с v_, никаких конфликтов не будет.

В качестве альтернативы можно для ясности дополнить неоднозначные ссылки. В приведенном выше примере src.foo будет однозначной ссылкой на столбец таблицы. Чтобы создать однозначную ссылку на переменную, объявите ее в помеченном блоке и используйте эту метку (см. раздел Структура PL/pgSQL). Например,

<<block>>
DECLARE
    foo int;
BEGIN
    foo := ...;
    INSERT INTO dest (col) SELECT block.foo + bar FROM src;

Здесь block.foo означает переменную, даже если в src есть столбец foo. Параметры функции, а также специальные переменные, например FOUND, можно дополнить именем функции, поскольку они неявно объявляются во внешнем блоке, помеченном именем функции.

Иногда нецелесообразно исправлять все неоднозначные ссылки в большом теле кода PL/pgSQL. В таких случаях можно указать, что PL/pgSQL должен разрешать неоднозначные ссылки как переменные или как столбцы таблицы (последнее совместимо с некоторыми другими системами, например с Oracle).

Чтобы изменить это поведение в масштабе всей системы, установите для параметра конфигурации plpgsql.variable_conflict один из параметров: error, use_variable или use_column (где error — заводская установка по умолчанию). Этот параметр влияет на последующие компиляции операторов в функциях PL/pgSQL, но не на операторы, уже скомпилированные в текущем сеансе. Поскольку изменение этого параметра может привести к неожиданным изменениям в поведении функций PL/pgSQL, его может изменить только суперпользователь.

Также можно установить поведение для каждой функции, вставив в начале текста функции одну из этих специальных команд:

#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

Эти команды влияют только на функцию, в которой они написаны, и переопределяют значение plpgsql.variable_conflict. Например:

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    #variable_conflict use_variable
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = curtime, comment = comment
          WHERE users.id = id;
    END;
$$ LANGUAGE plpgsql;

В команде UPDATE curtime, comment и id будут ссылаться на переменную и параметры функции независимо от того, есть ли у users столбцы с этими именами. Обратите внимание, что нам пришлось дополнить ссылку на users.id в предложении WHERE, чтобы она ссылалась на столбец таблицы. Но нам не нужно было дополнять ссылку на comment как цель в списке UPDATE, потому что синтаксически это должен быть столбец users. Мы можем написать ту же функцию, но не зависящую от значения variable_conflict, следующим образом:

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    <<fn>>
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment
          WHERE users.id = stamp_user.id;
    END;
$$ LANGUAGE plpgsql;

Подстановка переменных не происходит в командной строке, переданной EXECUTE или одному из ее вариантов. Если вам нужно вставить значение переменной в подобную команду, сделайте это при построении строкового значения или используйте USING, как показано в подразделе Выполнение динамических команд.

В настоящее время замена переменных работает только в командах SELECT, INSERT, UPDATE и DELETE, а также в командах, содержащих одну из них (например, EXPLAIN и CREATE TABLE ... AS SELECT), поскольку основной механизм SQL допускает параметры запроса только в этих командах. Чтобы использовать непостоянное имя или значение в других типах операторов (обычно называемых служебными операторами), необходимо создать служебный оператор в виде строки и выполнить его (EXECUTE).

Кэширование плана

Интерпретатор PL/pgSQL разбирает исходный текст функции и создает внутреннее двоичное дерево инструкций при первом вызове функции (в каждом сеансе). Дерево инструкций полностью переводит структуру операторов PL/pgSQL, но отдельные выражения и команды SQL, используемые в функции, переводятся не сразу.

Поскольку каждое выражение и команда SQL сначала выполняются в функции, интерпретатор PL/pgSQL разбирает и анализирует команду для создания подготовленного оператора, используя функцию SPI_prepare менеджера SPI. Последующие посещения этого выражения или команды повторно используют подготовленный оператор. Таким образом, функция с редко посещаемыми условными путями кода никогда не будет нести затраты на анализ тех команд, которые никогда не выполняются в текущем сеансе. Недостатком является то, что ошибки в определенном выражении или команде нельзя обнаружить, пока выполнение не дойдет до этой части функции. (Тривиальные синтаксические ошибки будут обнаружены во время первого разбора, но что-то более серьезное будет выявлено только во время выполнения.)

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

Поскольку PL/pgSQL сохраняет подготовленные операторы и иногда планы выполнения таким образом, команды SQL, которые находятся непосредственно в функции PL/pgSQL, должны при каждом выполнении ссылаться на одни и те же таблицы и столбцы; то есть в команде SQL нельзя использовать параметр в качестве имени таблицы или столбца. Чтобы обойти это ограничение, можно создать динамические команды с помощью оператора PL/pgSQL EXECUTE — ценой проведения нового разбора и синтаксического анализа и построения нового плана при каждом выполнении.

Изменчивая природа переменных записи представляет другую проблему в этой связи. Когда поля переменной записи используются в выражениях или операторах, типы данных этих полей не должны изменяться от одного вызова функции к следующему, поскольку каждое выражение будет анализироваться с использованием типа данных, который присутствует при первой обработке выражения. При необходимости, чтобы обойти эту проблему, можно воспользоваться командой EXECUTE.

Если одна функция используется как триггер для нескольких таблиц, PL/pgSQL подготавливает и кэширует операторы независимо для каждой такой таблицы — то есть существует кэш для каждой комбинации триггерной функции и таблицы, а не только для каждой функции. Это устраняет некоторые проблемы, касающиеся различных типов данных; например, триггерная функция сможет успешно работать со столбцом с именем key, даже если он имеет разные типы в разных таблицах.

Аналогично функции с полиморфными типами аргументов имеют отдельный кэш операторов для каждой комбинации фактических типов аргументов, для которых они были вызваны, так что различия типов данных не вызывают неожиданных сбоев.

Кэширование операторов иногда может оказать неожиданное влияние на интерпретацию чувствительных ко времени значений. Например, есть разница между тем, что делают эти две функции:

CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
    BEGIN
        INSERT INTO logtable VALUES (logtxt, 'now');
    END;
$$ LANGUAGE plpgsql;

и:

CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
    DECLARE
        curtime timestamp;
    BEGIN
        curtime := 'now';
        INSERT INTO logtable VALUES (logtxt, curtime);
    END;
$$ LANGUAGE plpgsql;

В случае logfunc1 главный анализатор QHB при анализе INSERT знает, что строка 'now' должна интерпретироваться как timestamp, потому что целевой столбец logtable относится к этому типу. Таким образом, при анализе INSERT 'now' будет преобразована в постоянную timestamp, а затем будет использоваться во всех вызовах logfunc1 в течение всего сеанса. Несомненно, это не то, что хотел программист. Было бы лучше использовать функцию now() или current_timestamp.

В случае logfunc2 основной анализатор QHB не знает, каким должен быть тип 'now', и поэтому возвращает значение типа text, содержащее строку now. Во время последующего присвоения локальной переменной curtime интерпретатор PL/pgSQL приводит эту строку к типу timestamp, вызывая для преобразования функции text_out и timestamp_in. Таким образом, вычисленная метка времени обновляется при каждом выполнении, как и ожидает программист. Но, несмотря на то, что все работает должным образом, это не очень эффективно, поэтому все равно было бы лучше воспользоваться функцией now().

Советы по разработке на 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)

Портирование из Oracle PL/SQL

В этом разделе объясняются различия между языком PL/pgSQL QHB и языком PL/SQL Oracle, чтобы помочь разработчикам, которые переносят приложения из Oracle® в QHB.

PL/pgSQL во многом похож на PL/SQL. Это блочно-структурированный императивный язык, где все переменные должны быть объявлены. Присвоения, циклы и условия схожи. Основные различия, которые следует учитывать при переносе с PL/SQL на PL/pgSQL, следующие:

Если имя, фигурирующее в команде SQL, может быть как именем столбца таблицы, используемой в этой команде, так и ссылкой на переменную функции, PL/SQL обрабатывает его как имя столбца. По умолчанию PL/pgSQL выдаст ошибку, предъявляя претензию о неоднозначности имени. Чтобы изменить это поведение на принятое в PL/SQL, можно указать plpgsql.variable_conflict = use_column, как описано в подразделе Подстановка переменных. Как правило, лучше вообще избегать подобных неоднозначностей, но если вам нужно портировать большой объем кода, зависящего от этого поведения, установка variable_conflict может быть лучшим решением.
В QHB тело функции должно быть записано как строковый литерал. Поэтому следует использовать долларовые кавычки или экранировать апострофы в теле функции. (См. подраздел Обработка кавычек.)
Имена типов данных часто нуждаются в преобразовании. Например, в Oracle строковые значения обычно объявляются как имеющие тип varchar2, не являющийся стандартным типом SQL. В QHB вместо него следует использовать тип varchar или text. Аналогичным образом нужно будет заменить тип number на numeric или использовать другой числовой тип, если есть более подходящий.
Для организации функций в группы используйте схемы вместо пакетов.
Поскольку пакетов нет, переменных уровня пакета тоже нет. Это несколько раздражает. Вместо этого можно хранить состояние сеанса во временных таблицах.
Целочисленные циклы FOR с REVERSE работают по-разному: PL/SQL ведет обратный отсчет от второго числа к первому, тогда как PL/pgSQL ведет обратный его от первого числа ко второму, поэтому при переносе границы цикла нужно поменять местами. Это неприятная несовместимость, но вряд ли что-то изменится. (См. подраздел FOR (целочисленный вариант).)
Циклы FOR по запросам (кроме курсоров) также работают по-разному: целевые переменные должны быть объявлены, тогда как PL/SQL всегда объявляет их неявно. Преимущество этого состоит в том, что значения переменных доступны и после выхода из цикла.
Существуют различные нотационные различия при использовании переменных курсора.

Примеры портирования

В Примере 9 показано, как портировать простую функцию из PL/SQL в PL/pgSQL.

Пример 9. Портирование простой функции из PL/SQL в PL/pgSQL

Функция Oracle PL/SQL:

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
                                                  v_version varchar2)
RETURN varchar2 IS
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
show errors;

Давайте рассмотрим эту функцию и посмотрим на различия по сравнению с PL/pgSQL:

Имя типа varchar2 нужно заменить на varchar или text. В примерах в этом подразделе мы будем использовать varchar, но зачастую text является лучшим выбором, если вам не нужны определенные ограничения длины строк.
Ключевое слово RETURN в прототипе функции (но не в ее теле) в QHB становится RETURNS. Кроме того, IS становится AS, и нужно добавить предложение LANGUAGE, поскольку PL/pgSQL не является единственным возможным языком функций.
В QHB тело функции считается строковым литералом, поэтому его нужно заключать в кавычки или знаки доллара. Это заменяет завершающий / в подходе Oracle.
В QHB не существует команды show errors, но она и не нужна, поскольку ошибки выводятся автоматически.

Вот как будет выглядеть эта функция при портировании на QHB:

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
RETURNS varchar AS $$
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE plpgsql;

В Примере 10 показано, как портировать функцию, создающую другую функцию, и как справиться с возникающими проблемами с кавычками.

Пример 10. Портирование функции, создающей другую функцию, из PL/SQL в PL/pgSQL

Следующая процедура извлекает строки из SELECT и строит большую функцию, результаты которой ради эффективности возвращаются в операторах IF.

Это версия Oracle:

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
    CURSOR referrer_keys IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
    func_cmd VARCHAR(4000);
BEGIN
    func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
                 v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN';

    FOR referrer_key IN referrer_keys LOOP
        func_cmd := func_cmd ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ''' || referrer_key.key_string
          || ''' THEN RETURN ''' || referrer_key.referrer_type
          || '''; END IF;';
    END LOOP;

    func_cmd := func_cmd || ' RETURN NULL; END;';

    EXECUTE IMMEDIATE func_cmd;
END;
/
show errors;

А вот как эта функция может выглядеть в QHB:

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$
DECLARE
    referrer_keys CURSOR IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
    func_body text;
    func_cmd text;
BEGIN
    func_body := 'BEGIN';

    FOR referrer_key IN referrer_keys LOOP
        func_body := func_body ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ' || quote_literal(referrer_key.key_string)
          || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
          || '; END IF;' ;
    END LOOP;

    func_body := func_body || ' RETURN NULL; END;';

    func_cmd :=
      'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
                                                        v_domain varchar,
                                                        v_url varchar)
        RETURNS varchar AS '
      || quote_literal(func_body)
      || ' LANGUAGE plpgsql;' ;

    EXECUTE func_cmd;
END;
$func$ LANGUAGE plpgsql;

Обратите внимание, что тело функции строится отдельно и передается через quote_literal для удвоения всех кавычек. Этот метод необходим, потому что мы не можем безопасно использовать долларовые кавычки для определения новой функции: мы не знаем наверняка, какие строки будут интерполированы из поля referrer_key.key_string. (Здесь предполагается, что referrer_key.kind точно всегда имеет значение host, domain или url, но referrer_key.key_string может быть чем угодно, в частности, содержать знаки доллара). На самом деле, эта функция улучшена по сравнению с оригиналом на Oracle, потому что она не будет генерировать неработающий код, если в полях referrer_key.key_string или referrer_key.referrer_type содержатся кавычки.

В Примере 11 показано, как портировать функцию с выходными параметрами (OUT), которая манипулирует строками. QHB не имеет встроенной функции instr, но ее можно создать, используя комбинацию других функций. В подразделе Приложение описана реализация instr в PL/pgSQL, которой можно воспользоваться для упрощения портирования.

Пример 11. Портирование процедуры с манипулированием строками и параметрами OUT из PL/SQL в PL/pgSQL

Следующая процедура Oracle PL/SQL используется для синтаксического анализа URL-адреса и возврата нескольких элементов (хост, путь и запрос).

Это версия Oracle:

CREATE OR REPLACE PROCEDURE cs_parse_url(
    v_url IN VARCHAR2,
    v_host OUT VARCHAR2,  -- Это будет возвращено
    v_path OUT VARCHAR2,  -- И это тоже
    v_query OUT VARCHAR2) -- И это
IS
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;

Вот возможная конвертация в PL/pgSQL:

CREATE OR REPLACE FUNCTION cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- Это будет возвращено
    v_path OUT VARCHAR,  -- И это тоже
    v_query OUT VARCHAR) -- И это
AS $$
DECLARE
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
$$ LANGUAGE plpgsql;

Эту функцию можно использовать так:

SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');

В Примере 12 показано, как портировать процедуру, использующую многочисленные функциональные возможности, характерные для Oracle.

Пример 12. Портирование процедуры из PL/SQL в PL/pgSQL

Версия Oracle:

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
    a_running_job_count INTEGER;
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        COMMIT; -- освободить блокировку
        raise_application_error(-20000,
                 'Unable to create a new job: a job is currently running.');
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN dup_val_on_index THEN NULL; -- ничего не делать, если оно уже существует
    END;
    COMMIT;
END;
/
show errors

Вот как можно портировать эту процедуру в PL/pgSQL:

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$
DECLARE
    a_running_job_count integer;
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        COMMIT; -- освободить блокировку
        RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- (1)
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN unique_violation THEN -- (2)
            -- ничего не делать, если оно уже существует
    END;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

(1): Синтаксис RAISE значительно отличается от оператора Oracle, хотя основной вариант RAISE имя_исключения работает аналогично.

(2): Имена исключений, поддерживаемые PL/pgSQL, отличаются от имен Oracle. Набор встроенных имен исключений намного больше (см. раздел Коды ошибок QHB). В настоящее время нет способа объявить пользовательские имена исключений, хотя вместо них можно вызывать ошибку с выбранными пользователем значениями SQLSTATE.

На что еще обратить внимание

В этом разделе описываются еще несколько вещей, на которые нужно обратить внимание при портировании функций Oracle PL/SQL в QHB.

Неявный откат после исключений

В PL/pgSQL при захвате исключения предложением EXCEPTION все изменения в базе данных, начиная с блока BEGIN, автоматически откатываются. То есть поведение равнозначно тому, что вы получите в Oracle при:

BEGIN
    SAVEPOINT s1;
    ... code here ...
EXCEPTION
    WHEN ... THEN
        ROLLBACK TO s1;
        ... code here ...
    WHEN ... THEN
        ROLLBACK TO s1;
        ... code here ...
END;

Если вы конвертируете процедуру Oracle, использующую SAVEPOINT и ROLLBACK TO в этом стиле, задача проста: просто опустите эти операторы. Если же у вас есть процедура, использующая SAVEPOINT и ROLLBACK TO по-другому, потребуется хорошенько поразмыслить.

EXECUTE

Версия EXECUTE для PL/pgSQL работает аналогично версии PL/SQL, но не забывайте использовать quote_literal и quote_ident, как описано в подразделе Выполнение динамических команд. Без этих функций конструкции типа EXECUTE 'SELECT * FROM $1'; не будут надежно работать.

Оптимизация функций PL/pgSQL

QHB предоставляет два модификатора при создании функции для оптимизации выполнения: «изменчивость» (всегда ли функция возвращает один и тот же результат, если задавать ей одни и те же аргументы) и «строгость» (возвращает ли функция NULL, если какой-либо аргумент равен NULL). Подробную информацию см. на справочной странице команды CREATE FUNCTION.

При использовании этих атрибутов оптимизации оператор CREATE FUNCTION может выглядеть примерно так:

CREATE FUNCTION foo(...) RETURNS integer AS $$
...
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

Приложение

В этом подразделе содержится код для установки Oracle-совместимых функций instr которые можно использовать для упрощения процесса портирования.

-
-- функции instr, имитирующие аналоги в Oracle
-- Синтаксис: instr(string1, string2 [, n [, m]])
-- где [] означают необязательные параметры.
--
-- Искать в string1, начиная с n-го символа, m-е вхождение
-- string2.  Если n — отрициальное число, искать в обратном направлении, начиная с
-- abs(n)-го символа от конца string1.
-- Если n не передано, предполагать 1 (поиск начинается с первого символа).
-- Если m не передано, предполагать 1 (найти первое вхождение).
-- Возвращает начальный индекс string2 в string1 или 0, если string2 не найдена.
--

CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
BEGIN
    RETURN instr($1, $2, 1);
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
                      beg_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN
        temp_str := substring(string FROM beg_index);
        pos := position(string_to_search_for IN temp_str);

        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN pos + beg_index - 1;
        END IF;
    ELSIF beg_index < 0 THEN
        ss_length := char_length(string_to_search_for);
        length := char_length(string);
        beg := length + 1 + beg_index;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            IF string_to_search_for = temp_str THEN
                RETURN beg;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
                      beg_index integer, occur_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;
BEGIN
    IF occur_index <= 0 THEN
        RAISE 'argument ''%'' is out of range', occur_index
          USING ERRCODE = '22003';
    END IF;

    IF beg_index > 0 THEN
        beg := beg_index - 1;
        FOR i IN 1..occur_index LOOP
            temp_str := substring(string FROM beg + 1);
            pos := position(string_to_search_for IN temp_str);
            IF pos = 0 THEN
                RETURN 0;
            END IF;
            beg := beg + pos;
        END LOOP;

        RETURN beg;
    ELSIF beg_index < 0 THEN
        ss_length := char_length(string_to_search_for);
        length := char_length(string);
        beg := length + 1 + beg_index;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            IF string_to_search_for = temp_str THEN
                occur_number := occur_number + 1;
                IF occur_number = occur_index THEN
                    RETURN beg;
                END IF;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;