2

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.

EMP
  • 59,148
  • 53
  • 164
  • 220

1 Answers1

3

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.

Gavin
  • 6,180
  • 3
  • 25
  • 25
  • +1 Interesting read, thanks. Looks like LATERAL didn't make it into 9.1 - perhaps 9.2? My problem, however, doesn't necessarily need LATERAL, because I only want to re-use a constant, not a row returned from a query. – EMP Jul 14 '11 at 11:31