postgresqlstored-proceduresprocedure

In PostgreSQL, how to return multiple different tables from a procedure call?


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


Solution

  • 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";