Получение данных из функций и процедур в PostgreSQL

Различные механизмы возврата наборов результатов в PostgreSQL требуют разных подходов к извлечению данных.

В статье Результирующие наборы данных в PostgreSQL описаны некоторые механизмы, которые PostgreSQL предоставляет для возврата результатов:

• RETURNS TABLE
• RETURNS SETOF
• INOUT REFCURSOR

Далее рассмотрим, как извлекать данные из функций и процедур, использующих эти механизмы.

1. Извлечение данных из функций с RETURNS TABLE

Функции, определённые с использованием RETURNS TABLE, можно напрямую запрашивать с помощью SQL.

Определение и особенности таких функций описаны в статье Результирующие наборы данных в PostgreSQL.

Пример функции:

CREATE OR REPLACE FUNCTION get_empl_by_id(v_EmpId INTEGER)
RETURNS TABLE
(
emp_id VARCHAR,
first_name VARCHAR,
last_name VARCHAR,
salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT emp_id, first_name, last_name, salary
FROM empl
WHERE emp_id = v_EmpId;
END;
$$;

Использование функции в SELECT

SELECT *
FROM get_empl_by_id(10);

Поскольку функция возвращает набор строк, её можно использовать во многих SQL-конструкциях.

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

WITH emp_data AS (
SELECT *
FROM get_empl_by_id(10)
)
SELECT *
FROM emp_data;

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

SELECT e.*, d.department_name
FROM get_empl_by_id(10) e
JOIN departments d
ON e.emp_id = d.manager_id;

Это работает, потому что функция ведёт себя как табличное выражение.

2. Извлечение данных из функций с RETURNS SETOF

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

Более подробно об этом подходе и случаях его использования см. в статье Результирующие наборы данных в PostgreSQL.

Пример функции:

CREATE FUNCTION get_high_sal(min_salary NUMERIC)
RETURNS SETOF employee_info
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT id, name, salary
FROM employee
WHERE salary >= min_salary;
END;
$$;

Данные можно извлекать тем же способом, что и при использовании RETURNS TABLE.

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

SELECT *
FROM get_high_sal(5000);

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

SELECT e.name, d.department_name
FROM get_high_sal(5000) e
JOIN departments d
ON e.id = d.manager_id;

Использование в подзапросах

SELECT name
FROM get_high_sal(5000)
WHERE salary > 10000;

3. Извлечение данных из процедур, возвращающих REFCURSOR

Процедуры, возвращающие параметры типа REFCURSOR, работают иначе.

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

Подход к проектированию и реализации таких процедур описан в статье Результирующие наборы данных в PostgreSQL.

Метод 1 — Использование локальных переменных курсора

В этом подходе процедура вызывается из блока PL/pgSQL, а возвращаемые курсоры сохраняются в локальные переменные.

DO $$
DECLARE
   v_cur1 refcursor;
   v_cur2 refcursor;
   v_rec record;
   v_sal DECIMAL(10,2);
BEGIN
   CALL fromora.get_empl_dept(1, v_sal, v_cur1, v_cur2);

   RAISE NOTICE 'Salary: %', v_sal;

   LOOP
      FETCH v_cur1 INTO v_rec;
      EXIT WHEN NOT FOUND;
      RAISE NOTICE 'Employee: %', v_rec;
   END LOOP;

   LOOP
      FETCH v_cur2 INTO v_rec;
      EXIT WHEN NOT FOUND;
      RAISE NOTICE 'Departments: %', v_rec;
   END LOOP;
END;
$$;

Как вы можете видеть, данные из результирующих наборов обрабатываются построчно.

Метод 2 — Передача имён курсоров напрямую

Другой вариант заключается в передаче имён курсоров как литералов при вызове процедуры.

CALL fromora.get_empl_dept (1, NULL, 'rf1'::refcursor, 'rf2'::refcursor);
FETCH ALL FROM rf1;
--FETCH ALL FROM rf2;

В этом случае:

• процедура открывает курсоры с указанными именами
• вызывающая сторона получает результаты с помощью FETCH Этот метод часто используется в клиентских приложениях или скриптах.

Итог

Извлечение данных зависит от механизма, используемого для возврата набора результатов.

Функции, возвращающие RETURNS TABLE и RETURNS SETOF, напрямую интегрируются в SQL-запросы, тогда как REFCURSOR требует процедурного подхода для извлечения данных.

Подробнее о том, как реализованы эти механизмы и когда их следует использовать, см. в статье Результирующие наборы данных в PostgreSQL.