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

> 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