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 ?