> 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