Is it possible to call 2 table returning functions in one main wrapper function in Postgres

28 Views Asked by At

I have 2 functions which perform selects on 2 tables and return data respectively.

These are defined as follows:

get_table_data:

-- Function that performs a SELECT query on the table and returns the result as a SETOF
DROP FUNCTION IF EXISTS get_table_data(uuid);
CREATE OR REPLACE FUNCTION get_table_data(sessiontoken uuid)
RETURNS SETOF device
LANGUAGE plpgsql
AS $$
        DECLARE user_id UUID;
        DECLARE organisation_id UUID;
BEGIN

    -- Get UserID from User table
    SELECT UserID INTO user_id
    FROM "user"
    WHERE "user".SessionToken = $1;

    -- Get OrganisationID from User table
    SELECT OrganisationID INTO organisation_id
    FROM "user"
    WHERE "user".UserID = user_id;

    -- Now, you can use the variables as needed in the rest of your PL/pgSQL block
    RAISE NOTICE 'UserID: %, OrganisationID: %', user_id, organisation_id;
    RETURN QUERY


    SELECT *
    FROM device d
    WHERE D.deviceid = (Select deviceid from userdevice ud join "user" u on ud.userid = u.userid and u.sessiontoken = $1);


   -- RETURN QUERY SELECT * FROM example_table2;
END;
$$;

The 2nd function get_table_data2 is defined as follows:

DROP FUNCTION IF EXISTS get_table_data2(uuid);
CREATE OR REPLACE FUNCTION get_table_data2(sessiontoken uuid)
RETURNS SETOF vwdevicechannel
LANGUAGE plpgsql
AS $$
        DECLARE user_id UUID;
        DECLARE organisation_id UUID;
BEGIN

    -- Get UserID from User table
    SELECT UserID INTO user_id
    FROM "user"
    WHERE "user".SessionToken = $1;

    -- Get OrganisationID from User table
    SELECT OrganisationID INTO organisation_id
    FROM "user"
    WHERE "user".UserID = user_id;

    -- Now, you can use the variables as needed in the rest of your PL/pgSQL block
    RAISE NOTICE 'UserID: %, OrganisationID: %', user_id, organisation_id;
    RETURN QUERY
    SELECT dc.*
    FROM vwdevicechannel dc
    JOIN
        UserDevice ud ON dc.DeviceID = ud.DeviceID
    LEFT JOIN
        ChannelType ct ON dc.ChannelTypeID = ct.ChannelTypeID
    WHERE
       ud.userid = user_id;
END;
$$;

I then want to call this in a main wrapper function called get_table_data3 defined as follows:

DROP FUNCTION IF EXISTS get_table_data3();
CREATE OR REPLACE FUNCTION get_table_data3()
RETURNS VOID
LANGUAGE plpgsql
AS $$
        DECLARE user_id UUID;
        DECLARE organisation_id UUID;
BEGIN

    SELECT * FROM get_table_data('151785c6-268f-4893-9d0d-c21079def599');

-- Call the function to get and display the data
    SELECT * FROM get_table_data2('151785c6-268f-4893-9d0d-c21079def599');
END;
$$;

I then attempt to call this as follows:

select * get_table_data3();

But I get the following error:

[2024-02-08 11:39:57] [42601] ERROR: query has no destination for result data
[2024-02-08 11:39:57] Hint: If you want to discard the results of a SELECT, use PERFORM instead.
[2024-02-08 11:39:57] Where: PL/pgSQL function get_table_data3() line 6 at SQL statement

How can I get these 2 functions to execute in 1 function?

I have to do this way because I have a c# executor function which will want to call this 1 main wrapper function get_table_data3 ?

0

There are 0 best solutions below