Thread

1. bug in plpgsql???

   Alexander Zagrebin <alexz@visp.ru> — 2001-05-22T12:24:37Z

   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
   --
   
   
   

2. Re: bug in plpgsql???

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-23T19:17:31Z

   "Alexander Zagrebin" <alexz@visp.ru> writes:
   > But if "rec" is declared as "hierarchy%ROWTYPE", then
   > execution fails with such log messages:
   
   I can't duplicate this in current sources.  Please try 7.1.2.
   
   			regards, tom lane
   
   

3. RE: bug in plpgsql???

   Alexander Zagrebin <alexz@visp.ru> — 2001-05-24T09:13:27Z

   Hi!
    
   > "Alexander Zagrebin" <alexz@visp.ru> writes:
   > > But if "rec" is declared as "hierarchy%ROWTYPE", then
   > > execution fails with such log messages:
   > 
   > I can't duplicate this in current sources.  Please try 7.1.2.
   > 
   > 			regards, tom lane
   
   Thanks! At 7.1.2 that procedure works without errors.
   
   Alexander Zagrebin
   --