Обсуждение: recursive function
hi,
though I have worked for a while with SQLServer2000 but I am new to Postgres (and also new to using mailing lists), so please bear with me if I am slower to pick these up.
I am trying to write a function in plpgsql that returns a group of people.
The group is defined by a set of conditions. These conditions are stored in a separate table so that it is configurable by the user.
So far so good, but here comes the trick: one of the conditions could be that people are already members of a parent group – so I need to call this function recursively.
At the top level the groupid=parentgroupid
So what I am trying to do is basically
Function dyn_group (groupheaderid)
If
groupid=parenttgroupid then return all people
Else
Select from
dyngroup(groupparentid) INNER JOIN people WHERE …all sorts of conditions
Endif
So what it should do is to look up if a group has parent, if yes look up, if it has parent and so on until we get a groupid=parentgroupid where it returns all people, use that in the INNER JOIN and return a recordset, than use this recordset in the INNER JOIN, return a recordset and so on until the original function returns the recordset that contains people who are members of all these groups.
In practice however this doesn’t seem to work as the function never returns.
Should this work in theory? Is this the recommended approach?
Thanks for the help.
SWK
On Tue, Dec 20, 2005 at 06:58:41PM +0100, Klein Bal?zs wrote: > In practice however this doesn?t seem to work as the function never returns. > > Should this work in theory? Is this the recommended approach? I can' think of any reason why it wouldn't work; have you tried adding RAISE statements to see what's actually happening? Also, you should look at contrib/ltree; it might be a better way to do what you're trying to do. Celko's book SQL For Smarties also has some different ways to store hierarchies; unfortunately it's not handy or I could give you some google search terms, but the book's worthy buying anyway. Apparently he's also got a book that's dedicated to hierarchies and graphs, but I don't know how good it is. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461