Re: Returning a set of dates

> 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
causeus   -- 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-152011-06-292011-07-132011-07-272011-08-102011-08-242011-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