Thread: Recursive pl/pgsql function ...

'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;
RETURNret;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

"Marc G. Fournier" <scrappy@postgresql.org> writes:
> 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"

Hm, what PG version is this?  I couldn't duplicate that in HEAD, though
I did find out that a zero or negative payment_period makes it recurse
until "stack depth exceeded".
        regards, tom lane

--On Sunday, October 15, 2006 23:27:34 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> 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"
>
> Hm, what PG version is this?  I couldn't duplicate that in HEAD, though
> I did find out that a zero or negative payment_period makes it recurse
> until "stack depth exceeded".

8.1.4 .. the odd thing is that I can throw any date at it, it seems, but I
can't use it as a 'field' in a query of a table, even when returning a single
record:

# select * from company_details where company_id = 76;company_id |   address_one   | address_two |   city    |
province_state|  
postal_code |      activated      | disabled | recurring | referrer_info |
billing_type_id | currency_id | country_id

------------+-----------------+-------------+-----------+----------------+-------------+---------------------+----------+-----------+---------------+-----------------+-------------+------------
      76 | 127 Main Street |             | Wolfville | NS             | B0P  
1X0     | 2001-11-01 02:00:00 |          | t         |               |
2 |           1 |         45
(1 row)

=# select get_next_billing_date('2001-11-01', 3);get_next_billing_date
-----------------------2006-11-01 00:00:00
(1 row)

=# select get_next_billing_date(activated, 3) from company_details where
company_id = 76;
ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function "get_next_billing_date"
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT  get_next_billing_date( $1 ,  $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
=# \d company_details               Table "public.company_details"    Column      |            Type             |
Modifiers
-----------------+-----------------------------+---------------company_id      | integer
|address_one    | text                        | not nulladdress_two     | text                        |city
|text                        | not nullprovince_state  | text                        |postal_code     | text
           |activated       | timestamp without time zone | default now()disabled        | timestamp without time zone
|recurring      | boolean                     | default falsereferrer_info   | text
|billing_type_id| integer                     |currency_id     | integer                     |country_id      | integer
                   | 
Foreign-key constraints:   "company_details_billing_type_id_fkey" FOREIGN KEY (billing_type_id)
REFERENCES billing_type(id) ON UPDATE CASCADE ON DELETE SET NULL   "company_details_country_id_fkey" FOREIGN KEY
(country_id)REFERENCES  
country(id) ON UPDATE CASCADE ON DELETE SET NULL   "company_details_currency_id_fkey" FOREIGN KEY (currency_id)
REFERENCES 
currency(id) ON UPDATE CASCADE ON DELETE SET NULL


----
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

Hi, Marc,
Hi, Tom,

Tom Lane wrote:
> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> 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"
> 
> Hm, what PG version is this?  I couldn't duplicate that in HEAD, though
> I did find out that a zero or negative payment_period makes it recurse
> until "stack depth exceeded".

And this is the reason why that function is a nice example where the
recursive design approach is not necessary, and even hurts.

A simple while-Loop will do the same, more efficient, and without any
stack problems.

Btw, it may even be possible to use an explicit formula to calculate
this problem, but date and time calculations are always crazy.

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org