Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 2499843CEB8
	for <pgsql-sql@postgresql.org>; Thu, 19 Jan 2012 04:33:01 -0400 (AST)
Received: from lo.gmane.org ([80.91.229.12])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1RnnQX-0001Tu-RX
	for pgsql-sql@postgresql.org; Thu, 19 Jan 2012 08:33:02 +0000
Received: from list by lo.gmane.org with local (Exim 4.69)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1RnnQH-0005rb-Dr
	for pgsql-sql@postgresql.org; Thu, 19 Jan 2012 09:32:41 +0100
Received: from static.187.232.9.176.clients.your-server.de ([176.9.232.187])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Thu, 19 Jan 2012 09:32:41 +0100
Received: from hari.fuchs by static.187.232.9.176.clients.your-server.de with
	local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Thu, 19 Jan 2012 09:32:41 +0100
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: hari.fuchs@gmail.com
Subject: Re: date range to set of dates expansion
Date: Thu, 19 Jan 2012 09:32:27 +0100
Lines: 53
Message-ID: <87mx9kks84.fsf@hf.protecting.net>
References: <201201190713.34333.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: static.187.232.9.176.clients.your-server.de
X-Archive: encrypt
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.2 (gnu/linux)
Cancel-Lock: sha1:0smhxMtqIjiXk5iTGSOU3L+JPQc=
X-Pg-Spam-Score: -4.2 (----)
X-Archive-Number: 201201/85
X-Sequence-Number: 36256

Gary Stainburn <gary.stainburn@ringways.co.uk> writes:

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

Why don't you just use the built-in PostgreSQL function for that?

SELECT aid, asid,
       generate_series (asdate, afdate, INTERVAL '1 day')::date AS asdate,
       acomments
FROM tbl