operator is only a shell - Error

[Rajesh S <rajesh.s@xxxxxxxxxxx>]

Hi,

I wanted to implement a new "=" (equal) operator with LEFTARG as numeric and RIGHTARG as varchar.  But after creating the function and operator, psql shows the error "operator is only a shell: character varying = numeric LINE 1: ...lect LIEN_AC_NO from deposit_lien where deposit_no='00021140...".   I'm sharing the function and operator scripts for your perusal.  Please advise how to proceed.

CREATE OR REPLACE FUNCTION public.num_eq_varchar(
    numeric,
    varchar)
    RETURNS boolean
AS 'select $1::NUMERIC=CAST($2 AS numeric);'
LANGUAGE SQL IMMUTABLE;

-- Operator: =;

-- DROP OPERATOR IF EXISTS public.= (numeric , varchar);

CREATE OPERATOR public.= (
    FUNCTION = num_eq_varchar,
    LEFTARG = numeric,
    RIGHTARG = varchar,
    COMMUTATOR = =,
    NEGATOR = <>,
    RESTRICT = eqsel,
    JOIN = eqjoinsel,
    HASHES, MERGES
);

CREATE OR REPLACE FUNCTION public.num_ne_varchar(
    numeric,
    varchar)
    RETURNS boolean
    LANGUAGE SQL IMMUTABLE
AS $BODY$
    select $1<>$2::numeric;
$BODY$;

-- Operator: <>;

-- DROP OPERATOR IF EXISTS public.<> (numeric , varchar);

CREATE OPERATOR public.<> (
    FUNCTION = num_ne_varchar,
    LEFTARG = numeric,
    RIGHTARG = varchar,
    COMMUTATOR = <>,
    NEGATOR = =,
    RESTRICT = neqsel,
    JOIN = neqjoinsel
);

Thanks,

Rajesh S

 

 

On 05-07-2022 13:52, Rajesh S wrote:

Hi,

We are migrating our database from Oracle to Postgresql.  In oracle we have used this syntax "SELECT ('1999-12-30'::DATE) - ('1999-12-11'::DATE)" to get difference between two dates as a integer output (ex: 19).  But in Postgres the same query returns result as "19 days".  Because of this we are getting errors while assigning this query output to a numeric variable saying "ERROR: invalid input syntax for type numeric: "1825 days"" and "ERROR: operator does not exist: interval + integer".  To avoid changing the application code in many places to extract the number of days alone, we tried operator overloading concept as below.

CREATE OR REPLACE FUNCTION public.dt_minus_dt(
    dt1 timestamp without time zone,
    dt2 timestamp without time zone)
    RETURNS integer
    LANGUAGE 'edbspl'
    COST 100
    VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
    days INTEGER;
BEGIN
    SELECT DATE_PART('day', dt1::timestamp - dt2::timestamp)::integer INTO days;
    RETURN days;
END
$BODY$;

CREATE OPERATOR public.- (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

When we execute "SELECT ('1999-12-30'::DATE) - ('1999-12-11'::DATE)", we are still getting "19 days" as result and not "19" as we expect.  The above same function works as expected for the operator + or ===. 

CREATE OPERATOR public.+ (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

SELECT ('1999-12-30'::DATE) + ('1999-12-11'::DATE)

CREATE OPERATOR public.=== (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

SELECT ('1999-12-30'::DATE) === ('1999-12-11'::DATE)

I really appreciate anyone's help in resolving this case.  Thanks in advance.

Rajesh S