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