Re: search_path for PL/pgSQL functions partially cached?

On Friday, January 3, 2025, Jan Behrens <jbe-mlist(at)magnetkern(dot)de> wrote:
>
> I would like to know if the above example is correct. It seems overall
> bulky, but I haven't found a better way, assuming that it can be
> unknown where a particular extension has been installed to. In
> particular I feel a bit insecure about where I have to fully qualify,
> and where not. See the comments in the code above.

Short answer, you cannot looking at a definition and know the answer -
whether the code is going to be executed in a sanitized search_path is what
matters. Anything that would be executed during pg_restore has to be made
safe. Therefore, code that is only ever executed by applications directly
can use swarch_path.

I’d probably modify the function signature to take search_path as a second
optional argument and then invoke a set search_path within the function.
At worse the caller can place current_setting(search_path) as the value of
that argument though being explicit would be recommended.

David J.