Date: Sun, 15 Oct 2006 23:39:51 -0300
From: "Marc G. Fournier" <scrappy@postgresql.org>
To: pgsql-sql@postgresql.org
Subject: Recursive pl/pgsql function ...
Message-ID: <E6008DD5708EE82E0C628C71@ganymede.hub.org>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="==========22E0D35A5E145F64F9B2=========="

--==========22E0D35A5E145F64F9B2==========
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline


'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=20
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,=20
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 || ''=20
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 || ''=20
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=20
error:

1 Record:
# select get_next_billing_date(activated, 12) from company_details where=20
company_id =3D 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=20
doable?

Thanks ...

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

--==========22E0D35A5E145F64F9B2==========
Content-Type: application/pgp-signature
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (FreeBSD)

iD8DBQFFMvD34QvfyHIvDvMRAneoAKDJvwMeSKGFO651i1E526w3lyDjcQCg3pUu
TAEdVkHySEFC+Yr6uQJyYJQ=
=wjhY
-----END PGP SIGNATURE-----

--==========22E0D35A5E145F64F9B2==========--