Re: [GENERAL] How to join function with a table?

[Yudie Pg]

On 8/5/05, Tom Lane <[EMAIL PROTECTED]> wrote:

Certainly not --- per the SQL spec, different elements of a FROM listare independent, so the datelist relation can't refer to P.
(I think SQL 2003 has a construct called LATERAL that would allowsuch things, but we don't implement that yet.)The only way to do this at the moment in Postgres is to put theset-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

Re: [GENERAL] How to join function with a table?

[Tom Lane]

Yudie Pg <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] How to join function with a table?

[Yudie Pg]

what about something likeselect id,datelistfrom 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.
 

Re: [GENERAL] How to join function with a table?

[Ragnar Hafstað]

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



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] How to join function with a table?

[Yudie Pg]

 try select * from payment as p, (select * from datelist('8/1/2005, 8/5/2005')) as datewhere 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.
 

Re: [GENERAL] How to join function with a table?

[Pascual De Ruvo]

On 8/5/05, Yudie Pg <[EMAIL PROTECTED]> 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.