问题描述
我在 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 查询中的多个函数调用中重用硬编码值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!