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

č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
>>
>>