[SOLVED] PostgreSQL Custom operator compare varchar and integer

PostgreSQL Custom operator compare varchar and integer

-- PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 64-bit.

-- Installed from offical repository.

-- No any changes in postgresql.conf .

-- CentOS release 6.8.

-- User: postgres.

-- Used pgAdmin3 LTS by BigSQL.

-- No any unusual log in server.

I have many queries.

in that case I need compare character varying data type (may be a table field) with integer value.

--Result is True or False

select '10' = 10; 
select '10' = '10'; 
select '10'::character varying = '10'::character varying; 
select '10'::character varying = 'foo bar'; 
select '10'::character varying = 'foo bar'::character varying; 
select 'foo bar' = 'foo bar'; 
select '10'::character varying = '10';

--Result is "operator does not exist: character varying = integer"

select '10'::character varying = 10;

so i create a custom operator for compare character varying and integer.

step 1: create simple function

CREATE OR REPLACE FUNCTION public.is_equal_char_int(character varying, integer) RETURNS boolean AS 
$BODY$ 
BEGIN 
    IF $1 = $2::character varying THEN
        RETURN TRUE;
    ELSE 
        RETURN FALSE; 
    END IF;
End;
$BODY$ 
LANGUAGE plpgsql VOLATILE COST 100;

step 2: create new operator

CREATE OPERATOR public.=( 
PROCEDURE = is_equal_char_int,
LEFTARG = character varying,
RIGHTARG = integer);

so i resoleved my problem and

select '10'::character varying = 10;

return true value.

and new problem is: when i compare character varying value with unkown data type value, postgresql use my custom operator.

select '10'::character varying = 'foo bar';

result is :

invalid input syntax for integer: "foo bar"

select pg_typeof('foo bar');

return unkown data type.

and next step I create new operator for compare character varying and unkown data type.

Step 1:

CREATE OR REPLACE FUNCTION public.is_equal_char_unknown(character varying, unknown)
RETURNS boolean AS
$BODY$
BEGIN
IF $1 = $2::character varying THEN
    RETURN TRUE;
ELSE
    RETURN FALSE;
END IF;
End;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

step 2:

CREATE OPERATOR public.=( 
PROCEDURE = is_equal_char_unknown,
LEFTARG = character varying,
RIGHTARG = unknown);

when I run

select '10'::character varying = 'foo bar';

I give

ERROR: operator is not unique: character varying = unknown.

So I'm in a hole.

Solution

There could be another option by defining how the cast between varchars and numerics should be made:

CREATE CAST (VARCHAR AS NUMERIC) WITH INOUT AS IMPLICIT;

This would make it possible to do comparisons like this:

SELECT '1'::character varying = 1::int;
> true

SELECT '01'::character varying = 1::int;
> true

SELECT '2'::character varying = 1::int;
> false

select '10'::character varying = 'foo bar';
> false

More about creating casts in postgresql here: https://www.postgresql.org/docs/current/sql-createcast.html