Message-ID: 
 <ab42e85cc41d268f0ceabc86368d3c53.squirrel@webmail.stinkweasel.net>
Date: Sat, 11 Jun 2011 17:18:21 -0500
Subject: Re: Returning a set of dates
From: "C. Bensend" <benny@bennyvision.com>
To: pgsql-sql@postgresql.org
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Importance: Normal


> 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

Almost, but not quite - the d parameter is a bill's "start date",
and the function shouldn't show dates in the past.  So, when the
above function is called with say '2011-06-01' as the beginning
date, the function will happily return '2011-06-01' in the result
set, even though it's in the past.

I've modified it a bit.  I renamed the function arguments to be a
bit more descriptive, did a little more math, and added an IF
statement to not return any dates in the past:


CREATE OR REPLACE FUNCTION public.next_bill_date(d date, frequency
   interval, daterange interval)
 RETURNS SETOF date
AS $function$
DECLARE
    max_date date;
    due_date date;
BEGIN
    -- We need to add the epoch date and daterange together, to
    -- get the "max_date" value.  However, this would cause us
    -- to lose the last due date in the result set.  Add one more
    -- frequency to it so we don't lose that.
    max_date := CURRENT_DATE + frequency + daterange;
    due_date := d;
    WHILE due_date + frequency <= max_date LOOP
        -- Don't include dates in the past - we only want future
        -- due dates for bills.
        IF due_date >= CURRENT_DATE
        THEN
            RETURN NEXT due_date;
        END IF;
        due_date := due_date + frequency;
    END LOOP;
    RETURN; -- exit function


This appears to work properly:

SELECT next_bill_date( '2011-06-01', '2 weeks', '3 months' );
 next_bill_date
----------------
 2011-06-15
 2011-06-29
 2011-07-13
 2011-07-27
 2011-08-10
 2011-08-24
 2011-09-07
(7 rows)

Thanks for all your help!  I'm not at all experienced with plpgsql,
so this was very much appreciated.  :)

Benny


-- 
"You were doing well until everyone died."
                                    -- "God", Futurama