Simple recursion function on plpgsql - Postgresql 7.1 - Mailing list pgsql-bugs

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@axxent.ca