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
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 |