Re:Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

Thank you very much for your reply.

I did the following two tests and found that the return value of pg_catalog.date and oracle.date are inconsistent.

①the function was created with return type pg_catalog.date

---

CREATE OR REPLACE FUNCTION to_date(str text) RETURNS pg_catalog.date AS $$ SELECT $1::pg_catalog.date; $$ LANGUAGE sql IMMUTABLE STRICT;

COMMENT ON FUNCTION public.to_date(text) IS 'Convert string to date';

---

The execution result is as follows：

postgres=# select TO_DATE(TO_CHAR(localtimestamp, 'YYYY/MM/DD')) as localdate, TO_DATE(TO_CHAR(current_timestamp, 'YYYY/MM/DD')) as currentdate;

localdate | currentdate

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

2023-04-27 | 2023-04-27

②the function was created with return type oracle.date

---

CREATE OR REPLACE FUNCTION to_date(str text) RETURNS oracle.date AS $$ SELECT $1::oracle.date; $$ LANGUAGE sql IMMUTABLE STRICT;

COMMENT ON FUNCTION public.to_date(text) IS 'Convert string to date';

---

The execution result is as follows：

postgres=# select TO_DATE(TO_CHAR(localtimestamp, 'YYYY/MM/DD')) as localdate, TO_DATE(TO_CHAR(current_timestamp, 'YYYY/MM/DD')) as currentdate;

localdate | currentdate

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

2023-04-27 00:00:00 | 2023-04-27 00:00:00

When the return type is set to oracle.date, there are hours, minutes, and seconds of the date value in the SQL execution result.

Why is there such a difference and how to solve it?

At 2023-04-25 20:53:09, "Erik Wienhold" <ewie(at)ewie(dot)name> wrote:
>> On 25/04/2023 13:34 CEST gzh <gzhcoder(at)126(dot)com> wrote:
>>
>> >The solution is the same whether you upgrade or not: you need
>> >to adjust your search_path to include the "oracle" schema,
>> >or else explicitly qualify references to orafce functions.
>> Thank you very much for your help.
>>
>> To use the to_date functions of Orafce 3.0.1, we created the following
>> to_date function in the public schema of the old database.
>>
>> -----
>> CREATE OR REPLACE FUNCTION to_date(str text) RETURNS date AS $$ SELECT $1::date; $$ LANGUAGE sql IMMUTABLE STRICT; COMMENT ON FUNCTION public.to_date(text) IS 'Convert string to date';
>> -----
>>
>> To avoid using a to_date function with the same name and parameter in the
>> pg_catalog schema first, the search_path of the old database is set as
>> follows:
>>
>> "$user", public, pg_catalog
>>
>> Make sure that public is searched before pg_catalog.
>> After the database is upgraded, in order to solve the changes in Oracle
>> 3.24, we have added oracle schema to the search_path, as shown below：
>>
>> "$user", public, oracle, pg_catalog
>>
>> The following error occurred when I ran my application.
>>
>> 42P13:ERROR:42P13: return type mismatch in function declared to return
>> pg_catalog.date
>>
>> When I put the oracle schema at the end of the search_path, the problem was
>> solved.
>> The search_path settings without problems are as follows:
>>
>> "$user", public, pg_catalog, oracle
>>
>> Why does it report an error when i put oracle between public and pg_catalog?
>
>When you created function to_date(text) your search_path was probably
>
> "$user", public, pg_catalog
>
>Thereby the function was created with return type pg_catalog.date and without
>a search_path setting.
>
>The cast to date in the function body, however, is unqualified and thus relies
>on the session search_path. When adding oracle to the session search_path
>before pg_catalog, the cast will be to oracle.date (orafce defines its own
>date type) instead of pg_catalog.date. The function return type, however, is
>still declared as pg_catalog.date.
>
>To fix this create the function with an explicit search_path, i.e.
>
> CREATE FUNCTION to_date(text)
> RETURNS oracle.date
> SET search_path = oracle
> ...
>
>Or write the cast as $1::oracle.date to not rely on the search_path at all.
>
>--
>Erik