pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRecursive pl/pgsql function ...
4+ messages / 3 participants
[nested] [flat]
* Recursive pl/pgsql function ...
@ 2006-10-16 02:39 Marc G. Fournier <scrappy@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Marc G. Fournier @ 2006-10-16 02:39 UTC (permalink / raw)
To: pgsql-sql
'k, this isn't quite working as expected (or, rather, hoped) ... I've created a
recursive function (that does work nicely) that takes a date in the past, and
keeps advancing in steps of 'n months' until the date is in the future:
CREATE OR REPLACE FUNCTION get_next_billing_date(timestamp with time zone,
int4) RETURNS TIMESTAMP AS '
DECLARE
max_date RECORD;
ret TIMESTAMP WITH TIME ZONE;
start_date ALIAS FOR $1;
payment_period ALIAS FOR $2;
BEGIN
SELECT INTO max_date CASE WHEN now() < start_date + ( payment_period || ''
months'')::interval THEN payment_period ELSE NULL END;
IF max_date.case IS NULL THEN
SELECT INTO ret get_next_billing_date(start_date + ( payment_period || ''
months'')::interval, payment_period);
ELSE
RETURN start_date + ( payment_period || '' months'')::interval;
END IF;
RETURN ret;
END;
' LANGUAGE plpgsql;
It works, no problem there:
# select get_next_billing_date('2005-10-15', 3);
get_next_billing_date
-----------------------
2007-01-15 00:00:00
(1 row)
But, as soon as I try and use that function as a field in a query, it gives an
error:
1 Record:
# select get_next_billing_date(activated, 12) from company_details where
company_id = 771;
get_next_billing_date
----------------------------
2007-05-03 15:09:19.491958
(1 row)
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"
Something I've written wrong in the function, or just not something that is
doable?
Thanks ...
----
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Recursive pl/pgsql function ...
@ 2006-10-16 03:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marc G. Fournier <scrappy@postgresql.org>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2006-10-16 03:27 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-sql
"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".
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Recursive pl/pgsql function ...
@ 2006-10-16 06:16 Marc G. Fournier <scrappy@postgresql.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Marc G. Fournier @ 2006-10-16 06:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
--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 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@hub.org MSN . scrappy@hub.org
Yahoo . yscrappy Skype: hub.org ICQ . 7615664
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Recursive pl/pgsql function ...
@ 2006-10-16 09:20 Markus Schaber <schabi@logix-tt.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Markus Schaber @ 2006-10-16 09:20 UTC (permalink / raw)
To: pgsql-sql; +Cc: Marc G. Fournier <scrappy@postgresql.org>
Hi, Marc,
Hi, Tom,
Tom Lane 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".
And this is the reason why that function is a nice example where the
recursive design approach is not necessary, and even hurts.
A simple while-Loop will do the same, more efficient, and without any
stack problems.
Btw, it may even be possible to use an explicit formula to calculate
this problem, but date and time calculations are always crazy.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-10-16 09:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-16 02:39 Recursive pl/pgsql function ... Marc G. Fournier <scrappy@postgresql.org>
2006-10-16 03:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-16 06:16 ` Marc G. Fournier <scrappy@postgresql.org>
2006-10-16 09:20 ` Markus Schaber <schabi@logix-tt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox