Skip site navigation (1) Skip section navigation (2)

Re: Recursive pl/pgsql function ...

From: "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Recursive pl/pgsql function ...
Date: 2006-10-16 06:16:06
Message-ID: FCD2F51032B144D60C081F36@ganymede.hub.org (view raw or flat)
Thread:
Lists: pgsql-sql

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

In response to

pgsql-sql by date

Next:From: Markus SchaberDate: 2006-10-16 09:20:40
Subject: Re: Recursive pl/pgsql function ...
Previous:From: Tom LaneDate: 2006-10-16 03:31:04
Subject: Re: Assigning a timestamp without timezone to a timestamp

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group