Re: Expressing a result set as an array (and vice versa)?

>> 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 ?