Recursive functions with SPL
Gabriele Bartolini
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?
Ciao
-David
Mark D. Stock
Paul Watson wrote:
> 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" <mds...@informix.com>
Organization: Informix SA
To: DK <devendr...@mci.com>
CC: inform...@iiug.org, d...@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 d...@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:mds...@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:mds...@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 |/ ////////|
+----------------------+-----------------------------------+-----------+