41.6. Структуры управления#

41.6. Структуры управления

41.6. Структуры управления #

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

41.6.1. Возвращение из функции #

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

41.6.1.1. RETURN #

RETURN expression;

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

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

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

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

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

Некоторые примеры:

-- functions returning a scalar type
RETURN 1 + 2;
RETURN scalar_var;

-- functions returning a composite type
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- must cast columns to correct types

41.6.1.2. RETURN NEXT и RETURN QUERY #

RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [USING expression [, ... ]];

Когда функция PL/pgSQL объявляется с возвращаемым значением SETOF sometype, процедура отличается немного. В этом случае, отдельные элементы для возврата указываются последовательностью команд 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 sometype, когда есть только один выходной параметр типа sometype, чтобы создать функцию, возвращающую набор с выходными параметрами.

Вот пример функции, использующей 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
        -- can do some processing here
        RETURN NEXT r; -- return current row of 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);

    -- Since execution is not finished, we can check whether rows were returned
    -- and raise exception if not.
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No flight at %.', $1;
    END IF;

    RETURN;
 END;
$BODY$
LANGUAGE plpgsql;

-- Returns available flights or raises exception if there are no
-- available flights.
SELECT * FROM get_available_flightid(CURRENT_DATE);

Примечание

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

41.6.2. Возвращение из процедуры #

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

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

41.6.3. Вызов процедуры #

A PL/pgSQL функция, процедура или DO блок могут вызывать процедуру с помощью 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;  -- prints 15
END;
$$;

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

41.6.4. Условия #

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

41.6.4.1. IF-THEN #

IF boolean-expression THEN
    statements
END IF;

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

Пример:

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

41.6.4.2. IF-THEN-ELSE #

IF boolean-expression THEN
    statements
ELSE
    statements
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;

41.6.4.3. IF-THEN-ELSIF #

IF boolean-expression THEN
    statements
[ЕСЛИ boolean-expression ТО
    statements
[ ЕСЛИ boolean-expression ТО
    statements
    ...
]]
[ ELSE
    statements ]
END IF;

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

Вот пример:

IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    -- hmm, the only other possibility is that number is 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, когда есть много альтернатив.

41.6.4.4. Простой CASE #

CASE search-expression
    WHEN expression [, expression [ ... ]] THEN
      statements
  [WHEN expression[, expression [ ... ]] THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

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

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

CASE x
    WHEN 1, 2 THEN
        msg := 'one or two';
    ELSE
        msg := 'other value than one or two';
END CASE;

41.6.4.5. Искомый CASE #

CASE
    WHEN boolean-expression THEN
      statements
  [ WHEN boolean-expression THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

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

Вот пример:

CASE
    WHEN x BETWEEN 0 AND 10 THEN
        msg := 'value is between zero and ten';
    WHEN x BETWEEN 11 AND 20 THEN
        msg := 'value is between eleven and twenty';
END CASE;

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

41.6.5. Простые циклы #

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

41.6.5.1. LOOP #

[ <<label>> ]
LOOP
    statements
END LOOP [ label ];

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

41.6.5.2. EXIT #

EXIT [ label ] [ WHEN boolean-expression ];

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

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

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

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

Примеры:

LOOP
    -- some computations
    IF count > 0 THEN
        EXIT;  -- exit loop
    END IF;
END LOOP;

LOOP
    -- some computations
    EXIT WHEN count > 0;  -- same result as previous example
END LOOP;

<<ablock>>
BEGIN
    -- some computations
    IF stocks > 100000 THEN
        EXIT ablock;  -- causes exit from the BEGIN block
    END IF;
    -- computations here will be skipped when stocks > 100000
END;

41.6.5.3. CONTINUE #

CONTINUE [ label ] [ WHEN boolean-expression ];

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

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

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

Примеры:

LOOP
    -- some computations
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- some computations for count IN [50 .. 100]
END LOOP;

41.6.5.4. WHILE #

[ <<label>> ]
WHILE boolean-expression LOOP
    statements
END LOOP [ label ];

Оператор WHILE повторяет последовательность операторов до тех пор, пока выражение boolean-expression оценивается как истинное. Выражение проверяется непосредственно перед каждым входом в тело цикла.

Например:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- some computations here
END LOOP;

WHILE NOT done LOOP
    -- some computations here
END LOOP;

41.6.5.5. FOR(Целочисленный Вариант) #

[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
    statements
END LOOP [ label ];

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

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

FOR i IN 1..10 LOOP
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- i will take on the values 10,8,6,4,2 within the loop
END LOOP;

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

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

41.6.6. Циклический проход по результатам запроса #

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

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

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

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

        -- Now "mviews" has one record with information about the materialized view

        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, последнее присвоенное значение строки остается доступным после цикла.

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

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

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

[ <<label>> ]
FOR target IN EXECUTE text_expression [USING expression [, ... ]] LOOP
    statements
END LOOP [ label ];

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

Другой способ указать запрос, результаты которого должны быть перебраны, - это объявить его как курсор. Об этом описано в Раздел 41.7.4.

41.6.7. Цикл по массивам #

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

[ <<label>> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
    statements
END LOOP [ label ];

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

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;

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

С положительным значением SLICE, FOREACH перебирает срезы массива, а не отдельные элементы. Значение SLICE должно быть целочисленной константой, не превышающей количество измерений массива. Переменная target должна быть массивом, и она получает последовательные срезы значения массива, где каждый срез имеет количество измерений, указанное в 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}

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

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

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [WHEN condition [ OR condition ... ] THEN
          handler_statements
      ...]
END;

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

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

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

Если происходит новая ошибка внутри выбранных handler_statements, она не может быть перехвачена этим 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 без необходимости.

Пример 41.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
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

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

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


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

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

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

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

GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ];

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

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

ИмяТипОписание
RETURNED_SQLSTATEtextкод SQLSTATE ошибки исключения
COLUMN_NAMEtextимя колонки, связанной с исключением
CONSTRAINT_NAMEtextимя ограничения, связанного с исключением
PG_DATATYPE_NAMEtextимя связанного с исключением типа данных
MESSAGE_TEXTtextтекст основного сообщения об исключении
TABLE_NAMEtextимя таблицы, связанной с исключением
SCHEMA_NAMEtextимя схемы, связанной с исключением
PG_EXCEPTION_DETAILtextтекст детального сообщения об исключении, если есть
PG_EXCEPTION_HINTtextтекст подсказки сообщения об исключении, если есть
PG_EXCEPTION_CONTEXTtextстрок(и) текста, описывающие стек вызовов в момент возникновения исключения (см. Раздел 41.6.9)

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

Вот пример:

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- some processing which might cause an exception
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;

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

Команда GET DIAGNOSTICS, описанная ранее в разделе Раздел 41.5.5, извлекает информацию о текущем состоянии выполнения (в то время как команда 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'--- Call Stack ---\n%', stack;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT outer_func();

NOTICE:  --- Call Stack ---
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 возвращает тот же тип трассировки стека, но описывает местоположение, в котором была обнаружена ошибка, а не текущее местоположение.