could not identify an equality operator for type annotation (Was: could not find array type for data type character varying[]) - Mailing list pgsql-general

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