pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rajesh S <rajesh.s@fincuro.com>
To: pgsql-general@lists.postgresql.org
Subject: - operator overloading not giving expected result
Date: Tue, 5 Jul 2022 13:52:32 +0530
Message-ID: <7f97243d-db62-199a-8412-5637963d117e@fincuro.com> (raw)

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

view thread (7+ messages)  latest in thread

Message-ID: <7f97243d-db62-199a-8412-5637963d117e@fincuro.com>
Permalink:  ../7f97243d-db62-199a-8412-5637963d117e@fincuro.com/
Also on:    postgresql.org/message-id/7f97243d-db62-199a-8412-5637963d117e@fincuro.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: rajesh.s@fincuro.com
  Subject: Re: - operator overloading not giving expected result
  In-Reply-To: <7f97243d-db62-199a-8412-5637963d117e@fincuro.com>
  References: <7f97243d-db62-199a-8412-5637963d117e@fincuro.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox