Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 2D3319FA383 for ; Mon, 16 Oct 2006 03:17:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 42878-05 for ; Mon, 16 Oct 2006 06:17:30 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from hub.org (hub.org [200.46.204.220]) by postgresql.org (Postfix) with ESMTP id E00469FA3AD for ; Mon, 16 Oct 2006 03:17:28 -0300 (ADT) Received: from localhost (unknown [200.46.208.251]) by hub.org (Postfix) with ESMTP id 9343E3A462E for ; Mon, 16 Oct 2006 03:17:25 -0300 (ADT) Received: from hub.org ([200.46.204.220]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 88286-05; Mon, 16 Oct 2006 03:16:10 -0300 (ADT) Received: from ganymede.hub.org (blk-137-86-60.eastlink.ca [24.137.86.60]) by hub.org (Postfix) with ESMTP id 84B753A343D; Mon, 16 Oct 2006 03:16:06 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id F3F5934126; Mon, 16 Oct 2006 03:16:06 -0300 (ADT) Date: Mon, 16 Oct 2006 03:16:06 -0300 From: "Marc G. Fournier" To: Tom Lane cc: pgsql-sql@postgresql.org Subject: Re: Recursive pl/pgsql function ... Message-ID: In-Reply-To: <7543.1160969254@sss.pgh.pa.us> References: <7543.1160969254@sss.pgh.pa.us> X-Mailer: Mulberry/4.0.6 (Linux/x86) MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="==========6DB1263B272DB3B8D10A==========" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/151 X-Sequence-Number: 26540 --==========6DB1263B272DB3B8D10A========== Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline --On Sunday, October 15, 2006 23:27:34 -0400 Tom Lane = wrote: > "Marc G. Fournier" 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=20 can't use it as a 'field' in a query of a table, even when returning a single=20 record: # select * from company_details where company_id =3D 76; company_id | address_one | address_two | city | province_state |=20 postal_code | activated | disabled | recurring | referrer_info |=20 billing_type_id | currency_id | country_id ------------+-----------------+-------------+-----------+----------------+------= -------+---------------------+----------+-----------+---------------+-----------= ------+-------------+------------ 76 | 127 Main Street | | Wolfville | NS | B0P=20 1X0 | 2001-11-01 02:00:00 | | t | |=20 2 | 1 | 45 (1 row) =3D# select get_next_billing_date('2001-11-01', 3); get_next_billing_date ----------------------- 2006-11-01 00:00:00 (1 row) =3D# select get_next_billing_date(activated, 3) from company_details where=20 company_id =3D 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 =3D# \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)=20 REFERENCES billing_type(id) ON UPDATE CASCADE ON DELETE SET NULL "company_details_country_id_fkey" FOREIGN KEY (country_id) REFERENCES=20 country(id) ON UPDATE CASCADE ON DELETE SET NULL "company_details_currency_id_fkey" FOREIGN KEY (currency_id) REFERENCES=20 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 --==========6DB1263B272DB3B8D10A========== Content-Type: application/pgp-signature Content-Transfer-Encoding: 7bit -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (FreeBSD) iD8DBQFFMyOm4QvfyHIvDvMRAkO2AKCW6yXF6/mZCf7flG3odd2SQ0gaJgCfddi8 3djEIYOawrKiQemrhidB79I= =xsck -----END PGP SIGNATURE----- --==========6DB1263B272DB3B8D10A==========--