Re: search_path for PL/pgSQL functions partially cached?

On Saturday, January 4, 2025, Jan Behrens <jbe-mlist(at)magnetkern(dot)de> wrote:
>
>
> Even if
>
> DECLARE "variable" "tbl"."col"%TYPE;
>
> follows *after* the schema is set to "myschema" in the example above, I
> still get differing results, depending on how the search_path was set
> when the function was first called.
>
> I think this has to do with the fact that the overall structure and
> probably types(?) are parsed first?
>

I concur that this dynamic doesn’t seem to be discussed. Namely that in
the presence of nested blocks the parse phase resolves placeholders for all
declared variables without executing any expressions in the body of the
function; therefore all types will be resolved seeing the same search_path,
namely that of the calling session or established using SET. Changing the
search_path within an outer function body block will not affect
declarations within an inner block. (I am not sure whether the for-loop
cases are exceptional in this.)

David J.