From: Marc G. Fournier <scrappy@postgresql.org>
To: pgsql-sql@postgresql.org
Subject: Recursive pl/pgsql function ...
Date: Sun, 15 Oct 2006 23:39:51 -0300
Message-ID: <E6008DD5708EE82E0C628C71@ganymede.hub.org> (raw)
'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 . scrappy@hub.org MSN . scrappy@hub.org
Yahoo . yscrappy Skype: hub.org ICQ . 7615664
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrappy@postgresql.org
Subject: Re: Recursive pl/pgsql function ...
In-Reply-To: <E6008DD5708EE82E0C628C71@ganymede.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox