> 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
