Ken Tanzer <ken.tan...@gmail.com> writes:
> On Thu, Feb 18, 2021 at 8:44 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
>> There was a security change to pg_dump a few years ago to make it
>> put "set search_path = pg_catalog" into the dump script. This
>> basically means that any user-defined function in indexes, check
>> constraints, etc is on its own to be sure that it schema-qualifies
>> non-system names, or has a "SET search_path" clause to do that
>> for it.
> Thank you Tom for that explanation. To follow on, I tried adding:
> SET search_path = public;
> to the functions, but that prevents my function from working at all:
No, the way to do it is with a SET function property, like
create or replace function myfunc(...) returns ... language ...
as $$body here$$
SET search_path = whatever
... other function properties ...
;
That takes care of restoring the old value on the way out of the
function, so it's okay to use in an immutable function.
I think you can plaster this property onto an existing function
with ALTER FUNCTION, which should be less error-prone than
repeating the whole CREATE.
regards, tom lane
