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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
