Using postgresql for a hierarchy, and I came up with this to find the immediate children of a node. Assuming that the parent node has:
nodeleft = $L
noderght = $R
First we have a table of all the children. In a separate table, we finds all nodes that are one level deeper than the parent node. We do this by first retrieving the path to the root, then counting the records in that path. Then it returns the common nodes of the two tables.
SELECT * FROM node AS a
INNER JOIN node AS b ON a.nodeid=b.nodeid
WHERE a.nodeleft>$L AND a.noderght<$R
AND (SELECT COUNT(nodeid) FROM node WHERE nodeleft<=b.nodeleft AND noderght>=b.noderght)=(SELECT COUNT(nodeid) FROM node WHERE nodeleft<=$L AND noderght>=$R)+1
ORDER BY a.nodeleft ASC;
I don't know enough about PostgreSQL's internals to know how optimal this operation is. For a small number of nodes, it's trivial, but it would take a significant amount of time on a large set if PostgreSQL isn't smart enough to satisfy the first table first, which would significantly reduce the result set that it has to look at.
I haven't tried Weirdan's method (mentioned above) yet, because I don't understand it. If anyone has better, please post it.