From: PFC <lists@peufeu.com>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Expressing a result set as an array (and vice versa)?
Date: Mon, 27 Mar 2006 17:12:49 +0200
Message-ID: <op.s62u7nfpcigqcu@apollo13> (raw)
In-Reply-To: <20060327134831.GJ80726@pervasive.com>
References: <F3E0C44C-6118-41C6-AC71-2B19258F4F07@genome.stanford.edu>
<op.s6xxmukycigqcu@apollo13>
<20060327134831.GJ80726@pervasive.com>
>> SELECT array_accum( DISTINCT list_id ) FROM bookmarks;
>> array_accum
>> ---------------
>> {1,2,3,4,5,7}
>
> Couldn't you just use array()?
Yes, you can do this :
SELECT ARRAY( SELECT something with one column );
However, array_accum() as an aggregate is more interesting because you
can use GROUP BY. For instance :
SELECT parent, array_accum( child ) FROM table GROUP BY parent;
I have another question. Suppose I have these tables :
CREATE TABLE items (
id SERIAL PRIMARY KEY,
category INTEGER NOT NULL,
name TEXT NOT NULL,
);
CREATE TABLE comments (
item_id INTEGER NOT NULL REFERENCES items(id),
id SERIAL PRIMARY KEY,
comment TEXT NOT NULL,
added TIMESTAMP NOT NULL DEFAULT now()
)
Say I want to display some items and the associated comments :
SELECT * FROM items WHERE category = ...
Then, I gather the item ids which were returned by this query, and do :
SELECT * FROM comments WHERE item_id IN ( the ids ) ORDER BY item_id,
added;
Is there a more elegant and efficient way which would avoid making a big
IN() query ? I could join comments with items, but in my case the search
condition on items is quite complicated and slow ; hence I only want to do
the search once. And I have several different tables in the same style of
the "comments" table, and so I make several queries using the same IN
(...) term. It isn't very elegant... is there a better way ? Use a
temporary table ? How do you do it ?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@peufeu.com, jnasby@pervasive.com
Subject: Re: Expressing a result set as an array (and vice versa)?
In-Reply-To: <op.s62u7nfpcigqcu@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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