Hi, this looks good, but it does not work with DISTINCT.
CREATE TYPE annotation AS ( namespace varchar, name varchar, value varchar ); SELECT node.id as id, array_agg(DISTINCT ROW(namespace, name, value)::annotation) as annotation ... GROUP BY id produces: ERROR: could not identify an equality operator for type annotation I tried to create a custom operator like this: CREATE OR REPLACE FUNCTION annotation_equal(lhs annotation, rhs annotation) RETURNS boolean AS $$ BEGIN RETURN lhs.namespace = rhs.namespace AND lhs.name = rhs.name AND lhs.value = rhs.value; END; $$ LANGUAGE plpgsql IMMUTABLE; CREATE OPERATOR = ( PROCEDURE = annotation_equal, LEFTARG = annotation, RIGHTARG = annotation ); But it doesn't seem to get picked up. If I leave out the DISTINCT in the first SELECT query, it works as expected. I could leave it out, but then application logic would be more complex. (Well, not really, I'd just use a Set and not a List in Java, but it would increase the amount of data send over the network and the reason I'm aggregating in the first place is to minimize the data.) I've also tried this: CREATE OR REPLACE FUNCTION annotation_hash(rhs annotation) RETURNS INTEGER AS $$ BEGIN RETURN hashtext (rhs.namespace || '-' || rhs.name || '-' || rhs.value); END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; CREATE OPERATOR CLASS annotation_ops DEFAULT FOR TYPE annotation USING hash AS OPERATOR 1 =, FUNCTION 1 annotation_hash(rhs annotation) ; But then the error message changes to: ERROR: cache lookup failed for operator 34755 Any idea? Cheers, Viktor Sam Mason wrote: > On Wed, Oct 28, 2009 at 04:17:32PM +0100, Viktor Rosenfeld wrote: > > I'm trying to aggregate a list of table attributes into an array. > > I'd suggest using a tuple, arrays for things where each element means > the same thing. I'd guess you care about the substructure (i.e. the > element has a "namespace", a "name" and a "value") and hence using an > array in the first place seems wrong. Maybe something like: > > CREATE TYPE foo AS ( namespace TEXT, name TEXT, value TEXT ); > SELECT id, array_accum(row(a,b,c)::foo) > FROM data > GROUP BY id; > > > Why doesn't this work? > > Arrays of arrays aren't directly supported; you currently have to put > them into a tuple first. Something like: > > CREATE TYPE bar AS ( a TEXT[] ); > SELECT array_agg(row(array['a'])::bar); > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general