Когда вы пишете функции в 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 TABLE(id INT, name TEXT, status TEXT) AS $$
BEGIN
RETURN QUERY
SELECT s.id, s.name, 'active'::TEXT
FROM students s
WHERE s.active = TRUE;
RETURN QUERY
SELECT s.id, s.name, 'inactive'::TEXT
FROM students s
WHERE s.active = FALSE;
END;
$$ LANGUAGE plpgsql;Здесь мы намеренно описали колонки результата прямо в RETURNS TABLE(...) — иначе при RETURNS SETOF RECORD вызов SELECT * FROM get_students_status() упал бы с ошибкой a column definition list is required for functions returning "record", и пришлось бы каждый раз дописывать список колонок в скобках после имени функции. Теперь вы можете получить оба набора данных:
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 выдаст ошибку.
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ