Re: Ugly group by problem

On 29 mar 2006, at 17.42, Achilleus Mantzios wrote:
> The _int_union trick is to force the arrays to have unique values.
> The order by has the meaning that '{5,23}' and '{23,5}' should be  
> treated
> the same way.

I didn't have the _int_union function. Is it internal? What PG- 
version did you use? I solved it using DISTINCT instead (perhaps  
making it a little simpler as well):

SELECT  ARRAY(SELECT DISTINCT feat_id FROM linktest WHERE  
link_id=t1.link_id ORDER BY feat_id) AS feat_group,  SUM(other)
FROM linktext t1
GROUP BY 1;
 feat_group | sum
-----------+-----
{2}        |   1
{5,23}     |  13
{23}       |  14
(3 rows)


Of course I haven't tested the performance on a table containing a  
lot of data... I'm not sure how the planner will treat the ARRAY- 
construct. You might be able to speed it up by turning it into a  
function marked STABLE, something like this:

CREATE OR REPLACE FUNCTION feat_group(INTEGER) RETURNS INTEGER[] AS $$
SELECT ARRAY(SELECT DISTINCT feat_id FROM linktest WHERE link_id=$1  
ORDER BY feat_id);
$$ LANGUAGE sql STABLE;

SELECT
feat_group(link_id),
SUM(other)
FROM linktext t1
GROUP BY 1;



Sincerely,

Niklas Johansson