Re: operator is only a shell - Error

Sorry Adrian, my bad. I'd mentioned "deposit_sub_no='1'" by mistake,
actually it was "deposit_sub_no=cast(1 as varchar)". This was throwing
error "SQL Error [42883]: ERROR: operator does not exist: numeric =
character varying  Hint: No operator matches the given name and argument
types. You might need to add explicit type casts.  Position: 19".  Then
realized that "numeric=text" works but "numeric=varchar" does not.  I
could resolve the problem by creating the following function and
operator by commenting "CUMMUTATOR" and "NEGATOR".  Also the error
"operator is only a shell" also vanished.  I'm just sharing the script
for your reference.  Thank you very much for your valuable support.

CREATE OR REPLACE FUNCTION public.num_eq_varchar(
    numeric,
    varchar)
    RETURNS boolean
AS 'select case when $2 ~ ''^[0-9\.]+$'' then $1 operator(pg_catalog.=)
cast($2 as numeric) else $1::varchar = $2 end;'
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
);

Thanks,

Rajesh S

On 18-03-2024 21:20, Adrian Klaver wrote:
> On 3/18/24 00:05, Rajesh S wrote:
>> Thank you for your response.  Actually, I was trying to address the
>> following query.
>>
>> select LIEN_AC_NO from deposit_lien where deposit_no='0002114029832'
>> and deposit_sub_no='1' and unlien_dt is null and unlien_remarks is null;
>>
>> In the above query "deposit_sub_no" column is "numeric" type and
>> passing '1' (as varchar).  To address this I'd created the function
>> and operator as I'd mentioned in the earlier mail. Even the following
>> query throws error after creating the function and operator.
>>
>> select * from deposit_lien where deposit_no='0002114029832';
>>
>> ERROR: operator is only a shell: character varying = numeric LINE 1:
>> select * from deposit_lien where deposit_no='0002114029832' ^ SQL
>> state: 42883 Character: 44
>>
>> In the above query "deposit_no" column is having "varchar" data
>> type.  But before creating the function and operator it was working
>> fine.  Tried dropping the same, even though the same error.  How to
>> proceed now?
>
> Not clear to me what the problem is you are trying to solve?
>
> On a stock Postgres install:
>
> select 1::numeric = '1';
>  ?column?
> ----------
>  t
>
> select '0002114029832'::varchar = '0002114029832';
>  ?column?
> ----------
>  t
>
>
>>
>>
>> Thanks,
>>
>> Rajesh S
>>
>> On 15-03-2024 19:10, Greg Sabino Mullane wrote:
>>> On Fri, Mar 15, 2024 at 6:26 AM Rajesh S <rajesh(dot)s(at)fincuro(dot)com> wrote:
>>>
>>>     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
>>>
>>> Your operator has numeric on the left and varchar on the right. But
>>> your query is doing numeric on the RIGHT. Probably want to make a
>>> matching one to cover both cases.
>>>
>>> Cheers,
>>> Greg
>>>
>