Problem of varchar2 + integer

[r.taka...@jp.fujitsu.com]

Hi,

My customer has Oracle database and table like as follows.

SQL> DESC test;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 COL1                                               VARCHAR2(20)

SQL> select * from test;

COL1

------------------------------------------------------------

1

He has application that run sql like as follows.

SQL> select col1 + 1 from test;

    COL1+1

----------

         2

He wants to migrate this database to PostgreSQL using orafce, but this sql does not work as follows.

postgres=# \d test

        Table "public.test"

 Column |     Type     | Modifiers

--------+--------------+-----------

 col1   | varchar2(20) |

postgres=# select * from test;

 col1

------

 1

(1 row)

postgres=# select col1 + 1 from test;

ERROR:  operator is not unique: varchar2 + integer

LINE 1: select col1 + 1 from test;

                    ^

HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

I think the cause of this error is following.

There are following '+' operators that the right argument is integer.

postgres=# \doS+

                                                                                             List of operators

   Schema   | Name |        Left arg type        |       Right arg type        |         Result type         |         Function         |

       Description

------------+------+-----------------------------+-----------------------------+-----------------------------+--------------------------+------------------------------------------------------------------

...

 pg_catalog | +    | bigint                      | integer                     | bigint                      | int84pl                  | add

 pg_catalog | +    | date                        | integer                     | date                        | date_pli                 | add

 pg_catalog | +    | integer                     | integer                     | integer                     | int4pl                   | add

 pg_catalog | +    | smallint                    | integer                     | integer                     | int24pl                  | add

Implicit casts of varchar2 to these four types are defined as follows.

postgres=# \dC

                                         List of casts

         Source type         |         Target type         |      Function      |   Implicit?

-----------------------------+-----------------------------+--------------------+---------------

...

 varchar2                    | bigint                      | (binary coercible) | yes

 varchar2                    | date                        | (binary coercible) | yes

 varchar2                    | integer                     | (binary coercible) | yes

 varchar2                    | smallint                    | (binary coercible) | yes

Therefore, PostgreSQL cannot choose a '+' oeprator from these four operators.

I have no idea to solve this problem without using explicit cast. He does not want to modify sql.

Do you have any ideas?

Regards,

Ryohei Takahashi

[Pavel Stehule]

Hi

po 29. 10. 2018 v 2:11 odesílatel <r.taka...@jp.fujitsu.com> napsal:

It is too typical for old Oracle applications :-(. Data types was not important in these times.

You can create own operator (or set of operators).

postgres=# create or replace function sum(varchar2, int)
postgres-# returns int as $$ select $1::int + $2 $$ language sql;
CREATE FUNCTION
postgres=# create operator + (FUNCTION = sum, leftarg = varchar2, rightarg = int, commutator = +);
CREATE OPERATOR
postgres=# create table foo(a varchar2);
CREATE TABLE
postgres=# insert into foo values(10);
INSERT 0 1
postgres=# insert into foo values(20);
INSERT 0 1
postgres=# select a + 1 from foo;
┌──────────┐
│ ?column? │
╞══════════╡
│       11 │
│       21 │
└──────────┘
(2 rows)

Regards

Pavel

 

Regards,

Ryohei Takahashi

--
You received this message because you are subscribed to the Google Groups "Better Oracle functions support" group.
To post to this group, send email to orafce-...@googlegroups.com.
Visit this group at https://groups.google.com/group/orafce-general.

[r.taka...@jp.fujitsu.com]

Thank you for your advice.

I created '+' operator and the sql works.

Thank you.

Regards,

Ryohei Takahashi