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:
Show quoted text
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