Function to determine next payment date

[Paul Lambert <paul.lambert@xxxxxxxxxxxxxxxxxx>]

I'm trying to create a function to determine the next due payment date 
of a recurring expense.

I have a table containing the date the payment first started, and a 
payment frequency key which relates to a payment ID in another table 
containing a string defining how frequent the payment exists.

-- Cut down version of table definitions:

CREATE TABLE "tblRecuringExpenses"
(
  expense_id integer NOT NULL DEFAULT nextval('seq_expense_id'::regclass),
  expense_frequency character varying(1) NOT NULL,
  expense_startdate timestamp with time zone NOT NULL,
  CONSTRAINT pk_tblrecuringexpenses_expense_id PRIMARY KEY (expense_id),
  CONSTRAINT fk_tblrecuringexpenses_expense_frequency FOREIGN KEY 
(expense_frequency)
      REFERENCES "tblRecuringFrequency" (frequency_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;

CREATE TABLE "tblRecuringFrequency"
(
  frequency_id character varying(1) NOT NULL,
  next_payment character varying(40),
  CONSTRAINT pk_tblrecuringfrequency_frequncy_id PRIMARY KEY (frequency_id)
)
WITHOUT OIDS;


I figure the best way to get the next payment date is to create a 
function that takes in the payment ID, then loops through adding the 
payment frequency interval onto the start date until it gets to a date 
that's greater than or equal to the current date.

Firstly... is this assumption correct or is there an easier way to do it?

Secondly... assuming my initial assumption is correct I've created the 
following function in PL/pgSQL, however when I execute it, it runs for 
minutes - where the two tables above I'm working with are dummy test 
tables that contain 2 rows each and the calculations are simple enough 
that I would assume a speedy conclusion - it would seem to me that my 
loop is endless.

An explain analyze does me no good - that get's stuck in the same 
endless loop, and a simple explain only gives me:
 "Result  (cost=0.00..0.01 rows=1 width=0)"


CREATE OR REPLACE FUNCTION fnNextPaymentDue(integer) RETURNS timestamp AS $$
DECLARE
   start_date timestamp;
   next_payment interval;
   loop_count int;
BEGIN
   SELECT INTO start_date (SELECT expense_startdate FROM
      "tblRecuringExpenses" WHERE expense_id=$1);
   SELECT INTO next_payment (select next_payment from
      "tblRecuringFrequency" JOIN "tblRecuringExpenses" on
      ("tblRecuringFrequency".frequency_id
       ="tblRecuringExpenses".expense_frequency)
      WHERE expense_id=$1)::interval;
LOOP
   if start_date >=current_date then
      exit;
   end if;
   start_date:=start_date+next_payment;
END LOOP;
RETURN start_date;
END;
$$ LANGUAGE PLPGSQL STABLE;

Since it's my first function in PL/pgSQL I've probably missed something 
pretty obvious - but if someone could point it out to me that would be 
muchly appreciated.

--
Paul Lambert
Database Administrator
AutoLedgers


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings