- operator overloading not giving expected result

From: Rajesh S <rajesh(dot)s(at)fincuro(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: - operator overloading not giving expected result
Date: 2022-07-05 08:22:32
Message-ID: 7f97243d-db62-199a-8412-5637963d117e@fincuro.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message houzj.fnst@fujitsu.com 2022-07-05 08:28:05 RE: Support logical replication of DDLs
Previous Message Matthias Apitz 2022-07-05 07:51:19 lifetime of the old CTID