From: gzh <gzhcoder@126.com>
To: Erik Wienhold <ewie@ewie.name>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Subject: Re:Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
Date: Thu, 27 Apr 2023 19:20:41 +0800 (CST)
Message-ID: <7480a292.7dc7.187c271a743.Coremail.gzhcoder@126.com> (raw)
In-Reply-To: <1073908593.624606.1682427189969@office.mailbox.org>
References: <328d2a0.828a.18799afd0ac.Coremail.gzhcoder@126.com>
<1597875806.606392.1681915893771@office.mailbox.org>
<70c95d95.7a58.1879a77ed4f.Coremail.gzhcoder@126.com>
<2993958.1681924695@sss.pgh.pa.us>
<5027d9b9.7aad.187b831fb18.Coremail.gzhcoder@126.com>
<1073908593.624606.1682427189969@office.mailbox.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: gzhcoder@126.com, ewie@ewie.name, tgl@sss.pgh.pa.us
Subject: Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
In-Reply-To: <7480a292.7dc7.187c271a743.Coremail.gzhcoder@126.com>
References: <328d2a0.828a.18799afd0ac.Coremail.gzhcoder@126.com> <1597875806.606392.1681915893771@office.mailbox.org> <70c95d95.7a58.1879a77ed4f.Coremail.gzhcoder@126.com> <2993958.1681924695@sss.pgh.pa.us> <5027d9b9.7aad.187b831fb18.Coremail.gzhcoder@126.com> <1073908593.624606.1682427189969@office.mailbox.org> <7480a292.7dc7.187c271a743.Coremail.gzhcoder@126.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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