Hi!
I try to work with trees in Postgres 7.1.1
My test script is
====================================================================
CREATE TABLE hierarchy
parent INTEGER,
child INTEGER
);
INSERT INTO hierarchy VALUES (1, 2);
INSERT INTO hierarchy VALUES (2, 4);
INSERT INTO hierarchy VALUES (3, 5);
INSERT INTO hierarchy VALUES (4, 5);
CREATE FUNCTION is_child(INTEGER, INTEGER) RETURNS BOOLEAN AS
'
DECLARE
-- Don't works!!!
rec hierarchy%ROWTYPE;
-- Works!!!
rec RECORD;
BEGIN
RAISE DEBUG ''child is %, parent is %'', $1, $2;
FOR rec IN SELECT * FROM hierarchy WHERE child = $1 LOOP
RAISE DEBUG ''found parent %'', rec.parent;
IF rec.parent = $2 OR is_child(rec.parent, $2) THEN
RETURN TRUE;
END IF;
END LOOP;
RETURN FALSE;
END;
'
LANGUAGE 'plpgsql';
SELECT is_child(5, 1);
====================================================================
If I declare "rec" as "RECORD", then all works fine:
2001-05-22 16:22:01 DEBUG: child is 5, parent is 1
2001-05-22 16:22:01 DEBUG: found parent 3
2001-05-22 16:22:01 DEBUG: child is 3, parent is 1
2001-05-22 16:22:01 DEBUG: found parent 4
2001-05-22 16:22:01 DEBUG: child is 4, parent is 1
2001-05-22 16:22:01 DEBUG: found parent 2
2001-05-22 16:22:01 DEBUG: child is 2, parent is 1
2001-05-22 16:22:01 DEBUG: found parent 1
But if "rec" is declared as "hierarchy%ROWTYPE", then
execution fails with such log messages:
2001-05-22 16:11:21 DEBUG: child is 5, parent is 10
2001-05-22 16:11:21 DEBUG: found parent 3
2001-05-22 16:11:21 DEBUG: child is 3, parent is 10
Server process (pid 62170) exited with status 139 at Tue May 22 16:11:21
2001
Terminating any active server processes...
Server processes were terminated at Tue May 22 16:11:21 2001
Reinitializing shared memory and semaphores
....
Is this bug or feature?
Alexander Zagrebin
--
