I need to return two (or more) queries from a PostgreSQL-stored procedure.
The equivalent of this mssql-query (note: simple example - far more complex queries):
CREATE PROCEDURE dbo.TestMe
AS
BEGIN
SELECT * FROM T_FMS_Configuration;
SELECT * FROM T_FMS_Navigation;
END
GO
Now I want to port these to postgresql.
What I managed to do so far:
Do it as a function:
CREATE OR REPLACE FUNCTION public.get_data_from_multiple_schemas(id integer, ref1 refcursor, ref2 refcursor)
RETURNS SETOF refcursor
LANGUAGE plpgsql
AS $function$
BEGIN
OPEN ref1 FOR SELECT * FROM dbo."T_FMS_Configuration";
RETURN NEXT ref1;
OPEN ref2 FOR SELECT * FROM dbo."T_FMS_Navigation";
RETURN NEXT ref2;
END;
$function$
;
SELECT get_data_from_multiple_schemas(69, 'Ref1', 'Ref2');
FETCH ALL IN "Ref1";
FETCH ALL IN "Ref2";
But this is a function.
I could do it as a procedure like this:
-- How to return multiple select queries from PostgreSQL's functions like we do in MS SQL Procedures
DROP PROCEDURE IF EXISTS sp_GiveMeTwoResults();
CREATE OR REPLACE PROCEDURE sp_GiveMeTwoResults()
LANGUAGE plpgsql
AS
$$
BEGIN
-- Suppress row count messages
PERFORM set_config('client_min_messages', 'warning', false);
-- Create a temporary table to store the result
DROP TABLE IF EXISTS temp_result1;
CREATE TEMP TABLE temp_result1 AS
SELECT * FROM dbo."T_FMS_Configuration";
DROP TABLE IF EXISTS temp_result2;
CREATE TEMP TABLE temp_result2 AS
SELECT * FROM dbo."T_FMS_Navigation";
-- Select data from the temporary table
-- You can adjust this query as needed
-- SELECT * FROM temp_result;
-- Optionally drop the temporary table when you're done
-- Reset client_min_messages to its default value (optional)
PERFORM set_config('client_min_messages', 'notice', false);
END;
$$;
CALL sp_GiveMeTwoResults();
SELECT * FROM temp_result1;
SELECT * FROM temp_result2;
But this creates temporary tables.
Is it not possible to do this in a procedure kindof the same way like in a function ? e.g. I want
CREATE OR REPLACE PROCEDURE get_two_datasets(OUT cursor1 refcursor, OUT cursor2 refcursor)
LANGUAGE plpgsql
AS
$$
BEGIN
-- Open the first refcursor and populate it with data
OPEN cursor1 FOR SELECT * FROM dbo."T_FMS_Configuration";
-- Open the second refcursor and populate it with data
OPEN cursor2 FOR SELECT * FROM dbo."T_FMS_Navigation";
END;
$$;
CALL get_two_datasets('Ref1', 'Ref2');
-- CALL get_two_datasets(ref1 => 'Ref1', ref2 => 'Ref2'); -- not working either
FETCH ALL IN "Ref1";
FETCH ALL IN "Ref2";
But here I get:
ERROR: Cursor »Ref1« not existing
FEHLER: Cursor »Ref1« not existing SQL state: 34000
Ah, never mind, it's as easy as changing the datatype of the refcursors from out to inout...
DROP PROCEDURE IF EXISTS get_two_datasets;
CREATE OR REPLACE PROCEDURE get_two_datasets
(
INOUT result_data1 refcursor
,INOUT result_data2 refcursor
)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
OPEN result_data1 FOR SELECT * FROM dbo."T_FMS_Configuration" LIMIT 10;
OPEN result_data2 FOR SELECT * FROM dbo."T_FMS_Navigation" LIMIT 10;
END
$BODY$;
CALL get_two_datasets(result_data1 => 'Ref1', result_data2 => 'Ref2');
FETCH ALL IN "Ref1";
FETCH ALL IN "Ref2";