date range to set of dates expansion

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: date range to set of dates expansion
Date: 2012-01-19 07:13:34
Message-ID: 201201190713.34333.gary.stainburn@ringways.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message hari.fuchs 2012-01-19 08:32:27 Re: date range to set of dates expansion
Previous Message Pavel Stehule 2012-01-18 09:25:27 Re: PostgreSQL Function