Re: BUG #10500: Cannot restore from a dump when some function is used in public shcema - Mailing list pgsql-bugs

nicolas@cybercat.ca writes:
> I narrow it down to this simple exemple. Here's a pg_dump in plain text of
> my test case :

> CREATE SCHEMA intranet;
> CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA public;
> SET search_path = public, pg_catalog;
> CREATE FUNCTION cyunaccent(character varying) RETURNS character varying
>     LANGUAGE sql IMMUTABLE
>     AS $_$ SELECT unaccent(lower($1)); $_$;

This function is unsafe on its face: it does not specify what schema to
find unaccent() in.  You need to either explicitly schema-qualify:

     SELECT public.unaccent(lower($1));

(for good measure it'd be wise to qualify lower() as well), or else attach
a "SET search_path" clause to the function definition.

It's arguable whether the search path sensitivity of such functions is
a feature or a bug.  But there are people depending on the fact that they
can change the search path and get different results, so it's unlikely
we'd change the definition now.

            regards, tom lane