Custom Operators Cannot be Found for Composite Type Values

"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 basic equivalence 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-semantic whitespace).

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