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.

You could prepare a statement using one $1 param (timestamp with time zone). I don't see any other solution to this. — Grzegorz Szpetkowski
– Grzegorz Szpetkowski, Commented Jul 14, 2011 at 11:50

Gavin · Accepted Answer · 2011-07-14 09:27:34Z

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.

answered Jul 14, 2011 at 9:27

Gavin

6,5603 gold badges28 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EMP Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related