Thread: Oracle 'connect by prior' now eaiser in 7.3?
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
Merrall, Graeme wrote: > 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? > See connectby() in contrib/tablefunc. Someone was working on SQL99 recursive queries but it didn't get done for 7.4 -- perhaps it will be in 7.5. In the meantime, connectby() is in 7.3 and might work for you. HTH, Joe
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 >
Hello!
Can anyone help me to use connectby() with my structure?
I cannot change the name of tables. It is a import!
------------------------------------------
Thomas Wegner
"Thomas Wegner" <tomaten@t-online.de> schrieb im Newsbeitrag
news:bm1p2g$98o$1@news.hub.org...
> Hello,
>
> i have a table like this:
>
> CREATE TABLE "public"."WINUSER" (
> "ID_WINUSER" INTEGER NOT NULL,
> "STATUS" INTEGER NOT NULL,
> "CUSTOMERID" VARCHAR(8) NOT NULL,
> "CUSTOMERPW" VARCHAR(100) NOT NULL,
> "EMAIL" VARCHAR(100) NOT NULL,
> "REF_ID_WINUSER" INTEGER,
> PRIMARY KEY("ID_WINUSER"),
> ) WITH OIDS;
>
> and will get the tree from this to fields:
>
> "ID_WINUSER" INTEGER NOT NULL,
> "REF_ID_WINUSER" INTEGER,
>
> i write this sql:
>
> SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
> '"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
>
> and get this error:
>
> ERROR: Query-specified return tuple not valid for Connectby: wrong number
> of columns
>
> How is the correct use of connectby() for me?
> ------------------------------------------
> Thomas Wegner
>
> "Joe Conway" <mail@joeconway.com> schrieb im Newsbeitrag
> news:3F6FC51F.6090105@joeconway.com...
> > Merrall, Graeme wrote:
> > > 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?
> > >
> >
> > See connectby() in contrib/tablefunc. Someone was working on SQL99
> > recursive queries but it didn't get done for 7.4 -- perhaps it will be
> > in 7.5. In the meantime, connectby() is in 7.3 and might work for you.
> >
> > HTH,
> >
> > Joe
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>
>
Hello,
i have a table like this:
CREATE TABLE "public"."WINUSER" ( "ID_WINUSER" INTEGER NOT NULL, "STATUS" INTEGER NOT NULL, "CUSTOMERID" VARCHAR(8) NOT
NULL,"CUSTOMERPW" VARCHAR(100) NOT NULL, "EMAIL" VARCHAR(100) NOT NULL, "REF_ID_WINUSER" INTEGER, PRIMARY
KEY("ID_WINUSER"),
) WITH OIDS;
and will get the tree from this to fields:
"ID_WINUSER" INTEGER NOT NULL, "REF_ID_WINUSER" INTEGER,
i write this sql:
SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
'"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
and get this error:
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
How is the correct use of connectby() for me?
------------------------------------------
Thomas Wegner
"Joe Conway" <mail@joeconway.com> schrieb im Newsbeitrag
news:3F6FC51F.6090105@joeconway.com...
> Merrall, Graeme wrote:
> > 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?
> >
>
> See connectby() in contrib/tablefunc. Someone was working on SQL99
> recursive queries but it didn't get done for 7.4 -- perhaps it will be
> in 7.5. In the meantime, connectby() is in 7.3 and might work for you.
>
> HTH,
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
Thomas Wegner wrote:
>>SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
>>'"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
>>
>>and get this error:
>>
>>ERROR: Query-specified return tuple not valid for Connectby: wrong number
>>of columns
Please see the documentation (README.tablefunc). You need to properly
specify the column definitions in the FROM clause, i.e. (untested):
SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"','"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER"
integer, "REF_ID_WINUSER" integer, level integer, branch text);
HTH,
Joe
[sNip]
> See connectby() in contrib/tablefunc. Someone was working on SQL99
> recursive queries but it didn't get done for 7.4 -- perhaps it will be
> in 7.5. In the meantime, connectby() is in 7.3 and might work for you.
Oracle 8i doesn't seem to have any special indexing to handle this
efficiently. Do you happen to know if PostgreSQL will have a special
indexing option for this feature? If it does, it will very likely provide a
major performance advantage over Oracle.
--
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.