Thread: Custom Operators Cannot be Found for Composite Type Values

Hackers,

I’m doing some development with the new JSON type (actually, Andrew’s backport to 9.1) and needed to do some very basic
equivalencetesting. 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? Is this expected behavior? Is there something about custom operators that they can’t be
usedto compare the values of values in composite types? 

I’ve worked around it by writing a separate operator to compare ajson types using
   SELECT $1::text = $2::text

But it’s a bit annoying.

Thanks,

David

"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

On Mar 7, 2012, at 8:23 PM, Tom Lane wrote:

> 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.

Ah, okay. Just need more stuff, I guess:
   CREATE OR REPLACE FUNCTION json_cmp(       json,       json   ) RETURNS INTEGER LANGUAGE SQL STRICT IMMUTABLE AS $$
    SELECT bttextcmp($1::text, $2::text);   $$;
 
   CREATE OR REPLACE FUNCTION json_eq(       json,       json   ) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
   SELECT bttextcmp($1::text, $2::text) = 0;   $$;
 
   CREATE OPERATOR = (       LEFTARG   = json,       RIGHTARG  = json,       PROCEDURE = json_eq   );
   CREATE OPERATOR CLASS json_ops   DEFAULT FOR TYPE JSON USING btree AS   OPERATOR    3   =  (json, json),   FUNCTION
 1   json_cmp(json, json);
 

This seems to work.

Best,

David

"David E. Wheeler" <david@justatheory.com> writes:
>     CREATE OPERATOR CLASS json_ops
>     DEFAULT FOR TYPE JSON USING btree AS
>     OPERATOR    3   =  (json, json),
>     FUNCTION    1   json_cmp(json, json);

> This seems to work.

Urk.  You really ought to provide the whole opclass (all 5 operators).
I'm not sure what will blow up if you leave it like that, but it won't
be pretty.
        regards, tom lane

On 03/08/2012 02:16 PM, Tom Lane wrote:
> "David E. Wheeler"<david@justatheory.com>  writes:
>>      CREATE OPERATOR CLASS json_ops
>>      DEFAULT FOR TYPE JSON USING btree AS
>>      OPERATOR    3   =  (json, json),
>>      FUNCTION    1   json_cmp(json, json);
>> This seems to work.
> Urk.  You really ought to provide the whole opclass (all 5 operators).
> I'm not sure what will blow up if you leave it like that, but it won't
> be pretty.

Yeah. Note too that this is at best dubious:
    CREATE OR REPLACE FUNCTION json_cmp(        json,        json    ) RETURNS INTEGER LANGUAGE SQL STRICT IMMUTABLE AS
$$       SELECT bttextcmp($1::text, $2::text);    $$;
 


Two pieces of JSON might well be textually different but semantically 
identical (e.g. by one having additional non-semantic whitespace).


cheers

andrew

On Mar 8, 2012, at 11:16 AM, Tom Lane wrote:

>> This seems to work.
>
> Urk.  You really ought to provide the whole opclass (all 5 operators).
> I'm not sure what will blow up if you leave it like that, but it won't
> be pretty.

Yes, I expect to have to fill in gaps as I go. These are just for unit tests, so I’m not too worried about it (yet).

David

On Mar 8, 2012, at 11:27 AM, Andrew Dunstan wrote:

> Yeah. Note too that this is at best dubious:
>
>    CREATE OR REPLACE FUNCTION json_cmp(
>        json,
>        json
>    ) RETURNS INTEGER LANGUAGE SQL STRICT IMMUTABLE AS $$
>        SELECT bttextcmp($1::text, $2::text);
>    $$;
>
>
> Two pieces of JSON might well be textually different but semantically identical (e.g. by one having additional
non-semanticwhitespace). 

Yes. This is just for unit tests, and is fine for the moment. If I end up with abnormalities, I will likely rewrite
json_cmp()in Perl and use JSON::XS to do normalization. Not needed yet, though. 

Thanks,

David