Result Sets Conversion to PostgreSQL

As you know, PostgreSQL does not allow you to return resultsets directly from a procedure. This can be a problem when migrating procedures from databases that support these mechanisms (SQL Server, Sybase ASE, etc.). Ispirer toolkit allows us to solve this problem and convert procedures that return resultsets in several ways. Let's look at these methods using the example of converting a procedure from SQL Server to PostgreSQL. This procedure returns a resultset:

 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

The following option is responsible for the result of converting such procedures in our tool (information on how to set options can be found here):

[Postgre]
RETURN_RESULT_FROM_SP_AND_FN

Option RETURN_RESULT_FROM_SP_AND_FN can have the following values:

• RETURN_RESULT_FROM_SP_AND_FN=SETOF

In this case, the conversion of such procedures will be made into a function that returns a setof type. This type will be pre-created (type name = procedure name + _rs). All calls to this procedure in other objects will be replaced accordingly by the select from the function.

Conversion when using the option with the SETOF value:

 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

If the Refcursor option is set, all result sets will be converted as additional INOUT parameters. Also, calls to this procedure in other objects will change accordingly.

Conversion when using the option with the REFCURSOR value:

 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; $$;

If the RETURN_RESULT_FROM_SP_AND_FN option is not specified, by default the conversion occurs as if the SETOF option was set. If 2 or more resultsets are returned in the source procedure, then as a result of the conversion they will be converted to REFCURSORS, regardless of the option set. This option also affects the conversion of functions that return a table. Everything described above is valid for such functions as well. The only difference is that if you do not specify this option, by default such functions will be converted into functions that return a table. All the above option values ​​are valid for all source databases that allow you to return a resultset in procedures or return a table from a function and whose conversion is supported by the Ispirer Toolkit. More information about which databases the Ispirer Toolkit supports converting from can be found here.

Conclusion

Which option to use in the conversion process? It depends entirely on the needs of your database and application. We always recommend starting conversion by using the default options. However, if your database has a large number of procedures that return resultsets and many of their calls from the application, we recommend using conversion through refcursors, since this option preserves the object type (procedure) and simplifies the work of changing the application.

If you have any questions or face any difficulties, please contact our support team: support@ispirer.com