Re-use a hardcoded value in multiple function calls in PostgreSQL query

Question

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?

I tried to do something like this:

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

...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.

Answer 1

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.