pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing Array-Values in subselect
7+ messages / 5 participants
[nested] [flat]
* Using Array-Values in subselect
@ 2000-11-14 20:51 Alvar Freude <alvar.freude@merz-akademie.de>
2000-11-14 21:15 ` Re: Using Array-Values in subselect Roberto Mello <rmello@cc.usu.edu>
2000-11-14 21:36 ` RE: Using Array-Values in subselect Edmar Wiggers <edmar@brasmap.com>
0 siblings, 2 replies; 7+ messages in thread
From: Alvar Freude @ 2000-11-14 20:51 UTC (permalink / raw)
To: pgsql-sql
Hi,
i want to create a linked structure of values. Each text has an id, an
array of children ids and the value itself.
CREATE TABLE structure
(
id int8,
children int8[],
value text
);
Now i want to select all values which are connected to a given parent #x
at once. My first idea was this:
SELECT value
FROM structure
WHERE id IN (SELECT children FROM structure WHERE id = #x);
But this causes an error because the array field just returns a string
instead of seperated values. Is there a way to make arrays return sort
of "real arrays" or something usable in a subselect in reasonable speed?
Thank you
Alvar
--
Alvar C.H. Freude | alvar.freude@merz-akademie.de
Demo: http://www.online-demonstration.org/ | Mach mit!
Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit
Blast-EN: http://www.a-blast.org/ | Blast/english
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using Array-Values in subselect
2000-11-14 20:51 Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
@ 2000-11-14 21:15 ` Roberto Mello <rmello@cc.usu.edu>
2000-11-14 21:48 ` Re: Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
1 sibling, 1 reply; 7+ messages in thread
From: Roberto Mello @ 2000-11-14 21:15 UTC (permalink / raw)
To: Alvar Freude <alvar.freude@merz-akademie.de>; +Cc: pgsql-sql
Alvar Freude wrote:
>
> Hi,
>
> i want to create a linked structure of values. Each text has an id, an
> array of children ids and the value itself.
Looks like you want something similar to Oracle's CONNECT BY statement.
There are some solutions to that. At OpenACS we had to deal with that so
implemented something like what you described here.
However, the methods described by Joe Celko is his book "SQL For
Smarties" on chapters 28 and 29 (I think) are better and more robust. If
you search for "trees" and related topics at the openacs.org and
arsdigita.com's web/db web bulletin boards, you'll find several hits.
-Roberto Mello
P.S: I know this doesn't directly anwser your question, but points you
to where to find the answer for yourself.
--
Computer Science Utah State University
Space Dynamics Laboratory Web Developer
USU Free Software & GNU/Linux Club http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using Array-Values in subselect
2000-11-14 20:51 Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
2000-11-14 21:15 ` Re: Using Array-Values in subselect Roberto Mello <rmello@cc.usu.edu>
@ 2000-11-14 21:48 ` Alvar Freude <alvar.freude@merz-akademie.de>
2000-11-14 22:31 ` Re: Using Array-Values in subselect Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 7+ messages in thread
From: Alvar Freude @ 2000-11-14 21:48 UTC (permalink / raw)
To: pgsql-sql
Roberto Mello schrieb:
> Looks like you want something similar to Oracle's CONNECT BY statement.
> There are some solutions to that. At OpenACS we had to deal with that so
> implemented something like what you described here.
> However, the methods described by Joe Celko is his book "SQL For
> Smarties" on chapters 28 and 29 (I think) are better and more robust. If
> you search for "trees" and related topics at the openacs.org and
> arsdigita.com's web/db web bulletin boards, you'll find several hits.
Thanks, the solutions discussed on these sites suggest the use of lookup
tables with parent and child for each record. I have thought about the
same, but thought the arrays in Postgres could provide a more elegant
way of building a tree.
I want to migrate from MySQL to Postgres mainly because the array and
subselect features. And finally the article at
http://www.phpbuilder.com/columns/tim20001112.php3 convinced me it could
run on my small machine. But it appears to me that the arrays always are
handled like strings and are not really useful for anything advanced. I
can't really believe it, please prove me wrong! I have set great hope in
Postgres. :)
Alvar
--
Alvar C.H. Freude | alvar.freude@merz-akademie.de
Demo: http://www.online-demonstration.org/ | Mach mit!
Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit
Blast-EN: http://www.a-blast.org/ | Blast/english
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using Array-Values in subselect
2000-11-14 20:51 Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
2000-11-14 21:15 ` Re: Using Array-Values in subselect Roberto Mello <rmello@cc.usu.edu>
2000-11-14 21:48 ` Re: Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
@ 2000-11-14 22:31 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-11-14 23:16 ` Re: Using Array-Values in subselect Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Stephan Szabo @ 2000-11-14 22:31 UTC (permalink / raw)
To: Alvar Freude <alvar.freude@merz-akademie.de>; +Cc: pgsql-sql
If you look in contrib of the source, there is a set of
array operators(functions) including element in set.
That'll probably do what you want (you don't do an in
actually, it'll be like <elem> <op> <array column>)
On Tue, 14 Nov 2000, Alvar Freude wrote:
> Roberto Mello schrieb:
> > Looks like you want something similar to Oracle's CONNECT BY statement.
> > There are some solutions to that. At OpenACS we had to deal with that so
> > implemented something like what you described here.
> > However, the methods described by Joe Celko is his book "SQL For
> > Smarties" on chapters 28 and 29 (I think) are better and more robust. If
> > you search for "trees" and related topics at the openacs.org and
> > arsdigita.com's web/db web bulletin boards, you'll find several hits.
>
> Thanks, the solutions discussed on these sites suggest the use of lookup
> tables with parent and child for each record. I have thought about the
> same, but thought the arrays in Postgres could provide a more elegant
> way of building a tree.
>
> I want to migrate from MySQL to Postgres mainly because the array and
> subselect features. And finally the article at
> http://www.phpbuilder.com/columns/tim20001112.php3 convinced me it could
> run on my small machine. But it appears to me that the arrays always are
> handled like strings and are not really useful for anything advanced. I
> can't really believe it, please prove me wrong! I have set great hope in
> Postgres. :)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using Array-Values in subselect
2000-11-14 20:51 Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
2000-11-14 21:15 ` Re: Using Array-Values in subselect Roberto Mello <rmello@cc.usu.edu>
2000-11-14 21:48 ` Re: Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
2000-11-14 22:31 ` Re: Using Array-Values in subselect Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2000-11-14 23:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-11-15 02:47 ` Re: Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2000-11-14 23:16 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Alvar Freude <alvar.freude@merz-akademie.de>; pgsql-sql
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> If you look in contrib of the source, there is a set of
> array operators(functions) including element in set.
> That'll probably do what you want (you don't do an in
> actually, it'll be like <elem> <op> <array column>)
The array stuff is pretty simplistic at the moment, and could be taken
a lot further if there were someone who wanted to work on it.
(hint hint)
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using Array-Values in subselect
2000-11-14 20:51 Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
2000-11-14 21:15 ` Re: Using Array-Values in subselect Roberto Mello <rmello@cc.usu.edu>
2000-11-14 21:48 ` Re: Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
2000-11-14 22:31 ` Re: Using Array-Values in subselect Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-11-14 23:16 ` Re: Using Array-Values in subselect Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-11-15 02:47 ` Alvar Freude <alvar.freude@merz-akademie.de>
0 siblings, 0 replies; 7+ messages in thread
From: Alvar Freude @ 2000-11-15 02:47 UTC (permalink / raw)
To: pgsql-sql
Tom Lane schrieb:
>
> The array stuff is pretty simplistic at the moment, and could be taken
> a lot further if there were someone who wanted to work on it.
> (hint hint)
:)
If i had time, this would be interesting, but I think my C experiences
are not very good and some years old (I like much more 68k Assembler --
or Perl) and i have to finish my Diploma work in january:
Art&Media-Design, no informatics ... ;)
Ciao
Alvar
--
Alvar C.H. Freude | alvar.freude@merz-akademie.de
Demo: http://www.online-demonstration.org/ | Mach mit!
Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit
Blast-EN: http://www.a-blast.org/ | Blast/english
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Using Array-Values in subselect
2000-11-14 20:51 Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
@ 2000-11-14 21:36 ` Edmar Wiggers <edmar@brasmap.com>
1 sibling, 0 replies; 7+ messages in thread
From: Edmar Wiggers @ 2000-11-14 21:36 UTC (permalink / raw)
To: Alvar Freude <alvar.freude@merz-akademie.de>; pgsql-sql
IMHO you should use another table instead of an array.
Forget about reasonable speed when using IN sub-queries, you'll get a
sequential scan of the sub-query for every row in the master select. I've
heard the EXISTS operator provides far better performance.
In 7.1, there's a very nice solution: use the sub-query in the FROM clause
and make joins to it. Should be MUCH faster.
> But this causes an error because the array field just returns a string
> instead of seperated values. Is there a way to make arrays return sort
> of "real arrays" or something usable in a subselect in reasonable speed?
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2000-11-15 02:47 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-14 20:51 Using Array-Values in subselect Alvar Freude <alvar.freude@merz-akademie.de>
2000-11-14 21:15 ` Roberto Mello <rmello@cc.usu.edu>
2000-11-14 21:48 ` Alvar Freude <alvar.freude@merz-akademie.de>
2000-11-14 22:31 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-11-14 23:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-11-15 02:47 ` Alvar Freude <alvar.freude@merz-akademie.de>
2000-11-14 21:36 ` Edmar Wiggers <edmar@brasmap.com>
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