Yudie Pg <yudiepg@xxxxxxxxx> writes:
> I have a function returning set of date called datelist(date,date)
> ...
> I would like to join this function with a table
> create table payment(
> id int4 not null,
> date_start date,
> date_end date
> )
> ...
> I thought simple join like this would work, but it doesn't
> select * from payment P, datelist(P.date_start, P.date_end)
Certainly not --- per the SQL spec, different elements of a FROM list
are independent, so the datelist relation can't refer to P.
(I think SQL 2003 has a construct called LATERAL that would allow
such things, but we don't implement that yet.)
The only way to do this at the moment in Postgres is to put the
set-returning function into the SELECT target list:
select id, datelist(date_start, date_end) from payment;
which will work fine if datelist() is implemented as a SQL function,
and not so fine if it's implemented in plpgsql. You can work around
this by wrapping the plpgsql function in a SQL function (ick).
I posted an example in another thread a day or so ago.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
