How to join function with a table?

On 8/5/05, Yudie Pg <yudiepg@gmail.com> wrote:

I thought simple join like this would work, but it doesn't
select * from payment P, datelist(P.date_start , P.date_end)

try select * from payment as p, (select * from datelist('8/1/2005,
8/5/2005')) as date
where date...... = p.....

try select * from payment as p, (select * from datelist('8/1/2005,

8/5/2005')) as date
where date...... = p.....

The problem is the function's parameters '8/1/2005', '8/5/2005' has to

refer to whatever value on the payment records.

On Fri, 2005-08-05 at 10:53 -0500, Yudie Pg wrote:

Hi everyone,

I have a function returning set of date called datelist(date,date)
example:
select * from datelist('8/1/2005, 8/5/2005');
8/1/2005
8/2/3005
8/3/2004
8/4/2005
8/5/2005

I would like to join this function with a table
create table payment(
id int4 not null,
date_start date,
date_end date
)
id | date_start | date_end
----------------------------------------
1 | 8/1/2005 | 8/2/2005
2 | 8/4/2005 | 8/6/2005

I wish I could do join that returns something like this with the
function

id | datelist
------------------
1 | 8/1/2005
1 | 8/2/2005
2 | 8/4/2005
2 | 8/5/2005
2 | 8/6/2005

what about something like

select id,datelist
from payment as p,
(select * from datelist('8/1/2005, 8/5/2005')) as list
where datelist between p.date_start and p.date_end;

gnari

what about something like

select id,datelist
from payment as p,
(select * from datelist('8/1/2005, 8/5/2005')) as list
where datelist between p.date_start and p.date_end;

That's works but have to put the whole date range into the parameters before
it can be joined.
This would need 2 queries where the first query only to find minumum &
maximum date that possibly recorded on payment table.

Yudie Pg <yudiepg@gmail.com> 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

On 8/5/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

This wraping works! Thanks Tom.
create function datelist_sql(date, date) returns setof date as'

select * from datelist($1,$2)

'language 'sql' strict immutable;
Then simply do this query
"select datelist('8/1/2005', '8/6/2005')"
otherwise with plpgsql function I got
ERROR: set-valued function called in context that cannot accept a set