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(dot)klaver(at)aklaver(dot)com