Re: Recursive functions with SPL
Paul Watson wrote:
>
> Gabriele Bartolini wrote:
> >
> > Ciao,
> >
> > Is it possible to create a recursive function with Stored
> > Procedures Language?
> >
> > I got this problem. I have a gerarchical relation between
> > records of the same table, like a tree. I got to perform a posticipate
> > view of the tree, retrieving the relationship (parent and son)
> > existing between all these entries.
> >
> > The record structure is:
> > ID
> > Description
> > ParentID (if 0 is at the top).
> >
> > Do you have any examples?
>
> You can do this, but don't recurse too deep. Check out the cdi archive
> Mark Stock posted some SPL that will do this about a year ago.
My word, you are right Paul! I had forgotten that. Although it WAS 18
months ago! ;-) Here it is:
------------------------------------------------------------------------
Subject: Re: need a SELECT script.
Date: Mon, 17 Aug 1998 23:35:25 +0200
From: "Mark D. Stock" <mdstock@informix.com>
Organization: Informix SA
To: DK <devendra.kumar@mci.com>
CC: informix-list@iiug.org, dk@pcisys.net
References: 1
DK wrote:
>
> I've a table which represents a tree structure:
>
> child_id | parent_id
> 2 1
> 3 2
> 4 3
> 8 3
> 5 4
> 6 4
> 9 8
>
> I need to select the
> 1. parent
> 2. child nodes
>
> of any given node.
RECURSION! Excellent! I accept the challenge. Don't know why, but if I
can do it in 4GL, then why not SPL.... Okay, okay, I'll read that list
later. :-)
So, starting with the following I presume:
------------------------------------------------------------------------
CREATE TABLE object
(
child_id SMALLINT,parent_id SMALLINT
);
------------------------------------------------------------------------
INSERT INTO object VALUES (2, 1);
INSERT INTO object VALUES (3, 2);
INSERT INTO object VALUES (4, 3);
INSERT INTO object VALUES (8, 3);
INSERT INTO object VALUES (5, 4);
INSERT INTO object VALUES (6, 4);
INSERT INTO object VALUES (9, 8);------------------------------------------------------------------------
> Like if I choose to find the parent of node=9 then i should get the
> results as
>
> 8
> 3
> 2
> 1
So being simplistic to start with, I assumed each child had only one
parent:
------------------------------------------------------------------------
CREATE PROCEDURE get_parent(child_id SMALLINT)
RETURNING SMALLINT;DEFINE parent_id SMALLINT;
LET parent_id = 0;
SELECT object.parent_id
INTO parent_id
FROM object
WHERE object.child_id = child_id;
IF parent_id != 0
THEN
RETURN parent_id WITH RESUME;
FOREACH EXECUTE PROCEDURE get_parent(parent_id) INTO parent_id
RETURN parent_id WITH RESUME;
END FOREACH;
END IF;
END PROCEDURE;
------------------------------------------------------------------------
EXECUTE PROCEDURE get_parent(9);------------------------------------------------------------------------
Which gives the desired results.
> and if I want to get the children of the node=3, then my result should
> be like
>
> 4
> 5
> 6
> 8
> 9
And a global replace of parent for child and child for parent, in three
passes you understand, gives...... a slight problem. But if we add one
more loop to handle multiple children, we get:
------------------------------------------------------------------------
CREATE PROCEDURE get_child(parent_id SMALLINT)
RETURNING SMALLINT;DEFINE child_id SMALLINT;
LET child_id = 0;
FOREACH SELECT object.child_id
INTO child_id
FROM object
WHERE object.parent_id = parent_id
IF child_id != 0
THEN
RETURN child_id WITH RESUME;
FOREACH EXECUTE PROCEDURE get_child(child_id)
INTO child_id
RETURN child_id WITH RESUME;
END FOREACH;
END IF;
END FOREACH;
END PROCEDURE;
------------------------------------------------------------------------
EXECUTE PROCEDURE get_child(3);------------------------------------------------------------------------
Which also gives the desired results.
> (I can read the results off the cursor?)
Indeed.
> I need a select query/script which can do this work and I can tune it to
> my
> requirements. Any suggestions will be welcome and if possible please
> e-mail me
> a copy of the solution at dk@pcisys.net
That's my first pass. It can probably stand improvement.
Hope it helps,
--
Mark.
+----------------------------------------------------------+-----------+
|Mark D. Stock - Informix SA http://www.informix.com |//////// /|
|mailto:mdstock@informix.com http://www.informix.com/idn |///// / //|
|http://www.iiug.org +-----------------------------------+//// / ///|
| Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////|
| Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////|
|Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////|
+----------------------+-----------------------------------+-----------+
------------------------------------------------------------------------
I'm sure you can work the description in for yourself Gabriele. ;-)
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /|
| http://www.informix.com http://www.informixhandbook.com |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |What year 2000 bug? year 2000 bug? |/// / ////|
| |year 2000 bug? year 2000 bug? year |// / /////|
| |2000 bug? year 2000 bug? year 1900 |/ ////////|
+----------------------+-----------------------------------+-----------+