Ken Tanzer <ken.tanzer@xxxxxxxxx> writes: > On Thu, Feb 18, 2021 at 8:44 PM Tom Lane <tgl@xxxxxxxxxxxxx> 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
