Postgres function: return multiple tables
Solution 1
After the seek, could not find any better solutions as use of cursors.
CREATE FUNCTION load_page(_session INT) RETURNS setof refcursor AS
$$
DECLARE c_top_items refcursor;
DECLARE c_shopping_cart refcursor;
BEGIN
OPEN c_top_items FOR
SELECT t.name, t.description
FROM top_item t
ORDER BY t.popularity DESC
LIMIT 10;
RETURN NEXT c_top_items;
OPEN c_shopping_cart FOR
SELECT c.product_id, c.product_name, c.quantity
FROM shopping_cart c
WHERE c.session_id = _session
ORDER BY c.id;
RETURN NEXT c_shopping_cart;
END;
$$ LANGUAGE plpgsql;
And calling:
BEGIN;
SELECT load_page(mySession);
FETCH ALL IN "<server cursor 1>";
FETCH ALL IN "<server cursor 2>";
COMMIT;
Solution 2
I don't want to use joins because several phones for user are possible.
This is not a reason to avoid JOIN
s in PostgreSQL. At all.
PostgreSQL allows you to aggregate the phone numbers into an array:
CREATE OR REPLACE FUNCTION getUserById()
RETURNS TABLE (
id INTEGER,
name TEXT,
/* and other columns */
phone_numbers TEXT[]
)
AS
$$
select
users.id,
users.name,
/* and other columns */
-- Remove NULL because you get an array containing just NULL
-- if user_phones doesn't contain any matching rows.
array_remove(array_agg(user_phones.phone_number), NULL) as phone_numbers
from users
left join user_phones on user_phones.user_id = users.id
where users.id = 1
-- Note that grouping by a table's primary key allows you to use
-- any column from that table in the select in PostgreSQL
group by users.id
;
$$
LANGUAGE SQL
STABLE
;
This is much simpler and more intuitive.
You can switch to an inner join if it's okay to give back zero rows for a user without a phone number. In that case, you could drop the array_remove
call.
I also added the STABLE
specification to the function (since it doesn't modify any table data) and switched it to SQL
instead of PGPLSQL
(since it's just a single query). This will allow PG to optimize better; in particular, it can inline the query and push filters down in some cases. You may not even need a function, actually.
Solution 3
CREATE OR REPLACE FUNCTION public.TestReturnMultipleTales
(
param_coid integer,
ref1 refcursor,
ref2 refcursor
)
RETURNS SETOF refcursor
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
BEGIN
OPEN ref1 FOR SELECT * FROM dbo.tbl1 WHERE coid = param_coid;
RETURN NEXT ref1;
OPEN ref2 FOR SELECT * FROM dbo.tbl2 LIMIT 5;
RETURN NEXT ref2;
END;
$BODY$;
BEGIN;
SELECT football_players.show_cities_multiple(123456, 'Ref1', 'Ref2');
FETCH ALL IN "Ref1";
FETCH ALL IN "Ref2";
COMMIT;
ALSO, CONTACT ME IF WANT TO IMPLEMENT IT INTO .NET WITH C# I RESOLVED THE ISSUE.
user1820686
Updated on June 08, 2022Comments
-
user1820686 almost 2 years
Is it possible to return several result sets of different types from postgres function?
Something like:
CREATE OR REPLACE FUNCTION getUserById() RETURNS setof ??? AS $$ BEGIN return query select id, name /* and other columns */ from users where id = 1; return query select id, phone_number from user_phones where user_id = 1 END $$ LANGUAGE plpgsql;
I don't want to use joins because several phones for user are possible. Also it would be great to avoid using cursors. It's possible in MS SQL and I want to do the same thing in postgres.
-
user1820686 about 8 yearsYes, I've seen this article. Anyway, thanks for your researching :)
-
Blip about 3 yearsThe link mentioned in the article is dead
-
mostafa hosseini over 2 yearsThis case works