Re: Simple recursion function on plpgsql - Postgresql 7.1

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