problem is that if i give a resourceid i need to get all the childs recursively.
constraint is that this should be in a single query.
PostgreSQL does not yet have the ability to do recursive queries, as with "CONNECT BY" in Oracle or MSSQL. This will probably be available with the next major release, version 7.4. You can get developer snapshots of 7.4 and start browsing the newsgroup comp.databases.postgresql.hackers if this is really important. But, I wouldn't recommend running a production system on a developer snapshot. (Look for snapshots in the /dev section of the PostgreSQL download mirrors)