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