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

Re: Simple recursion function on plpgsql - Postgresql 7.1

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pvi(at)axxent(dot)ca
Cc: Jan Wieck <JanWieck(at)Yahoo(dot)com>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Simple recursion function on plpgsql - Postgresql 7.1
Date: 2001-07-04 18:18:16
Message-ID: 29457.994270696@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Curious.  I can reproduce the problem in REL7_1_STABLE, but not in
current CVS tip.  I suspect that the problem was repaired in Jan's
sizable commit of 2001-05-21 10:22 ... but I don't immediately see
where the bug fix is; most of that commit is adding features or
closing memory leaks AFAICS.  Jan, any ideas?

			regards, tom lane


"Vadim I. Passynkov" <pvi(at)axxent(dot)ca> writes:
> Hi All
> Simple task: calculate number children for parent 1
>                       1
>                      /|\
>                     7 2 8
>                      / \
>                     3   4
>                        / \
>                       5   6

> DROP   TABLE test1;
> CREATE TABLE test1 ( child int4, parent int4 );

> INSERT INTO test1 VALUES ( 2, 1 );
> INSERT INTO test1 VALUES ( 7, 1 );
> INSERT INTO test1 VALUES ( 8, 1 );

> INSERT INTO test1 VALUES ( 3, 2 );
> INSERT INTO test1 VALUES ( 4, 2 );

> INSERT INTO test1 VALUES ( 5, 4 );
> INSERT INTO test1 VALUES ( 6, 4 );

> DROP   FUNCTION test1 ( int4, int2 );
> CREATE FUNCTION test1 ( int4, int2 ) RETURNS int4 AS '
> DECLARE
>   rec record;
>   cn int4;
> BEGIN
>   IF $2 = 100 THEN
>       RAISE EXCEPTION ''Loop !!!'';
>   END IF;
>   cn := ( SELECT COUNT ( * ) FROM test1 WHERE parent = $1 );
>   FOR rec IN SELECT child FROM test1 WHERE parent = $1 LOOP
>     cn := test1 ( rec.child, $2 + 1 ) + cn;
>   END LOOP;
>   RETURN cn;
> END;
> ' LANGUAGE 'plpgsql';


>  SELECT test1 ( 1, 0 );
>  test1 
> -------
>      7
> (1 row)

> This result is OK.


> but if in test1 function replace string
> "cn := test1 ( rec.child, $2 + 1 ) + cn;"
> to
> "cn := cn + test1 ( rec.child, $2 + 1 );"

> SELECT test1 ( 1, 0 );
>  test1 
> -------
>      6

> Very strange problem;
> On 7.0.3 both functions working right.


> -- 

>  Vadim I. Passynkov, Axxent Corp.
>  mailto:pvi(at)axxent(dot)ca

> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

In response to

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2001-07-04 21:26:53
Subject: Re: configure finds ant (screensaver)
Previous:From: Vadim I. PassynkovDate: 2001-07-04 17:26:36
Subject: Simple recursion function on plpgsql - Postgresql 7.1

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