From: | Rajesh S <rajesh(dot)s(at)fincuro(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | operator is only a shell - Error |
Date: | 2024-03-15 10:25:45 |
Message-ID: | f0d571bc-4631-4214-b1be-73d15cbb4310@fincuro.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Thiemo Kellner | 2024-03-15 10:30:48 | Re: select results on pg_class incomplete |
Previous Message | Laurenz Albe | 2024-03-15 07:37:20 | Re: Seeing high query planning time on Azure Postgres Single Server version 11. |