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

примечание

Эта страница переведена при помощи нейросети GigaChat.

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

Команды, возвращающие значения из функции

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

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

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

Завершение процедуры

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

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

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

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

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

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

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

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 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;

IF-THEN-ELSE

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

IF-THEN-ELSE операторы добавляют к IF-THEN, позволяя указать альтернативный набор операторов, которые должны быть выполнены, если условие не является истинным. Обратите внимание, что это включает случай, когда условие оценивается как 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 boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
    ...
]
]
[ ELSE
    statements ]
END IF;

Иногда существует более двух альтернатив. IF-THEN-ELSIF предоставляет удобный метод проверки нескольких альтернатив по очереди. Условия IF проверяются последовательно до тех пор, пока не будет найдено первое истинное условие. Затем выполняются связанные с ним операторы, после чего управление передается следующему оператору после END IF. Любые последующие условия IF не проверяются. Если ни одно из условий IF не является истинным, то выполняется блок 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 при наличии множества альтернатив.

Простой 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 не оцениваются. Если совпадение не найдено, выполняются выражения ELSE statements, но если ELSE отсутствует, то возникает исключение CASE_NOT_FOUND.

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

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

CASE с перебором условий

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

Эта форма CASE обеспечивает условное выполнение на основе истинности логических выражений. Каждое выражение 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 приводит к ошибке, а не ничего не делает.

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

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

LOOP

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

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

EXIT

EXIT [ label ] [ WHEN boolean-expression ];

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

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

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

При использовании с блоком BEGIN, EXIT передает управление следующему оператору после конца блока. Обратите внимание, что для этого должна быть использована метка. Немаркированный EXIT никогда не считается соответствующим блоку BEGIN. Это изменение от версий PostgreSQL до 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;

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;

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;

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

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

Эта форма FOR создает цикл, который проходит через диапазон целочисленных значений. Переменная name автоматически определяется как тип integer и существует только внутри цикла (любое существующее определение имени переменной игнорируется внутри цикла). Два выражения, задающие нижнюю и верхнюю границы диапазона, оцениваются один раз при входе в цикл. Если предложение 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), тело цикла вообще не выполняется. Ошибка не возникает.

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

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

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

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

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

В качестве запроса в этом типе оператора FOR может задаваться любая команда SQL, возвращающая строки. Чаще всего это SELECT, но также можно использовать и INSERT, UPDATE, DELETE или MERGE с предложением RETURNING. Кроме того, возможно применение и некоторых служебных команд, например EXPLAIN.

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

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

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

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

Еще один способ указать запрос, результаты которого должны быть перебраны – это объявить его курсором. Это описано в разделе «Проход по результату курсора».

Цикл по элементам массива

Цикл 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}

Обработка ошибок

По умолчанию любая ошибка, возникающая в функции 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. В списке ищется первое совпадение с ошибкой, которая произошла. Если найдено совпадение, выполняются соответствующие handler_statements, а затем управление передается следующему оператору после END. Если совпадение не найдено, ошибка распространяется так, как будто предложение EXCEPTION вообще отсутствует: ошибку может поймать блок с EXCEPTION, или, если его нет, он прерывает обработку функции.

Имена condition могут быть любыми из тех, что показаны в «Приложение А. Коды ошибок PostgreSQL». Имя категории соответствует любой ошибке в этой категории. Специальное имя условия 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 без необходимости.

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

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

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

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

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

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

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

Элементы диагностики ошибок:

Имя	Тип	Описание
`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
  -- 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;

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

Команда 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'--- 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 возвращает тот же тип трассировки стека, но описывает место, где была обнаружена ошибка, а не текущее местоположение.

Команды, возвращающие значения из функции​

RETURN​

RETURN NEXT и RETURN QUERY​

Завершение процедуры​

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

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

IF-THEN​

IF-THEN-ELSE​

IF-THEN-ELSIF​

Простой CASE​

CASE с перебором условий​

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

LOOP​

EXIT​

CONTINUE​

WHILE​

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

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

Цикл по элементам массива​

Обработка ошибок​

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

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

Команды, возвращающие значения из функции

RETURN

RETURN NEXT и RETURN QUERY

Завершение процедуры

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

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

IF-THEN

IF-THEN-ELSE

IF-THEN-ELSIF

Простой CASE

CASE с перебором условий

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

LOOP

EXIT

CONTINUE

WHILE

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

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

Цикл по элементам массива

Обработка ошибок

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

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