Re: [SQL] Recursive pl/pgsql function ...

Sun, 15 Oct 2006 23:17:53 -0700 


--On Sunday, October 15, 2006 23:27:34 -0400 Tom Lane <[EMAIL PROTECTED]> wrote:


"Marc G. Fournier" <[EMAIL PROTECTED]> 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 . [EMAIL PROTECTED]                              MSN . [EMAIL PROTECTED]
Yahoo . yscrappy               Skype: hub.org        ICQ . 7615664



Attachment:
pgp6QDoYVNkVo.pgp

Description: PGP signature






















					Reply via email to