Postgres函数:返回多个表 [英] Postgres function: return multiple tables
本文介绍了Postgres函数:返回多个表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否可以从postgres函数返回多个不同类型的结果集?
Is it possible to return several result sets of different types from postgres function?
类似的东西:
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;
我不想使用加入,因为可以为用户使用多个电话.同样,最好避免使用游标. 在MS SQL中是可能的,而我想在postgres中做同样的事情.
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.
推荐答案
搜索之后,找不到任何更好的解决方案,例如使用游标.我假设您已经看过这本书了: https://blog.dsl-platform.com/multiple-result-sets-alternatives-in-postgres-3/
After the seek, could not find any better solutions as use of cursors. I assume you have seen this one already: https://blog.dsl-platform.com/multiple-result-sets-alternatives-in-postgres-3/
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;
并致电:
BEGIN;
SELECT load_page(mySession);
FETCH ALL IN "<server cursor 1>";
FETCH ALL IN "<server cursor 2>";
COMMIT;
这篇关于Postgres函数:返回多个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文