database - 如何在 PostgreSQL 的事务中重用查询结果？

Question

我需要在一个事务中的其他查询中重用大量嵌套 SELECT 查询的结果。可能吗？

--谢谢!

Answer 1

在存储过程/函数中你可以这样做:

DECLARE mylist INTEGER[]
...
...
mylist := array( SELECT primarykey FROM bigquery );
...
...
SELECT foo FROM bar WHERE id =ANY( mylist );
SELECT x FROM y WHERE id =ANY( mylist );

这对于收集 PK 列表(使用大型慢速查询)并对其执行多个查询非常有用，特别是考虑到 plpgsql 函数可以向客户端返回多个结果集(RETURN SETOF refcursor)。

例如，我使用大型搜索查询(要点索引和地理定位)获取 50 个房地产列表 ID；该查询包括许多列、连接、排序、散列，以及最终的 LIMIT/OFFSET，并且不通过所有这些拖动所有列似乎要快得多，而不是仅使用搜索中使用的列，然后抓取一个 ID 列表，应用 LIMIT/OFFSET，然后返回抓取所有列。

然后使用这个 id 列表，我从其他表中获取信息，如联系人、电话号码等。由于一个列表可以有多个电话号码或联系人，使用另一个光标分别返回它们并让应用程序将其重新组合在一起，而不是使用类似 array_agg() 的方法在每个结果行中返回 phone# 列表。

好消息是，如果您使用预先准备好的语句，您可以选择，或者您也可以使用 EXECUTE，这样 postgres 就可以在知道数组长度的情况下重新计划查询，如果您预计它有时会非常大的话。

另一种解决方案是简单地

• 使用临时表(速度较慢，但​​您可以对其进行分析，这非常有用)
• 或者在你的大查询上创建一个游标，并创建一个返回这个游标内容的plpgsql函数，然后你可以多次使用它