Re: BUG #10500: Cannot restore from a dump when some function is used in public shcema

nicolas(at)cybercat(dot)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