Date: Mon, 27 Mar 2006 17:12:49 +0200
To: "Jim C. Nasby" <jnasby@pervasive.com>
Subject: Re: Expressing a result set as an array (and vice versa)?
Cc: pgsql-sql@postgresql.org
References: <F3E0C44C-6118-41C6-AC71-2B19258F4F07@genome.stanford.edu>
	<op.s6xxmukycigqcu@apollo13> <20060327134831.GJ80726@pervasive.com>
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.s62u7nfpcigqcu@apollo13>
In-Reply-To: <20060327134831.GJ80726@pervasive.com>
User-Agent: Opera M2/8.51 (Linux, build 1462)


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