Re: [HACKERS] Custom Operators Cannot be Found for Composite Type Values
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 OPERATOR3 = (json, json), FUNCTION1 json_cmp(json, json); This seems to work. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Custom Operators Cannot be Found for Composite Type Values
David E. Wheeler da...@justatheory.com writes: CREATE OPERATOR CLASS json_ops DEFAULT FOR TYPE JSON USING btree AS OPERATOR3 = (json, json), FUNCTION1 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Custom Operators Cannot be Found for Composite Type Values
On 03/08/2012 02:16 PM, Tom Lane wrote: David E. Wheelerda...@justatheory.com writes: CREATE OPERATOR CLASS json_ops DEFAULT FOR TYPE JSON USING btree AS OPERATOR3 = (json, json), FUNCTION1 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Custom Operators Cannot be Found for Composite Type Values
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Custom Operators Cannot be Found for Composite Type Values
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 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? Is this expected behavior? Is there something
about custom operators that they can’t be used to 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Custom Operators Cannot be Found for Composite Type Values
David E. Wheeler da...@justatheory.com writes:
Im doing some development with the new JSON type (actually, Andrews
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
