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