Re: Custom Operators Cannot be Found for Composite Type Values - Mailing list pgsql-hackers

"David E. Wheeler" <david@justatheory.com> writes:
> I�m doing some development with the new JSON type (actually, Andrew�s backport to 9.1) and needed to do some very
basicequivalence testing. So I created a custom operator:
 

>     CREATE OR REPLACE FUNCTION json_eq(
>         json,
>         json
>     ) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
>         SELECT $1::text = $2::text;
>     $$;

>     CREATE OPERATOR = (
>         LEFTARG   = json,
>         RIGHTARG  = json,
>         PROCEDURE = json_eq
>     );

> With this in place, these work:

>      SELECT '{}'::json = '{}'::json;
>      SELECT ROW('{}'::json) = ROW('{}'::json);

> However this does not:

>     create type ajson AS (a json);
>     SELECT ROW('{}'::json)::ajson = ROW('{}'::json)::ajson;

> That last line emits an error:

>     ERROR:  could not identify an equality operator for type json

> To which my response was: WTF?

You have not told the system that your operator is equality for the
datatype.  It's just a random operator that happens to be named "=".
We try to avoid depending on operator names as cues to semantics.

You need to incorporate it into a default hash or btree opclass before
the composite-type logic will accept it as the thing to use for
comparing that column.
        regards, tom lane