Re: operator is only a shell - Error

On 3/19/24 00:02, Rajesh S wrote:
> 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 

1) Maybe you could explain the logic of taking a number and casting it 
to a string to compare it to a number?

2) select 1::varchar = 1::varchar;
  ?column?
----------
  t

So:

deposit_sub_no::varchar = 1::varchar


> 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
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com