Hi,
How can I expand a date range in a table to a set of date records?
I have a table of availabilities thus:
Column | Type | Modifiers
-----------+-----------------------------+------------------------------------------------------------
aid | integer | not null default
nextval('availability_aid_seq'::regclass)
asid | integer | not null
asdate | date | not null
afdate | date | not null
adays | integer |
acomments | text |
asdate is the start date
afdate is the finish date
How can I expand this to a set of
Column | Type | Modifiers
-----------+-----------------------------+------------------------------------------------------------
aid | integer | not null
asid | integer | not null
adate | date | not null
acomments | text |
i.e.
aid | asid | asdate | afdate | adays | acomments
-----+------+------------+------------+-------+--------------------
12 | 1 | 2007-08-11 | 2007-08-12 | 1 | Early finish Sunday
Becomes
aid | asid | asdate | acomments
-----+------+------------+--------------------
12 | 1 | 2007-08-11 | Early finish Sunday
12 | 1 | 2007-08-12 | Early finish Sunday
I have a function date_range to return a set of dates, but so far I can't get
a valid view to work.
Also, is there a better method?
CREATE FUNCTION date_range(fdate date, tdate date) RETURNS SETOF date
AS $$
DECLARE
wdate date;
BEGIN
return next fdate;
wdate:=fdate+1;
while wdate <= tdate LOOP
return next wdate;
wdate:=wdate+1;
end LOOP;
return;
END;
$$
LANGUAGE plpgsql;
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
