pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOracle 'connect by prior' now eaiser in 7.3?
7+ messages / 5 participants
[nested] [flat]
* Oracle 'connect by prior' now eaiser in 7.3?
@ 2003-09-23 02:41 Merrall, Graeme <gmerrall@team.aol7.com.au>
0 siblings, 2 replies; 7+ messages in thread
From: Merrall, Graeme @ 2003-09-23 02:41 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle 'connect by prior' now eaiser in 7.3?
@ 2003-09-23 03:59 Joe Conway <mail@joeconway.com>
parent: Merrall, Graeme <gmerrall@team.aol7.com.au>
1 sibling, 2 replies; 7+ messages in thread
From: Joe Conway @ 2003-09-23 03:59 UTC (permalink / raw)
To: Merrall, Graeme <gmerrall@team.aol7.com.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle 'connect by prior' now eaiser in 7.3?
@ 2003-09-26 14:04 Evgen Potemkin <evgent@ns.terminal.ru>
parent: Merrall, Graeme <gmerrall@team.aol7.com.au>
1 sibling, 0 replies; 7+ messages in thread
From: Evgen Potemkin @ 2003-09-26 14:04 UTC (permalink / raw)
To: Merrall, Graeme <gmerrall@team.aol7.com.au>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle 'connect by prior' now eaiser in 7.3?
@ 2003-10-08 19:38 Thomas Wegner <tomaten@t-online.de>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 7+ messages in thread
From: Thomas Wegner @ 2003-10-08 19:38 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle 'connect by prior' now eaiser in 7.3?
@ 2003-10-10 08:09 Thomas Wegner <tomaten@t-online.de>
parent: Thomas Wegner <tomaten@t-online.de>
0 siblings, 1 reply; 7+ messages in thread
From: Thomas Wegner @ 2003-10-10 08:09 UTC (permalink / raw)
To: pgsql-sql
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
> >
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle 'connect by prior' now eaiser in 7.3?
@ 2003-10-14 17:45 Joe Conway <mail@joeconway.com>
parent: Thomas Wegner <tomaten@t-online.de>
0 siblings, 0 replies; 7+ messages in thread
From: Joe Conway @ 2003-10-14 17:45 UTC (permalink / raw)
To: Thomas Wegner <tomaten@t-online.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle 'connect by prior' now eaiser in 7.3?
@ 2003-11-17 04:35 Randolf Richardson, DevNet SysOp 29 <rr@8x.ca>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 7+ messages in thread
From: Randolf Richardson, DevNet SysOp 29 @ 2003-11-17 04:35 UTC (permalink / raw)
To: pgsql-sql
[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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-11-17 04:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-23 02:41 Oracle 'connect by prior' now eaiser in 7.3? Merrall, Graeme <gmerrall@team.aol7.com.au>
2003-09-23 03:59 ` Joe Conway <mail@joeconway.com>
2003-10-08 19:38 ` Thomas Wegner <tomaten@t-online.de>
2003-10-10 08:09 ` Thomas Wegner <tomaten@t-online.de>
2003-10-14 17:45 ` Joe Conway <mail@joeconway.com>
2003-11-17 04:35 ` Randolf Richardson, DevNet SysOp 29 <rr@8x.ca>
2003-09-26 14:04 ` Evgen Potemkin <evgent@ns.terminal.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox