Recursive functions with SPL

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? Ciao -David

Gabriele Bartolini <g.bartol@comune.prato.it> writes: > 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? Maybe this can give you some ideas? This is both my first SP, and it's recursive:) You should check for self-reference, and set a max limit for the iterations. The docs. are great btw -have you read them? Thomas --- DROP FUNCTION f; CREATE FUNCTION f (c_id INTEGER) -- Recuresive function returning this categorys name and parents names RETURNING VARCHAR(255); DEFINE n,n2 VARCHAR(255); DEFINE p INTEGER; LET n,p = (SELECT name, parent FROM c WHERE id = c_id); IF p IS NULL THEN RETURN n; ELSE EXECUTE FUNCTION f(p) INTO n2; RETURN n2 || ' -> ' || n; END IF END FUNCTION;

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. 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? > > Ciao > -David > -- Paul Watson # WF Software Ltd # You are only young once Tel: +44 1436 674728 # but you can be immature Fax: +44 1436 678693 # for ever www.wfsoftware.com #