Попробовать бесплатно

Результирующие наборы данных в PostgreSQL

В отличие от таких Баз Данных, как Microsoft SQL Server или Sybase Adaptive Server Enterprise, PostgreSQL не позволяет процедурам напрямую возвращать наборы результатов так же, как это делает обычный оператор SELECT.

Однако PostgreSQL предоставляет несколько механизмов для возврата наборов строк.

В настоящее время Конвертум Мастер поддерживает следующие подходы:

  • RETURNS TABLE (вариант по умолчанию)
  • RETURNS SETOF
  • INOUT REFCURSOR

Последние два варианта требуют дополнительной настройки инструмента. Более подробную информацию о конфигурации можно найти в статье: Настройка параметра преобразования результирующих наборов для PostgreSQL.

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

1. RETURNS TABLE

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

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

Преимущества:

  • обеспечивает хорошую читаемость кода
  • возвращает набор строк, который ведёт себя как обычная таблица
  • функцию можно использовать напрямую в:
    1. SELECT
    2. CTE (WITH)
    3. подзапросах
    4. других функциях
  • не требуется объявлять отдельные OUT-параметры, поскольку выходные столбцы задаются напрямую в конструкции RETURNS TABLE

Функции, определённые таким образом, естественно интегрируются в SQL-запросы.

Примеры вызова таких функций и получения результатов описаны в статье Получение данных из функций и процедур в PostgreSQL.

2. RETURNS SETOF (составной тип)

CREATE TYPE employee_info AS (
   emp_id INT,
   name TEXT,
   salary NUMERIC
);

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

В нашем инструменте этот подход реализуется через составной тип, который создаётся перед самой функцией.

Такие функции возвращают набор строк и также могут использоваться напрямую в:

  1. SELECT
  2. CTE (WITH)
  3. подзапросах
  4. других функциях

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

Примеры выполнения запросов к функциям, возвращающим значения SETOF, приведены в статье Получение данных из функций и процедур в PostgreSQL.

Ограничения RETURNS TABLE и RETURNS SETOF

Оба подхода имеют ряд общих ограничений:

  • структура возвращаемого набора результатов должна быть заранее определена, поэтому динамическое изменение набора возвращаемых столбцов не поддерживается
  • весь набор результатов формируется и возвращается целиком
  • для больших объёмов данных это может негативно сказаться на производительности

Примечание:

Не допускается использование явных параметров OUT или INOUT совместно с конструкцией RETURNS TABLE. Все выходные столбцы должны быть определены исключительно в секции TABLE.

3. INOUT REFCURSOR

Если требуется возвращать несколько наборов результатов или получать данные по частям (построчно/порционно), можно использовать параметры типа REFCURSOR.

Наш инструмент поддержки конвертации преобразует такую логику в процедуру, использующую параметры REFCURSOR в качестве INOUT-аргументов.

CREATE OR REPLACE PROCEDURE fromora.get_empl_dept( 
    v_EmpId INTEGER,
    INOUT v_MinSalary DECIMAL(10,2),
    INOUT SWV_RefCur refcursor DEFAULT NULL,
    INOUT SWV_RefCur2 refcursor DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- get information about a specific employee
    OPEN SWV_RefCur FOR
    SELECT emp_id, name, salary
    FROM fromora.empl
    WHERE emp_id = v_EmpId;
    -- fill the output parameter
    SELECT salary INTO v_MinSalary
    FROM empl
    WHERE emp_id = v_EmpId
    UNION ALL
    SELECT v_MinSalary;
    -- get the list of departments
    OPEN SWV_RefCur2 FOR
    SELECT dept_id, location, region
    FROM fromora.dept;
END;
$$;

Преимущества использования REFCURSOR:

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

Ограничения:

  • более сложный синтаксис
  • курсоры нельзя использовать напрямую в SQL-запросах
  • большое количество крупных курсоров может потреблять значительный объём памяти
  • построчная обработка (FETCH) обычно медленнее, чем наборный SELECT

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

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

Содержание: