From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: best way: diary functions.
Date: Tue, 3 Apr 2007 13:25:50 +0100
User-Agent: KMail/1.9.5
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200704031325.50542.gary.stainburn@ringways.co.uk>

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