Search code examples
sqlpostgresqloperator-overloading

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

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.

Solution

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

    This would work:

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

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

CREATE OPERATOR public.= (             -- explicit schema!
  LEFTARG    = real
, RIGHTARG   = real
, FUNCTION   = public.new_equality
, COMMUTATOR = OPERATOR(public.=)      -- explicit 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';

    fiddle - with more examples

    The syntax for "standard SQL" functions requires at least Postgres 14. See:

    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, refer to it 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:

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

    Like suggested in the comments, 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.