Thread

1. Simple recursion function on plpgsql - Postgresql 7.1

   Vadim I. Passynkov <pvi@axxent.ca> — 2001-07-04T17:26:36Z

   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
   
   

2. Re: Simple recursion function on plpgsql - Postgresql 7.1

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-07-04T18:18:16Z

   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