From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: date range to set of dates expansion
Date: Thu, 19 Jan 2012 07:13:34 +0000
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201201190713.34333.gary.stainburn@ringways.co.uk>

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