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

40.6.1.1. RETURN

RETURN выражение;

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

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

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

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

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

Несколько примеров:

-- Функции, возвращающие скалярный тип данных
RETURN 1 + 2;
RETURN scalar_var;

-- Функции, возвращающие составной тип данных
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- требуется приведение типов

40.6.1.2. RETURN NEXT и RETURN QUERY

RETURN NEXT выражение;
RETURN QUERY запрос;
RETURN QUERY EXECUTE command-string [USING выражение [, ...]];

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

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, а если только один с типом sometype, то RETURNS SETOF 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
        -- здесь возможна обработка данных
        RETURN NEXT r; -- добавляет текущую строку запроса к возвращаемому результату
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();

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

CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY SELECT flightid
                   FROM flight
                  WHERE flightdate >= $1
                    AND flightdate < ($1 + 1);

    -- Так как выполнение ещё не закончено, можно проверить, были ли возвращены строки
    -- Если нет, то вызываем исключение
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Нет рейсов на дату: %.', $1;
    END IF;

    RETURN;
 END
$BODY$
LANGUAGE plpgsql;

-- Возвращает доступные рейсы, либо вызывает исключение
SELECT * FROM get_available_flightid(CURRENT_DATE);

40.6.2.1. IF-THEN

IF boolean-expression THEN
    statements
END IF;

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

Пример:

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

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

40.6.2.3. 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 не является истинным, то выполняется блок ELSE (если присутствует).

Пример:

IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    -- раз мы здесь, значит значение number не определено (NULL)
    result := 'NULL';
END IF;

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

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

IF demo_row.sex = 'm' THEN
    pretty_sex := 'man';
ELSE
    IF demo_row.sex = 'f' THEN
        pretty_sex := 'woman';
    END IF;
END IF;

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

40.6.2.4. Простой CASE

CASE search-expression
    WHEN выражение [, выражение [...]] THEN
      statements
  [WHEN выражение [, выражение [...]] 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 := 'один или два';
    ELSE
        msg := 'значение, отличное от один или два';
END CASE;

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

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

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

Пример:

CASE
    WHEN x BETWEEN 0 AND 10 THEN
        msg := 'значение в диапазоне между 0 и 10';
    WHEN x BETWEEN 11 AND 20 THEN
        msg := 'значение в диапазоне между 11 и 20';
END CASE;

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

40.6.3.1. LOOP

[<<метка>>]
LOOP
    statements
END LOOP [ метка ];

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

40.6.3.2. EXIT

EXIT [ метка ] [WHEN boolean-expression];

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

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

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

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

Примеры:

LOOP
    -- здесь вычисления 
    IF count > 0 THEN
        EXIT;  -- выход из цикла
    END IF;
END LOOP;

LOOP
    -- здесь вычисления
    EXIT WHEN count > 0;  -- аналогично предыдущему примеру
END LOOP;

<<ablock>>
BEGIN
    -- здесь вычисления
    IF stocks > 100000 THEN
        EXIT ablock;  -- выход из блока ablock
    END IF;
    -- вычисления не будут выполнены, если stocks > 100000
END;

40.6.3.3. CONTINUE

CONTINUE [ метка ] [WHEN boolean-expression];

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

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

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

Примеры:

LOOP
    -- здесь вычисления
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- Вычисления для count в диапазоне 50 .. 100
END LOOP;

40.6.3.4. WHILE

[<<метка>>]
WHILE boolean-expression LOOP
    statements
END LOOP [ метка ];

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

Пример:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- здесь вычисления
END LOOP;

WHILE NOT done LOOP
    -- здесь вычисления
END LOOP;

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

[<<метка>>]
FOR имя IN [REVERSE] выражение .. выражение [BY выражение] LOOP
    statements
END LOOP [ метка ];

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

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

FOR i IN 1..10 LOOP
    -- внутри цикла переменная i будет иметь значения 1,2,3,4,5,6,7,8,9,10
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- внутри цикла переменная i будет иметь значения 10,9,8,7,6,5,4,3,2,1
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- внутри цикла переменная i будет иметь значения 10,8,6,4,2
END LOOP;

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

Если у цикла есть метка, то к переменной цикла можно обращаться по имени, дополненному меткой.

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

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

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

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

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

Каждый item является ключевым словом, идентифицирующим значение состояния, которое будет присвоено указанной переменной. Переменная должна быть соответствующего типа данных. Идентификаторы, доступные в настоящее время, приведены в Таблице 40-1.

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

Пример:

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- здесь происходит обработка, которая может вызвать исключение
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;