pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedpsql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
11+ messages / 4 participants
[nested] [flat]
* psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
@ 2023-04-19 13:24 gzh <gzhcoder@126.com>
0 siblings, 1 reply; 11+ messages in thread
From: gzh @ 2023-04-19 13:24 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
@ 2023-04-19 14:51 Erik Wienhold <ewie@ewie.name>
parent: gzh <gzhcoder@126.com>
0 siblings, 1 reply; 11+ messages in thread
From: Erik Wienhold @ 2023-04-19 14:51 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; pgsql-general@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
@ 2023-04-19 17:02 gzh <gzhcoder@126.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 2 replies; 11+ messages in thread
From: gzh @ 2023-04-19 17:02 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-general@lists.postgresql.org
Thank you for your prompt reply.
Is there another solution if the database is not upgraded to 12.14?
>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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
@ 2023-04-19 17:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: gzh <gzhcoder@126.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2023-04-19 17:18 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
@ 2023-04-19 17:23 Adrian Klaver <adrian.klaver@aklaver.com>
parent: gzh <gzhcoder@126.com>
1 sibling, 0 replies; 11+ messages in thread
From: Adrian Klaver @ 2023-04-19 17:23 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-general@lists.postgresql.org
On 4/19/23 10:02 AM, gzh wrote:
> Thank you for your prompt reply.
> Is there another solution if the database is not upgraded to 12.14?
>
>>Better upgrade to latest release 12.14.
>
The point being made was that 12.14 is the latest minor release so you
might as well upgrade to it. It will not change your situation, as
others have pointed out that is a schema/search_path issue. You will
still need to resolve that.
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
@ 2023-04-25 11:34 gzh <gzhcoder@126.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: gzh @ 2023-04-25 11:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org
>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?
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
@ 2023-04-25 12:53 Erik Wienhold <ewie@ewie.name>
parent: gzh <gzhcoder@126.com>
0 siblings, 1 reply; 11+ messages in thread
From: Erik Wienhold @ 2023-04-25 12:53 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re:Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
@ 2023-04-27 11:20 gzh <gzhcoder@126.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 2 replies; 11+ messages in thread
From: gzh @ 2023-04-27 11:20 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org
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@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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Re:Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
@ 2023-04-27 13:20 Erik Wienhold <ewie@ewie.name>
parent: gzh <gzhcoder@126.com>
1 sibling, 0 replies; 11+ messages in thread
From: Erik Wienhold @ 2023-04-27 13:20 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: pgsql-general@lists.postgresql.org
> On 27/04/2023 13:20 CEST gzh <gzhcoder@126.com> wrote:
>
> 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?
orafce defines oracle.date as timestamp(0) [0] because Oracle's DATE type has
a precision of one second [1]. That's the point of orafce: to provide Oracle
compatibility.
You can cast oracle.date to pg_catalog.date but then you're in Postgres
territory again. Depends on what you want to achieve. If it's just formatting
use oracle.to_char:
SELECT oracle.to_char('2023-04-27'::oracle.date, 'YYYY-MM-DD');
[0] https://github.com/orafce/orafce/blob/VERSION_3_24_4/orafce--3.24.sql#L343
[1] https://oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals#date
--
Erik
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
@ 2023-04-27 14:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: gzh <gzhcoder@126.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2023-04-27 14:11 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
@ 2023-04-28 03:43 gzh <gzhcoder@126.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: gzh @ 2023-04-28 03:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org
>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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2023-04-28 03:43 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-19 13:24 psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist gzh <gzhcoder@126.com>
2023-04-19 14:51 ` Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist Erik Wienhold <ewie@ewie.name>
2023-04-19 17:02 ` Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist gzh <gzhcoder@126.com>
2023-04-19 17:18 ` Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist Tom Lane <tgl@sss.pgh.pa.us>
2023-04-25 11:34 ` Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist gzh <gzhcoder@126.com>
2023-04-25 12:53 ` Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist Erik Wienhold <ewie@ewie.name>
2023-04-27 11:20 ` Re:Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist gzh <gzhcoder@126.com>
2023-04-27 13:20 ` Re: Re:Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist Erik Wienhold <ewie@ewie.name>
2023-04-27 14:11 ` Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist Tom Lane <tgl@sss.pgh.pa.us>
2023-04-28 03:43 ` Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist gzh <gzhcoder@126.com>
2023-04-19 17:23 ` Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox