Як повертати значення з функцій у 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 видасть помилку.