> On 25/04/2023 13:34 CEST gzh <gzhcoder@xxxxxxx> 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
