Re: plpgsql recursion - Mailing list pgsql-general

Stefan,

I just downloaded PostgreSQL and I thought it would be a good learning
experience to figure out your problem. My background is as an Oracle
programmer but I haven't been using PL/SQL for a while.

Anyway, I tested your procedure and I got ok1. I think that you expected
"circular". I am afraid I am stumped as to why you do not get "circular" as
a result.

I was trying to figure out how to debug a plpgsql program. I finally figured
out that I could insert the values of node, and testing at each iteration of
the loop which in this case was the same as each time the module was called
recursively. I got

(3, 1)
(2, 1)
(1, 1)  --> At this point t_rec.node should have equaled testing and I would
have expected Circular to be returned. It didn't and I am completely
stumped.

There does seem to be a logical error with what you are doing though, but it
does not change the fact that the code seems to be acting incorrectly.

The reason your code seems to be illogical is that your hierarchy is
4-->3-->2-->1 where 4 is the top parent. Your code seems to be testing to
see whether inserting 4-->1 would cause a problem. That is you are saying
that the following is wrong

          4
         /  \
       3    1
      /
     2
    /
   1

It is not checking for a circular relationship. I believe that you should go
down the  hierarchy recursively and find that inserting 1-->4 causes a
circular relationship because 4 is ultimately a child of 4. I don't believe
that this is what your code is doing.

Another thing that does not affect your test but seems to be incorrect is
that I expected the row 4-->NULL to be in the table since 4 was at the top
of your hierarchy.

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "Stefano Vita Finzi" <stefano.vita@pronesis.it>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, May 20, 2003 9:52 AM
Subject: [GENERAL] plpgsql recursion


> Greetings!
> I have a table like:
>
> node parent
>    1       2
>    2       3
>    3       4
>
> Since i traverse this table with a recursive function, i want to avoid
> infinite recursion loop. I have wrote a function to check that a new
record
> does not create a circular dependency. The function i wrote is as follow:
>
> CREATE OR REPLACE FUNCTION dba_test(INTEGER,INTEGER) RETURNS TEXT AS '
>   DECLARE
>     traversing ALIAS FOR $1;
>     testing ALIAS FOR $2;
>     t_rec RECORD;
>   BEGIN
>     FOR t_rec IN SELECT node,parent FROM dba_test WHERE parent =
traversing
> LOOP
>       IF t_rec.node = testing THEN
>         RETURN ''Circular'';
>       ELSE
>         PERFORM dba_test(t_rec.node,testing);
>       END IF;
>     END LOOP;
>     RETURN ''ok'' || testing::text;
>   END;
> ' LANGUAGE 'plpgsql';
>
> I would use this function BEFORE inserting the new row. But if i try
SELECT
> dba_test(4,1); i don't have the result i expect. Can i you give me an hint
> where am i wrong?
>
> Thank you!
>
> Stefano Vita Finzi
> kluge@despammed.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>