Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1o9lLf-000352-IC for pgsql-general@arkaria.postgresql.org; Fri, 08 Jul 2022 10:36:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1o9lLd-0003cv-3l for pgsql-general@arkaria.postgresql.org; Fri, 08 Jul 2022 10:36:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1o8dpS-000071-4O for pgsql-general@lists.postgresql.org; Tue, 05 Jul 2022 08:22:42 +0000 Received: from mail-pl1-x629.google.com ([2607:f8b0:4864:20::629]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1o8dpP-0000m5-7s for pgsql-general@lists.postgresql.org; Tue, 05 Jul 2022 08:22:41 +0000 Received: by mail-pl1-x629.google.com with SMTP id 5so4510514plk.9 for ; Tue, 05 Jul 2022 01:22:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=fincuro-com.20210112.gappssmtp.com; s=20210112; h=message-id:date:mime-version:user-agent:content-language:to:from :subject; bh=D1hiSq9bIw4AYy4hghjGHJXdf46AsEZAhADGZB3t1OM=; b=4EAHOhWD5pnPDxLELhmf+3d+5qbv/wE9QfEHCf/LFzvMt8img32upG8Kyih246O3UU +CYPTYZQyV750ZfiAFi4xqjDFvJLrMMypG15mlslyQwsL2BE2u5/MyP4MoIr/1MLRiVY elYpNHXWwM5w5PLqVeoNFYpxaoP1RCBPAp2HDY91kk12B7Fl8S0JLgBb2cNRiIR/pdcr yPUTeKp30tL4fgDSdjnUrMUCMRQGuv85Rlbu4cZT6Faey8WeWlOTLmqRI54B2SBsb1Bi nnoYdDI7oYM/1FkUOUgzZj0JUS/TKl2YgcK0/lbhcDgJ/Q10m9+TwUP3WjaRwR7HL0om sINg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent :content-language:to:from:subject; bh=D1hiSq9bIw4AYy4hghjGHJXdf46AsEZAhADGZB3t1OM=; b=NNedm82thMR1Q4vCNohUzfJbr2ly3c3OwBcIayvSvWnNpJnRtSN1jKgFBp8QEKJfxo 5Xf9VNobLe+ok+bO9L2zxn4rUQVjL97S5fhxZm/gRXCP2qvZLSgd0hHz+vz9PNHhZGZ/ Ve8UnVsieI4uEslUIx1ODG3awMAkRPtjc9x2BkVP4/yFJSEwxStVnWPQecbfn5bbm6Rr MwcP/7VKzTirzkocB/9fbVS/55Y8ApBStbhz4Yfy8eM009gzl1KGlFkg6zf8sSairoN/ 3GawZGFTLb6Q3ZHLkzaqkTdu6HmOfG5nHfcFfvqGrkuDwZBu7CduzfUD1vKpA+Q+WZ2i r59g== X-Gm-Message-State: AJIora9opcrjJz2EnzYtbHTjpoB77SCU6jzqQoTY0dbOMGY4tUGgZu5B 3rnnJmJZJMhhWv8rDCGXTovZaaZTIokSqw== X-Google-Smtp-Source: AGRyM1u3nl40SGmbmfhVOU+Hm+DP4KXQ9vsonwoE5IIfLglCvus/8QraYE8ngAvynqNm8XZU26LqRQ== X-Received: by 2002:a17:903:22c7:b0:16b:e0e5:b72 with SMTP id y7-20020a17090322c700b0016be0e50b72mr10635807plg.32.1657009356503; Tue, 05 Jul 2022 01:22:36 -0700 (PDT) Received: from [172.26.200.10] ([49.205.148.166]) by smtp.gmail.com with ESMTPSA id v3-20020aa799c3000000b005284d0fc634sm6100261pfi.97.2022.07.05.01.22.35 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 05 Jul 2022 01:22:36 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------s4wvQmqMPWoZQIxIuRjMfszW" Message-ID: <7f97243d-db62-199a-8412-5637963d117e@fincuro.com> Date: Tue, 5 Jul 2022 13:52:32 +0530 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101 Thunderbird/91.10.0 Content-Language: en-CA To: pgsql-general@lists.postgresql.org From: Rajesh S Subject: - operator overloading not giving expected result List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------s4wvQmqMPWoZQIxIuRjMfszW Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------s4wvQmqMPWoZQIxIuRjMfszW Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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


--------------s4wvQmqMPWoZQIxIuRjMfszW--