Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 2E537B5DBD1
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sat, 11 Jun 2011 02:11:46 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 46524-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sat, 11 Jun 2011 05:11:39 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pz0-f46.google.com (mail-pz0-f46.google.com
	[209.85.210.46])
	by mail.postgresql.org (Postfix) with ESMTP id BF17DB5DBC1
	for <pgsql-sql@postgresql.org>; Sat, 11 Jun 2011 02:11:38 -0300 (ADT)
Received: by pzk9 with SMTP id 9so1504647pzk.19
	for <pgsql-sql@postgresql.org>; Fri, 10 Jun 2011 22:11:37 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.68.27.130 with SMTP id t2mr1345774pbg.230.1307769097439; Fri,
	10 Jun 2011 22:11:37 -0700 (PDT)
Received: by 10.68.49.197 with HTTP; Fri, 10 Jun 2011 22:11:37 -0700 (PDT)
X-Originating-IP: [66.215.95.51]
In-Reply-To: 
 <c29311b814cfdc327e4b19ae910d8a93.squirrel@webmail.stinkweasel.net>
References: 
 <c29311b814cfdc327e4b19ae910d8a93.squirrel@webmail.stinkweasel.net>
Date: Fri, 10 Jun 2011 22:11:37 -0700
Message-ID: <BANLkTik2c8U=DyFAkDDFDNaiagcrspYrdQ@mail.gmail.com>
Subject: Re: Returning a set of dates
From: Samuel Gendler <sgendler@ideasculptor.com>
To: "C. Bensend" <benny@bennyvision.com>
Cc: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=bcaec520eb6b8049d904a568b82d
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201106/32
X-Sequence-Number: 35498

--bcaec520eb6b8049d904a568b82d
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Jun 10, 2011 at 9:28 PM, C. Bensend <benny@bennyvision.com> wrote:

> SELECT next_bill_date( '2011-01-01', '1 month', '4 months' );
>
>
> .. I expect the following result set:
>
>
>  next_bill_date
> ----------------
>
> 2011-07-01
> 2011-08-01
> 2011-09-01
> 2011-10-01
>
>
>

http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING


CREATE OR REPLACE FUNCTION public.next_bill_date(d date, period interval,
  i interval)
 RETURNS SETOF date
AS $function$
DECLARE
    max_date date;
    due_date date;
BEGIN
    max_date := CURRENT_DATE + i;
    due_date := d;
    WHILE due_date + period <= max_date LOOP
        RETURN NEXT due_date; -- add d to the result set
        due_date := due_date + period;
    END LOOP;
    RETURN; -- exit function
END;
$function$ language plpgsql;


testdb=# select next_bill_date('2011-06-11', '2 week', '3 month');
next_bill_date
----------------
 2011-06-11
 2011-06-25
 2011-07-09
 2011-07-23
 2011-08-06
 2011-08-20

--bcaec520eb6b8049d904a568b82d
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div>On Fri, Jun 10, 2011 at 9:28 PM, C. Bensend <span dir=3D"ltr">&lt;<a h=
ref=3D"mailto:benny@bennyvision.com">benny@bennyvision.com</a>&gt;</span> w=
rote:</div><div><div><div class=3D"gmail_quote"><blockquote class=3D"gmail_=
quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1=
ex;">
SELECT next_bill_date( &#39;2011-01-01&#39;, &#39;1 month&#39;, &#39;4 mont=
hs&#39; );<br>
<br>
<br>
.. I expect the following result set:<br>
<br>
<br>
=A0next_bill_date<br>
----------------<br>
<br>
2011-07-01<br>
2011-08-01<br>
2011-09-01<br>
2011-10-01<br>
<br>
<br></blockquote><div><br></div><div><br></div><div><a href=3D"http://www.p=
ostgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-=
STATEMENTS-RETURNING">http://www.postgresql.org/docs/8.4/interactive/plpgsq=
l-control-structures.html#PLPGSQL-STATEMENTS-RETURNING</a></div>
<div><br></div><div><br></div><div><font class=3D"Apple-style-span" face=3D=
"arial, sans-serif"><span class=3D"Apple-style-span" style=3D"border-collap=
se: collapse;"><div>CREATE OR REPLACE FUNCTION public.next_bill_date(d date=
, period interval,</div>
<div>=A0=A0i interval)</div><div>=A0RETURNS SETOF date</div><div>AS $functi=
on$</div><div>DECLARE</div><div>=A0=A0 =A0max_date date;</div><div>=A0=A0 =
=A0due_date date;</div><div>BEGIN</div><div>=A0=A0 =A0max_date :=3D CURRENT=
_DATE + i;</div><div>=A0=A0 =A0due_date :=3D d;</div>
<div>=A0=A0 =A0WHILE due_date + period &lt;=3D max_date LOOP</div><div>=A0=
=A0 =A0 =A0 =A0RETURN NEXT due_date; -- add d to the result set</div><div>=
=A0=A0 =A0 =A0 =A0due_date :=3D due_date + period;</div><div>=A0=A0 =A0END =
LOOP;</div><div>=A0=A0 =A0RETURN; -- exit function</div>
<div>END;</div><div>$function$ language plpgsql;</div><div><br></div><div><=
br></div><div><div>testdb=3D# select next_bill_date(&#39;2011-06-11&#39;, &=
#39;2 week&#39;, &#39;3 month&#39;); next_bill_date=A0</div><div>----------=
------</div>
<div>=A02011-06-11</div><div>=A02011-06-25</div><div>=A02011-07-09</div><di=
v>=A02011-07-23</div><div>=A02011-08-06</div><div>=A02011-08-20</div></div>=
<div><br></div></span></font></div></div></div></div>

--bcaec520eb6b8049d904a568b82d--