if you need oracle's syntax and can recompile pgsql see patch at
http://gppl.terminal.ru/index.eng.html
regards, evgen potemkin
---
On Tue, 23 Sep 2003, Merrall, Graeme wrote:
>
> I'll start by admitting that I've not looked at it myself yet but I'm
> just wondering if any others have looked at this yet. A while ago we
> migrated a complex web-app from Oracle to postgres. It went very well
> when it came to replace 'connect by prior'. Because we were time poor
> we couldn't spend much time looking the best way to emulate this in
> postgres and the stored procs seemed complex. One look at the OpenACS
> information sent me screaming for the hills :)
>
> Given a table of
> PARENT_NODE_ID NOT NULL NUMBER
> CHILD_NODE_ID NOT NULL NUMBER
> ORDINAL NUMBER
>
> And Oracle SQL of
> select child_node_id, level
> from node_relationships
> connect by prior child_node_id = parent_node_id
> start with parent_node_id=10
> order by Hierarchy.Branch(level, ordinal)
>
> Am I right in thinking that recursive procedures and procs returning row
> sets would allow us to better emulate this behaviour? As anyone looked
> at it yet?
>
> Cheers,
> Graeme
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>