How to overload the equality operator for primitive types (int, real...)?

user_18222330 0
Viewed 137

Is it possible to overload the existing equality operator in PostgreSQL to give a new definition of equality between two values of type int or real? I think it's against the rules of overloading but I wonder if there's a way to do it anyway. I tried this:

CREATE OPERATOR = ( LEFTARG = real ,
                    RIGHTARG = real,
                    PROCEDURE = new_equality,
                    COMMUTATOR = = ,
                    NEGATOR = !=
                  );

CREATE OR REPLACE FUNCTION new_equality (real, real) RETURNS BOOLEAN AS 
$$
SELECT abs ($1 - $2) < 0,2 ;
$$ LANGUAGE PL/PGSQL

But when I use the equality operator in a query I don't get any result.
I also tried to define the new_equality() function parameters as the type of my attributes like this:

CREATE OR REPLACE FUNCTION new_equality (Student.age%TYPE, Student.age%TYPE) RETURNS BOOLEAN 
AS
$$
SELECT abs ($1 - $2) < 0,2;
$$ lANGUAGE PL/PGSQL

But I get a notice saying that Postgres converts them to real and when I use the equality operator in a query I still don't get any result.

postgresql operator-overloading sql
2 Answers

Laurenz pointed out the immediate problem search_path. There is more.

Assuming Postgres 14, this would work:

CREATE OR REPLACE FUNCTION public.new_equality (real, real)  -- be explicit about the schema!
  RETURNS bool
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE COST 10
BEGIN ATOMIC
SELECT abs($1 - $2) < 0.2;
END;

CREATE OR REPLACE FUNCTION public.new_inequality (real, real)  -- be explicit about the schema!
  RETURNS bool
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE COST 10
BEGIN ATOMIC
SELECT abs($1 - $2) >= 0.2;
END;

CREATE OPERATOR public.= (             -- be explicit about the schema!
  LEFTARG    = real
, RIGHTARG   = real
, FUNCTION   = public.new_equality
, COMMUTATOR = OPERATOR(public.=)      -- be explicit about the schema!
, NEGATOR    = OPERATOR(public.!=)     -- must also exist
);

CREATE OPERATOR public.!= (
  LEFTARG    = real
, RIGHTARG   = real
, FUNCTION   = public.new_inequality
, COMMUTATOR = OPERATOR(public.!=)
, NEGATOR    = OPERATOR(public.=)
);

Use the OPERATOR() construct to call it:

SELECT id, real '0.1' OPERATOR(public.=) real '0.2';
SELECT id, real '0.1' OPERATOR(public.!=) real '0.2';
SELECT id, real '0.1' OPERATOR(public.<>) real '0.2';

db<>fiddle here - with more examples

Be aware of the higher operator precedence, possibly forcing parentheses where the plain operator wouldn't!

You must also define the NEGATOR you mention in the declaration. Using the built-in != would be contradicting nonsense. Create a matching operator, which you must refer to with schema-qualified syntax. The manual:

To give a schema-qualified operator name in com_op or the other optional arguments, use the OPERATOR() syntax [...]

Related:

Note that <> is an automatic alias of !=, and <> is the default inequality operator in SQL.

An unqualified = will be the standard equality operator (OPERATOR(pg_catalog.=)) while you don't mess with the search_path to demote pg_catalog - which you shouldn't! Demoting pg_catalog opens the door to all kinds of serious problems, since system objects are now hidden behind one or more other schemas. Don't do that unless you know exactly what you are doing. About the search_path:

This assumes at least Postgres 14. About BEGIN ATOMIC:

Using the keyword FUNCTION instead of the misleading PROCEDURE, which is still valid for backward compatibility. See:

Like a_horse_with_no_name suggested, it may be more convenient to use an operator symbol that is different from existing ones to avoid conflicts. Would still have standard (= higher) operator precedence than default comparison operators, and that cannot be changed easily.

user_9398600

Your code is invalid (it should be LANGUAGE sql), but your operator won't get used, because pg_catalog is implicitly first on the search_path.

You can either change search_path to put pg_catalog elsewhere:

SET search_path = public, pg_catalog;

or you can reference the operator explicitly:

SELECT 1::real OPERATOR(public.=) 1.1::real;
user_64643080
Related