Обсуждение: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

Hi, 

I upgraded the version of PostgreSQL from 12.10 to 12.13, 

when I insert data into the t_mstr table, the to_char function in the t_mstr's trigger caused the following error.

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

There is no problem before the upgrade and to_char(numeric) function comes from the Orafce extension.

The configuration of the old and new databases is as follows.

Database server (old): PostgreSQL 12.10（orafce3.15）

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.10 and orafce 3.15", 

but occurs in "PostgreSQL 12.13 and orafce 3.24", 

so either the difference between 12.10 and 12.13 or the difference between orafce 3.15 and 3.24 is suspicious.

What is the reason for the problem?

Regards

>Better upgrade to latest release 12.14.

At 2023-04-19 22:51:33, "Erik Wienhold" <ewie@ewie.name> wrote:
>> On 19/04/2023 15:24 CEST gzh <gzhcoder@126.com> wrote:
>>
>> Hi,
>>
>> I upgraded the version of PostgreSQL from 12.10 to 12.13,
>
>Better upgrade to latest release 12.14.
>
>> when I insert data into the t_mstr table, the to_char function in the t_mstr's
>> trigger caused the following error.
>>
>> psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
>>
>> There is no problem before the upgrade and to_char(numeric) function comes
>> from the Orafce extension.
>> The configuration of the old and new databases is as follows.
>>
>> Database server (old): PostgreSQL 12.10（orafce3.15）
>> 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.10 and orafce 3.15",
>> but occurs in "PostgreSQL 12.13 and orafce 3.24",
>> so either the difference between 12.10 and 12.13 or the difference between
>> orafce 3.15 and 3.24 is suspicious.
>>
>> What is the reason for the problem?
>
>orafce 3.22 moved functions to schema oracle:
>
>https://github.com/orafce/orafce/blob/b492a0f50d5ee866c9870f886401d7c34ad8ccb3/NEWS#L4,L5
>https://github.com/orafce/orafce/commit/86a1b51440ca33a04ef47fc3bb704dee26d16753
>
>--
>Erik

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

At 2023-04-20 01:18:15, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>gzh  <gzhcoder@126.com> writes:
>> Thank you for your prompt reply. 
>> Is there another solution if the database is not upgraded to 12.14?
>
>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.
>
>			regards, tom lane

Thank you very much for your reply.

①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@ewie.name> wrote:
>> On 25/04/2023 13:34 CEST gzh <gzhcoder@126.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

>If you're porting a bunch of code written for Oracle to Postgres,

>you'd have to run around and change every occurrence of "date" to

>"timestamp" ... unless you install orafce, in which case you can

>rely on this alias type that orafce creates.  But you do then have

>two types named "date" in the system, so you have to be careful

>about search_path settings or you'll get more confusion than it's

>worth.

When I put the oracle schema in the search_path behind the pg_catalog schema, 

even if I don't make any changes to the to_date(text) functions of the public schema, 

the application behaves the same as the old version of the database(PostgreSQL 12.10 and orafce 3.15). 

Can I understand that when the pg_catalog schema is in front of the oracle schema, 

the date type that does not specify the schema in all functions takes the date type 

of pg_catalog.date first, so setting the search_path as below is also a solution.

postgres=# show search_path;

             search_path

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

 "$user", public, pg_catalog, oracle

At 2023-04-27 22:11:22, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>gzh  <gzhcoder@126.com> writes:
>> I did the following two tests and found that the return value of pg_catalog.date and oracle.date are inconsistent.
>
>Yeah ... that's pretty much the point.  Oracle uses the name "date"
>for a data type that Postgres (and the SQL standard) calls a "timestamp".
>That's very ancient on their side and I doubt they'll ever change it.
>
>If you're porting a bunch of code written for Oracle to Postgres,
>you'd have to run around and change every occurrence of "date" to
>"timestamp" ... unless you install orafce, in which case you can
>rely on this alias type that orafce creates.  But you do then have
>two types named "date" in the system, so you have to be careful
>about search_path settings or you'll get more confusion than it's
>worth.
>
>			regards, tom lane