pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Using 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>
  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 21:15  Roberto Mello <rmello@cc.usu.edu>
  parent: 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 21:36  Edmar Wiggers <edmar@brasmap.com>
  parent: Alvar Freude <alvar.freude@merz-akademie.de>
  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

* Re: Using Array-Values in subselect
@ 2000-11-14 21:48  Alvar Freude <alvar.freude@merz-akademie.de>
  parent: Roberto Mello <rmello@cc.usu.edu>
  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 22:31  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Alvar Freude <alvar.freude@merz-akademie.de>
  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 23:16  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  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-15 02:47  Alvar Freude <alvar.freude@merz-akademie.de>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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


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