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 NEXTexpression
; RETURN QUERYquery
; RETURN QUERY EXECUTEcommand-string
[USINGexpression
[, ... ]];
Когда функция 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
#
IFboolean-expression
THENstatements
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
#
IFboolean-expression
THENstatements
ELSEstatements
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
#
IFboolean-expression
THENstatements
[ЕСЛИboolean-expression
ТОstatements
[ ЕСЛИboolean-expression
ТОstatements
... ]] [ ELSEstatements
] 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
#
CASEsearch-expression
WHENexpression
[,expression
[ ... ]] THENstatements
[WHENexpression
[,expression
[ ... ]] THENstatements
... ] [ ELSEstatements
] 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 WHENboolean-expression
THENstatements
[ WHENboolean-expression
THENstatements
... ] [ ELSEstatements
] 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
>> ] LOOPstatements
END LOOP [label
];
LOOP
определяет безусловный цикл, который повторяется
бесконечно, пока не будет прерван операторами EXIT
или
RETURN
. Опциональный параметр
label
может использоваться операторами EXIT
и CONTINUE
во вложенных циклах для указания, на какой цикл
относятся эти операторы.
41.6.5.2. EXIT
#
EXIT [label
] [ WHENboolean-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
] [ WHENboolean-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
>> ] WHILEboolean-expression
LOOPstatements
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
>> ] FORname
IN [ REVERSE ]expression
..expression
[ BYexpression
] LOOPstatements
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
>> ] FORtarget
INquery
LOOPstatements
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
>> ] FORtarget
IN EXECUTEtext_expression
[USINGexpression
[, ... ]] LOOPstatements
END LOOP [label
];
Это похоже на предыдущую форму, за исключением того, что исходный запрос указывается в виде строкового выражения, которое вычисляется и перепланируется при каждом входе в цикл FOR
. Это позволяет программисту выбрать скорость предварительно запланированного запроса или гибкость динамического запроса, так же, как с обычным оператором EXECUTE
. Как и с EXECUTE
, значения параметров могут быть вставлены в динамическую команду с помощью USING
.
Другой способ указать запрос, результаты которого должны быть перебраны, - это объявить его как курсор. Об этом описано в Раздел 41.7.4.
41.6.7. Цикл по массивам #
Цикл FOREACH
очень похож на цикл FOR
,
но вместо итерации по строкам, возвращаемым SQL-запросом,
он итерирует по элементам массива.
(Обычно, FOREACH
предназначен для итерации
по компонентам составного выражения; в будущем могут быть добавлены варианты для итерации
по составным типам, кроме массивов).
Выражение FOREACH
для итерации по массиву выглядит так:
[ <<label
>> ] FOREACHtarget
[ SLICEnumber
] IN ARRAYexpression
LOOPstatements
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
>> ] [ DECLAREdeclarations
] BEGINstatements
EXCEPTION WHENcondition
[ ORcondition
... ] THENhandler_statements
[WHENcondition
[ ORcondition
... ] THENhandler_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 DIAGNOSTICSvariable
{ = | := }item
[ , ... ];
Каждый item
- это ключевое слово, определяющее значение статуса, которое будет присвоено указанной variable
(которая должна быть правильного типа данных для его получения). В настоящее время доступные элементы статуса показаны в Таблица 41.2.
Таблица 41.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 | строк(и) текста, описывающие стек вызовов в момент возникновения исключения (см. Раздел 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
возвращает тот же тип трассировки стека, но описывает местоположение,
в котором была обнаружена ошибка, а не текущее местоположение.