Re: function to_char(unknown) is not unique at character 8

čt 6. 7. 2023 v 16:28 odesílatel gzh <gzhcoder(at)126(dot)com> napsal:

> Thank you for the solution, it works fine.
>
>
> > I have a question. Why do you use the to_char(string) function? Instead
> to_char('text') you can write only 'text'.
> I'm guessing it's probably a bug made by the original developer,
> but I'm not sure how many bugs there are, because it works fine in older
> version(orafce 3.13).
>

you can use another version

CREATE OR REPLACE FUNCTION oracle.to_char(text)
RETURNS text AS $$
BEGIN
RAISE WARNING 'using useless to_char function';
RETURN $1;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

This can help to identify these useless usage of this function

Unfortunately, the Oracle type system is very different, so it is hard to
emulate in Postgres. And Oracle PL/SQL design allows people to write
terribly ugly code. These bad things are hard to repeat in Pg.

>
>
> At 2023-07-06 19:54:19, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>
>
> čt 6. 7. 2023 v 13:38 odesílatel gzh <gzhcoder(at)126(dot)com> napsal:
>
>> Thank you very much for taking the time to reply to my question.
>> There are a lot of TO_CHAR in my application, and there is a high cost of
>> modifying the code,
>> is there another solution to solve the problem without modifying the code?
>>
>
> There is one dirty workaround, and because it is dirty, I don't want to
> push it to orafce.
>
> You can create own function to_char
>
> CREATE OR REPLACE FUNCTION oracle.to_char(text)
> RETURNS text AS $$
> SELECT $1
> $$ LANGUAGE sql IMMUTABLE STRICT;
>
> This version will be preferred and fix this issue. On second thought, the
> behavior can be a little bit different than before.
>
> I have a question. Why do you use the to_char(string) function? Instead
> to_char('text') you can write only 'text'.
>
>
>
>>
>>
>> At 2023-07-06 19:21:24, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>
>> Hi
>>
>> čt 6. 7. 2023 v 11:19 odesílatel gzh <gzhcoder(at)126(dot)com> napsal:
>>
>>> Hi,
>>>
>>>
>>> I upgraded the version of PostgreSQL from 12.6 to 12.13,
>>>
>>> when I execute the sql below , the to_char function caused the following
>>> error.
>>>
>>>
>>> ---------------SQL------------------
>>>
>>> select TO_CHAR('1000000');
>>>
>>>
>>> ERROR: function to_char(unknown) is not unique at character 8
>>>
>>> HINT: Could not choose a best candidate function. You might need to add
>>> explicit type casts.
>>>
>>>
>>> There is no problem before the upgrade and to_char function comes from
>>> the Orafce extension.
>>>
>>> The configuration of the old and new databases is as follows.
>>>
>>>
>>> Database server (old): PostgreSQL 12.6（orafce3.13）
>>>
>>> Database server (new): PostgreSQL 12.13（orafce3.24）
>>>
>>>
>>> The new database has successfully installed the orafce 3.24 extension.
>>>
>>> It does not occur in "PostgreSQL 12.6 and orafce 3.13",
>>>
>>> but occurs in "PostgreSQL 12.13 and orafce 3.24",
>>>
>>> so either the difference between 12.6 and 12.13 or the difference
>>> between orafce 3.13 and 3.24 is suspicious.
>>>
>>>
>>> What is the reason for the problem and how to fix the error?
>>>
>>
>> The new orafce contains to_char for numeric types and to_char for
>> timestamp. Old orafce had to_char function (with one argument) only for
>> numeric types.
>>
>> This is the reason why the system doesn't know if a string of unknown
>> type (postgresql string literal) should be processed as numeric or as a
>> timestamp.
>>
>> The best fix is specify used type like
>>
>> (2023-07-06 13:19:33) postgres=# select oracle.TO_CHAR('1000000'::int);
>> ┌─────────┐
>> │ to_char │
>> ╞═════════╡
>> │ 1000000 │
>> └─────────┘
>> (1 row)
>>
>> Regards
>>
>> Pavel
>>
>>>
>>> Regards
>>>
>>>