[SQL] Recursive pl/pgsql function ...

Marc G. Fournier Sun, 15 Oct 2006 19:41:13 -0700

'k, this isn't quite working as expected (or, rather, hoped) ... I've created a recursive function (that does work nicely) that takes a date in the past, and keeps advancing in steps of 'n months' until the date is in the future:

CREATE OR REPLACE FUNCTION get_next_billing_date(timestamp with time zone, int4) RETURNS TIMESTAMP AS '

DECLARE
  max_date RECORD;
  ret TIMESTAMP WITH TIME ZONE;

  start_date ALIAS FOR $1;
  payment_period ALIAS FOR $2;

BEGIN

SELECT INTO max_date CASE WHEN now() < start_date + ( payment_period || '' months'')::interval THEN payment_period ELSE NULL END;


 IF max_date.case IS NULL THEN

SELECT INTO ret get_next_billing_date(start_date + ( payment_period || '' months'')::interval, payment_period);

 ELSE
   RETURN start_date + ( payment_period || '' months'')::interval;
 END IF;
 RETURN ret;
END;
' LANGUAGE plpgsql;

It works, no problem there:

# select get_next_billing_date('2005-10-15', 3);
get_next_billing_date
-----------------------
2007-01-15 00:00:00
(1 row)

But, as soon as I try and use that function as a field in a query, it gives an error:


1 Record:

# select get_next_billing_date(activated, 12) from company_details where company_id = 771;

  get_next_billing_date
----------------------------
2007-05-03 15:09:19.491958
(1 row)

More then one Record:
# select get_next_billing_date(activated, 12) from company_details;
ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function "get_next_billing_date"

Something I've written wrong in the function, or just not something that is doable?


Thanks ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED]                              MSN . [EMAIL PROTECTED]
Yahoo . yscrappy               Skype: hub.org        ICQ . 7615664

pgpQYr5LP4REk.pgp
Description: PGP signature

[SQL] Recursive pl/pgsql function ...

Reply via email to