Обсуждение: Simple recursion function on plpgsql - Postgresql 7.1

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

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

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