Thread: best way: diary functions.

Hi folks

I've got 2 tables, 

availabiliy
~~~~~~~

stdate    date
edate    date
workdays    integer
comments    text

example record
2007-03-01    2007-03-07     5    Please can I have alternate days


roster
~~~~
rdate    date
rdiag    varchar(10)

example
2007-03-01    B12
2007-03-03    B11
2006-03-05    B12
2007-03-07    B13

What would be the best way to create a view to list every date within a range 
giving either rostered, available but not rostered, and not available?

I've read through the docs and created a function (below) to return every date 
within a range, but I can't get my head round converting that to a query.

Although I'm doing this as a learning exercise, it will be used in a web site 
I'm developing so comments on speed and efficiency would also be welcome.

Gary

create or replace function date_range(fdate date,tdate date) returns setof 
date
AS $PROC$
DECLARE wdate date;
BEGIN return next fdate; wdate:=fdate+1; while wdate <= tdate LOOP   return next wdate;   wdate:=wdate+1; end LOOP;
return;
END;
$PROC$ LANGUAGE plpgsql;



-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     

My solution that works is:

select date_range as rdate,  case      when rsgsid is not null then 2     when aid is not null then 1     else 0 end as
statusfrom date_range('2007-04-01','2007-04-30') left join availability a on a.asid = 1 and (date_range,date_range)
overlaps
 
(a.asdate,a.afdate) left join roster_staff r on r.rsdate = date_range;

Can anyone see any problems or improvements to this?
-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000