sql - 在 PostgreSQL 中重用存储过程结果

Question

我正在处理 PostgreSQL 数据库的只读副本，不可能创建任何临时表(只读事务)。

假设我有存储过程 foo(...) 返回我的 TABLE(...) 结果(或 SETOF Foo_Type是一样的)。

这个 Foo_Type 有一些 type 字段定义行中的数据类型。

在另一个具有固定返回类型的过程 foo_wrapper(...) 中(它也是 TABLE(...) 或者说 SETOF Foo_Wrapper_Type) 我需要执行以下操作:

根据 Foo_Type.type 值，我需要使用 left join 将来自 foo(...) 的结果与不同的表合并。目前是这样的:

...
return query
select
   ft.*,
   a1.x1
   from foo(param1, param2, ...) ft
   left join a_table a on ...
   where ft.type = 'value_1'

union
select
   ft.*,
   b1.x1
   from foo(param1, param2, ...) ft
   left join b_table b on ...
   where ft.type = 'value_2'

...

这个 union 有几十个。

不要告诉我我的 Schema 设计得多么邪恶和糟糕。我知道，这是遗留架构。

我想问的是如何避免在这条语句中多次调用 foo(...) 过程？

这个foo_wrapper(...) 过程运行起来非常慢。我怀疑这是因为使用相同参数多次调用 foo(...) 过程，我不确定 DB 是否将其结果缓存在一个 session 中。

所以我的问题由两部分组成:

1. 在我的大联合选择之前，有什么方法可以“提取”并保存 foo(...) 的结果吗？DECLARE 结果 SETOF Foo_Type 不起作用。

2. 可能这不是优化的重点，有人肯定会声称 foo(...) 的第一次调用的结果被缓存以供此过程中的进一步调用？

Answer 1

使用公用表表达式:

return query
with foo_data as (
  select * 
  from foo(param1, param2, ...)
)
select ft.*,
       a1.x1
from foo_data ft
  left join a_table a on ...
where ft.type = 'value_1'
union 
select ft.*,
       b1.x1
from foo_data ft
   left join b_table b on ...
where ft.type = 'value_2'
...

您可能想使用union all 而不是union。