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

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