- operator overloading not giving expected result

2022-07-08 Thread Rajesh S 

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

Re: - operator overloading not giving expected result

2022-07-11 Thread Rajesh S 

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.

operator is only a shell - Error

2024-03-15 Thread Rajesh S 

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

Re: operator is only a shell - Error

2024-03-18 Thread Rajesh S 
Thank you for your response.  Actually, I was trying to address the 
following query.


select LIEN_AC_NO from deposit_lien where deposit_no='0002114029832' and 
deposit_sub_no='1' and unlien_dt is null and unlien_remarks is null;


In the above query "deposit_sub_no" column is "numeric" type and passing 
'1' (as varchar).  To address this I'd created the function and operator 
as I'd mentioned in the earlier mail.  Even the following query throws 
error after creating the function and operator.


select * from deposit_lien where deposit_no='0002114029832';

ERROR: operator is only a shell: character varying = numeric LINE 1: 
select * from deposit_lien where deposit_no='0002114029832' ^ SQL state: 
42883 Character: 44


In the above query "deposit_no" column is having "varchar" data type.  
But before creating the function and operator it was working fine.  
Tried dropping the same, even though the same error.  How to proceed now?



Thanks,

Rajesh S

On 15-03-2024 19:10, Greg Sabino Mullane wrote:

On Fri, Mar 15, 2024 at 6:26 AM Rajesh S  wrote:

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

Your operator has numeric on the left and varchar on the right. But 
your query is doing numeric on the RIGHT. Probably want to make a 
matching one to cover both cases.


Cheers,
Greg

Re: operator is only a shell - Error

2024-03-19 Thread Rajesh S 
Sorry Adrian, my bad. I'd mentioned "deposit_sub_no='1'" by mistake, 
actually it was "deposit_sub_no=cast(1 as varchar)". This was throwing 
error "SQL Error [42883]: ERROR: operator does not exist: numeric = 
character varying  Hint: No operator matches the given name and argument 
types. You might need to add explicit type casts.  Position: 19".  Then 
realized that "numeric=text" works but "numeric=varchar" does not.  I 
could resolve the problem by creating the following function and 
operator by commenting "CUMMUTATOR" and "NEGATOR".  Also the error 
"operator is only a shell" also vanished.  I'm just sharing the script 
for your reference.  Thank you very much for your valuable support.


CREATE OR REPLACE FUNCTION public.num_eq_varchar(
    numeric,
    varchar)
    RETURNS boolean
AS 'select case when $2 ~ ''^[0-9\.]+$'' then $1 operator(pg_catalog.=) 
cast($2 as numeric) else $1::varchar = $2 end;'

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
);


Thanks,

Rajesh S

On 18-03-2024 21:20, Adrian Klaver wrote:

On 3/18/24 00:05, Rajesh S wrote:
Thank you for your response.  Actually, I was trying to address the 
following query.


select LIEN_AC_NO from deposit_lien where deposit_no='0002114029832' 
and deposit_sub_no='1' and unlien_dt is null and unlien_remarks is null;


In the above query "deposit_sub_no" column is "numeric" type and 
passing '1' (as varchar).  To address this I'd created the function 
and operator as I'd mentioned in the earlier mail. Even the following 
query throws error after creating the function and operator.


select * from deposit_lien where deposit_no='0002114029832';

ERROR: operator is only a shell: character varying = numeric LINE 1: 
select * from deposit_lien where deposit_no='0002114029832' ^ SQL 
state: 42883 Character: 44


In the above query "deposit_no" column is having "varchar" data 
type.  But before creating the function and operator it was working 
fine.  Tried dropping the same, even though the same error.  How to 
proceed now?


Not clear to me what the problem is you are trying to solve?

On a stock Postgres install:

select 1::numeric = '1';
 ?column?
--
 t

select '0002114029832'::varchar = '0002114029832';
 ?column?
--
 t





Thanks,

Rajesh S

On 15-03-2024 19:10, Greg Sabino Mullane wrote:

On Fri, Mar 15, 2024 at 6:26 AM Rajesh S  wrote:

    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

Your operator has numeric on the left and varchar on the right. But 
your query is doing numeric on the RIGHT. Probably want to make a 
matching one to cover both cases.


Cheers,
Greg