Как вернуть значения из функций в PL/pgSQL

Когда вы пишете функции в PostgreSQL, одна из первых задач — понять, как вернуть результат. Иногда нужно просто вернуть одно число. Иногда — целую таблицу. А порой — даже несколько наборов данных. В этом разделе разберём все основные варианты: от простейшего RETURN до RETURN QUERY, RETURNS TABLE и SETOF.

Один результат: RETURN

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

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT;
BEGIN
    SELECT COUNT(*) INTO total FROM students;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

Когда вы создаёте функцию в PL/pgSQL, вы должны указать, что она возвращает. Это относится к ключевому слову RETURNS, которое определяет "формат" результата, возвращаемого функцией. Так что если вы хотите вернуть одно число, текст или таблицу данных — всё это нужно прописать в строке с RETURNS.

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

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Здесь ключевое слово RETURNS INT указывает, что функция возвращает число.

Возврат одного значения

Давайте начнём с самого простого — функции, которая возвращает одно значение. Например, функция, которая подсчитывает количество студентов в таблице students:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT;
BEGIN
    SELECT COUNT(*) INTO total FROM students; -- Сохраняем результат запроса в переменную total
    RETURN total; -- Возвращаем результат
END;
$$ LANGUAGE plpgsql;

Теперь мы можем вызвать эту функцию:

SELECT count_students(); -- Вернёт количество студентов

Возврат нескольких значений с помощью RETURNS TABLE

Иногда возникает необходимость вернуть не одно значение, а целый набор записей. Например, список всех студентов с их именами и идентификаторами. Для этого используется конструкция RETURNS TABLE.

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students; -- Возвращаем результат запроса как таблицу
END;
$$ LANGUAGE plpgsql;

Теперь мы можем вызвать эту функцию:

SELECT * FROM get_students(); -- Вернёт таблицу со всеми студентами

Обратите внимание на ключевые слова RETURNS TABLE. Они указывают, что функция возвращает таблицу с указанными столбцами (id и name в данном случае).

Использование RETURN QUERY

Вам уже должен был понравиться наш пример выше. Но вот ещё одна деталь: RETURN QUERY — это волшебная палочка PL/pgSQL, которая позволяет возвращать данные непосредственно из запроса. С её помощью вы можете вернуть как результат целого запроса, так и его подмножество.

Допустим, нам нужно вернуть всех студентов, которые активно обучаются (их статус в базе данных отмечен как active = TRUE):

CREATE FUNCTION get_active_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name
    FROM students
    WHERE active = TRUE; -- Возвращаем только активных студентов
END;
$$ LANGUAGE plpgsql;

Теперь мы можем вызвать функцию и получить данные об активных студентах:

SELECT * FROM get_active_students();

Возврат нескольких строк без RETURNS TABLE

В некоторых случаях вы можете захотеть вернуть строки данных без использования RETURNS TABLE. Для этого можно воспользоваться типом данных SETOF. Это позволяет возвращать строки данных одной и той же структуры. Например:

CREATE FUNCTION get_student_names() RETURNS SETOF TEXT AS $$
BEGIN
    RETURN QUERY
    SELECT name
    FROM students;
END;
$$ LANGUAGE plpgsql;

Эта функция возвращает только список имён студентов:

SELECT * FROM get_student_names();

Возврат значений в зависимости от входных данных

Функции не всегда возвращают только статические результаты. Они могут использовать параметры, чтобы динамически изменять результаты.

Вот пример возврата данных по идентификатору студента

CREATE FUNCTION get_student_by_id(student_id INT) RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name
    FROM students
    WHERE id = student_id; -- Используем параметр student_id
END;
$$ LANGUAGE plpgsql;

Теперь вы можете запросить информацию о конкретном студенте:

SELECT * FROM get_student_by_id(3); -- Вернёт данные студента с ID = 3

Возврат сложных данных (несколько наборов)

Иногда данные настолько сложны, что их нужно возвращать несколькими наборами. Для этого вы можете использовать курсоры. Например, если вы хотите предоставить два набора данных из функции — список активных студентов и неактивных студентов.

CREATE FUNCTION get_students_status() RETURNS SETOF RECORD AS $$
BEGIN
    RETURN QUERY
    SELECT id, name, 'active' AS status
    FROM students
    WHERE active = TRUE;

    RETURN QUERY
    SELECT id, name, 'inactive' AS status
    FROM students
    WHERE active = FALSE;
END;
$$ LANGUAGE plpgsql;

Теперь вы можете получить оба набора данных:

SELECT * FROM get_students_status();

Типичные ошибки при работе с RETURNS

Не указать тип возврата: Если вы не укажете, что функция возвращает, PostgreSQL выдаст ошибку. Например:

CREATE FUNCTION no_return_type() AS $$ -- Ошибка, не указан RETURNS
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Не совпадают типы данных: убедитесь, что возвращаемые значения соответствуют объявленным типам. Например, если вы указали, что возвращаете INT, не пытайтесь вернуть строку.

Пропущенное использование RETURN QUERY: если вы забыли использовать RETURN QUERY для выполнения сложного запроса, функция просто ничего не вернёт.

Некорректный возврат нескольких значений: если вы возвращаете строку данных, но забыли использовать SETOF или TABLE, PostgreSQL выдаст ошибку.