Настройка параметра преобразования результирующих наборов для PostgreSQL

Как известно, PostgreSQL не позволяет напрямую возвращать результирующие наборы данных (result sets) из процедур. Это может создавать сложности при миграции процедур из тех Баз Данных, которые поддерживают такой механизм (например, Microsoft SQL Server, Sybase ASE и др).

Конвертум Мастер позволяет решить эту проблему и предоставляет несколько способов конвертации процедур, возвращающих наборы данных. Рассмотрим эти подходы на примере конвертации процедуры из SQL Server в PostgreSQL.

Ниже приведён пример процедуры SQL Server, возвращающей результирующий набор данных:

 create table test_data(c1 int, c2 varchar(22))
 create procedure result_set_pr @p1 Date as
 select @p1 as c0, c1, c2 from test_data

В нашем инструменте за результат конвертации таких процедур отвечает параметр RETURN_RESULT_FROM_SP_AND_FN.

Чтобы изменить значение данного параметра, следуйте инструкциям, приведённым на скриншоте ниже:

Параметр RETURN_RESULT_FROM_SP_AND_FN может принимать следующие значения:

По умолчанию

• RETURN_RESULT_FROM_SP_AND_FN = Table

В этом случае такие процедуры конвертируются в функции, возвращающие таблицу. Все вызовы данной процедуры в других объектах будут автоматически заменены на соответствующие вызовы функции через SELECT.

Конвертация при использовании значения Table:

CREATE OR REPLACE FUNCTION result_set_pr(v_p1 DATE)
RETURNS TABLE (c0 DATE,c1 INTEGER,c2 VARCHAR(22))
LANGUAGE plpgsql
AS $$
BEGIN
 RETURN QUERY SELECT v_p1 AS c0, c1, c2 FROM test_data;
END;
$$;

• RETURN_RESULT_FROM_SP_AND_FN = SETOF

В этом случае такие процедуры конвертируются в функцию, возвращающую тип SETOF. Данный тип будет предварительно создан (имя типа = имя процедуры + _ rs). Все вызовы этой процедуры в других объектах будут соответствующим образом заменены на вызов функции с использованием SELECT.

Пример конвертации при значении SETOF:

 CREATE TYPE result_set_pr_rs AS(c0 DATE, c1 INTEGER, c2 VARCHAR(22));
 CREATE OR REPLACE FUNCTION result_set_pr(v_p1 DATE)
 RETURNS SETOF result_set_pr_rs LANGUAGE plpgsql
    AS $$
 BEGIN
    return query select v_p1 as c0, c1, c2 from test_data;
 END; $$;

• RETURN_RESULT_FROM_SP_AND_FN = REFCURSOR

Если выбран режим REFCURSOR, все результирующие наборы будут конвертированы в дополнительные INOUT параметры. Соответственно, вызовы данной процедуры в других объектах также будут изменены.

Пример конвертации при значении REFCURSOR:

 CREATE OR REPLACE PROCEDURE result_set_pr(v_p1 DATE, INOUT SWV_RefCur refcursor)
 LANGUAGE plpgsql
    AS $$
 BEGIN
    open SWV_RefCur for
    select v_p1 as c0, c1, c2 from test_data;
 END; $$;

Если в исходной процедуре возвращается два или более набора данных, то в результате конвертации они будут преобразованы в REFCURSOR, независимо от установленного параметра.

Данный параметр также влияет на конвертацию функций, возвращающих таблицу. Всё описанное выше применимо и к таким функциям. Единственное отличие заключается в том, что если параметр не задан, по умолчанию такие функции будут конвертироваться в функции, возвращающие таблицу.

Все перечисленные значения параметра применимы ко всем исходным СУБД, которые поддерживают возврат наборов данных в процедурах или возврат таблиц из функций и конвертация которых поддерживается Конвертум Мастер. Дополнительную информацию о поддерживаемых исходных базах данных можно найти по ссылке здесь.

Заключение

Какое значение параметра использовать в процессе конвертации? Это полностью зависит от требований вашей базы данных и приложения. Мы всегда рекомендуем начинать конвертацию с использования значений по умолчанию. Однако если в вашей базе данных присутствует большое количество процедур, возвращающих наборы данных, и они активно вызываются из приложения, мы рекомендуем использовать конвертацию через REFCURSOR, поскольку этот подход сохраняет тип объекта (процедуру) и упрощает внесение изменений в приложение.

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

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