在 PostgreSQL 查询中的多个函数调用中重用硬编码值 [英] Re-use a hardcoded value in multiple function calls in PostgreSQL query

问题描述

我在 PostgreSQL 9.0 中有一些返回表结果的函数.这些背后的想法是返回特定时间的数据，例如.

I have some functions in PostgreSQL 9.0 that return table results. The idea behind these is to return the data as it was at a certain time, eg.

CREATE FUNCTION person_asof(effective_time timestamp with time zone)
RETURNS SETOF person
...

CREATE FUNCTION pgroup_asof(effective_time timestamp with time zone)
RETURNS SETOF pgroup
...

我几乎可以像查询表一样查询它们，包括连接和所有:

I can query them almost as if they were tables, with joins and all:

SELECT *
FROM pgroup_asof('2011-01-01') g
JOIN person_asof('2011-01-01') p
ON g.id = p.group_id

这很好用，但是有什么技巧可以用来指定一次有效时间吗?

This works fine, but is there any trick I can use to specify the effective time just once?

我尝试做这样的事情:

SELECT *
FROM (SELECT '2010-04-12'::timestamp ts) effective,
pgroup_asof(effective.ts) g
JOIN person_asof(effective.ts) p
ON g.id = p.group_id

...但是由于错误而失败:FROM 中的函数表达式不能引用相同查询级别的其他关系，并且将主查询放入子查询也无济于事.

...but that fails with ERROR: function expression in FROM cannot refer to other relations of same query level and putting the main query into a sub-query doesn't help, either.

推荐答案

这也是我过去想做的事情，但现在看起来不太可能，但可能有 希望即将到来.

This is something I have wanted to do in the past as well but does not look like it is possible yet, but there may be hope on the horizon.

这篇关于在 PostgreSQL 查询中的多个函数调用中重用硬编码值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！