Re: Recursive pl/pgsql function ...

--On Sunday, October 15, 2006 23:27:34 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Marc G. Fournier" <scrappy(at)postgresql(dot)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 null
address_two | text |
city | text | not null
province_state | text |
postal_code | text |
activated | timestamp without time zone | default now()
disabled | timestamp without time zone |
recurring | boolean | default false
referrer_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(at)hub(dot)org MSN . scrappy(at)hub(dot)org
Yahoo . yscrappy Skype: hub.org ICQ . 7615664