From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: best way: diary functions.
Date: Tue, 3 Apr 2007 13:25:50 +0100
Message-ID: <200704031325.50542.gary.stainburn@ringways.co.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: best way: diary functions.
In-Reply-To: <200704031325.50542.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox