Postgresql exception when others then

PostgreSQL Exception

Summary: in this tutorial, you will learn how to catch PostgreSQL exceptions in PL/pgSQL.

Introduction to the PL/pgSQL Exception clause

When an error occurs in a block, PostgreSQL will abort the execution of the block and also the surrounding transaction.

To recover from the error, you can use the exception clause in the begin. end block.

The following illustrates the syntax of the exception clause:

  • First, when an error occurs between the begin and exception , PL/pgSQL stops the execution and passes the control to the exception list.
  • Second, PL/pgSQL searches for the first condition that matches the occurring error.
  • Third, if there is a match, the corresponding handle_exception statements will execute. PL/pgSQL passes the control to the statement after the end keyword.
  • Finally, if no match found, the error propagates out and can be caught by the exception clause of the enclosing block. In case there is no enclosing block with the exception clause, PL/pgSQL will abort the processing.

The condition names can be no_data_found in case of a select statement return no rows or too_many_rows if the select statement returns more than one row. For a complete list of condition names on the PostgreSQL website.

It’s also possible to specify the error condition by SQLSTATE code. For example, P0002 for no_data_found and P0003 for too_many_rows .

Typically, you will catch a specific exception and handle it accordingly. To handle other exceptions rather than the one you specify on the list, you can use the when others then clause.

Handling exception examples

We’ll use the film table from the sample database for the demonstration.

1) Handling no_data_found exception example

The following example issues an error because the film with id 2000 does not exist.

The following example uses the exception clause to catch the no_data_found exception and report a more meaningful message:

2) Handling too_many_rows exception example

The following example illustrates how to handle the too_many_rows exception:

In this example, the too_many_rows exception occurs because the select into statement returns more than one row while it is supposed to return one row.

3) Handling multiple exceptions

The following example illustrates how to catch multiple exceptions:

4) Handling exceptions as SQLSTATE codes

The following example is the same as the one above except that it uses the SQLSTATE codes instead of the condition names:

Источник

Postgresql exception when others then

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

39.6.1. Команды для возврата значения из функции

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

39.6.1.1. RETURN

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

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

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

Для функции, возвращающей void , RETURN можно использовать в любом месте, но без выражения после RETURN .

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

39.6.1.2. RETURN NEXT и RETURN QUERY

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

Читайте также:  Oysters t72hm 3g кастомная прошивка

RETURN NEXT и RETURN QUERY не выполняют возврат из функции. Они просто добавляют строки в результирующее множество. Затем выполнение продолжается со следующего оператора в функции. Успешное выполнение RETURN NEXT и RETURN QUERY формирует множество строк результата. Для выхода из функции используется RETURN , обязательно без аргументов (или можно просто дождаться окончания выполнения функции).

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

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

Пример использования RETURN NEXT :

Пример использования RETURN QUERY :

Примечание

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

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

Операторы 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

39.6.2.1. IF-THEN

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

39.6.2.2. IF-THEN-ELSE

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

39.6.2.3. IF-THEN-ELSIF

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

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

Другой вариант сделать то же самое, это использование вложенных операторов IF-THEN-ELSE , как в следующем примере:

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

39.6.2.4. Простой CASE

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

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

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

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

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

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

39.6.3.1. LOOP

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

39.6.3.2. EXIT

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

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

Читайте также:  Servlet exception java heap space

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

Когда EXIT используется для выхода из блока, управление переходит к следующему оператору после окончания блока. Обратите внимание, что для выхода из блока нужно обязательно указывать метку . EXIT без метки не позволяет прекратить работу блока. (Это изменение по сравнению с версиями PostgreSQL до 8.4, в которых разрешалось использовать EXIT без метки для прекращения работы текущего блока.)

39.6.3.3. CONTINUE

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

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

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

39.6.3.4. WHILE

WHILE выполняет серию команд до тех пор, пока истинно логическое-выражение . Выражение проверяется непосредственно перед каждым входом в тело цикла.

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

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

Примеры целочисленного FOR :

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

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

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

Другой вариант FOR позволяет организовать цикл по результатам запроса. Синтаксис:

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

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

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

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

Ещё одна разновидность этого типа цикла FOR-IN-EXECUTE :

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

Ещё один способ организовать цикл по результатам запроса это объявить курсор. Описание в Подразделе 39.7.4.

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

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

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

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

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

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

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

Читайте также:  Server error in application что это значит

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

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

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

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

При присвоении значения переменной y произойдёт ошибка division_by_zero . Она будет перехвачена в секции EXCEPTION . Оператор RETURN вернёт значение x , увеличенное на единицу, но изменения сделанные командой UPDATE будут отменены. Изменения, выполненные командой INSERT , которая предшествует блоку, не будут отменены. В результате, база данных будет содержать Tom Jones , а не Joe Jones .

Подсказка

Наличие секции EXCEPTION значительно увеличивает накладные расходы на вход/выход из блока, поэтому не используйте EXCEPTION без надобности.

Пример 39.2. Обработка исключений для команд UPDATE / INSERT

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

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

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

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

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

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

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

Таблица 39.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 строки текста, описывающие стек вызовов в момент исключения (см. Подраздел 39.6.7)

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

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

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

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

Источник

Smartadm.ru
Adblock
detector