Обсуждение: How to join function with a table?

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

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

select * from datelist($1,$2)

'language 'sql' strict immutable;