Re: - operator overloading not giving expected result

Rajesh S <rajesh.s@xxxxxxxxxxx> · Mon, 11 Jul 2022 16:43:34 +0530

    Hi All,
    Thank you for your valuable comments and suggestions.  As it was
      pointed out we are using EDB Postgres and the language was
      mentioned as 'edbspl'.  We made to work by setting the search_path
      variable as follows:
    set search_path="$user", public, pg_catalog
    And also changed the Operator overloading function as below. 
      This gave us the expected results.  Thanks once again for all your
      comments.  Consider this thread as closed.

    CREATE OR REPLACE FUNCTION public.dt_minus_dt(dt1 DATE, dt2 DATE)

      RETURNS INTEGER as

      $$

          SELECT (to_char(dt1::date,'J'::varchar) -
      to_char(dt2::date,'J'::varchar))::integer

      $$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE COST 1;

      CREATE OPERATOR public.- (

        PROCEDURE = dt_minus_dt,

        LEFTARG = DATE,

        RIGHTARG = DATE

      );

                        Thanks,
                        Rajesh
                            S

    On 08-07-2022 08:05 pm, Ravi Krishna
      wrote:

      LANGUAGE 'edbspl'

This is the root cause of your issue.

You are not using PGSQL, but EDB version of it which is compatible with Oracle PL/SQL.