Thread: Using Array-Values in subselect
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
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
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?
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
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. :)
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
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