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

[GENERAL] How to join function with a table?

[Yudie Pg]

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


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

because it require a relation.


help is appreciated.


Yudie

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

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 torefer to whatever value on the payment records.

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]

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?

[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