Re: Recursive pl/pgsql function ...

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