Re: search_path for PL/pgSQL functions partially cached?

On Sun, 05 Jan 2025 07:48:56 +0100
Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> So what you should do is set the "search_path" *on* the function, not *in*
> the function:
>
> CREATE FUNCTION "run"() RETURNS TEXT LANGUAGE plpgsql
> SET search_path = myschema
> AS $$
> DECLARE
> "variable" "tbl"."col"%TYPE;
> BEGIN
> "variable" := "foo"();
> RETURN "variable";
> END;
> $$;
>
> Yours,
> Laurenz Albe

Yes, that's what works and what I would also do whenever possible
(probably in the form "SET search_path FROM CURRENT").

Summarizing the remaining thread, some issues are:

* The documentation isn't providing a prominent warning that behavior
can be surprising if "SET search_path" is not used in the function's
or procedure's defintion. (E.g. searching for "schema" in the
documentation page for "CREATE FUNCTION" doesn't give any helpful
hints or warning.)

* Things get more complicated when it's impossible to use
"SET search_path" in the function's/procedure's definition, for which
there are two scenarios:

Scenario 1: The function or procedure needs or wants to access or use
the search_path of the caller.

Scenario 2: A procedure wants to execute transactional statements
such as COMMIT or ROLLBACK within its body.

In scenario 1, using "SET search_path" will overwrite the caller's
search_path at runtime, so I cannot access it. (In my post from Sat,
4 Jan 2025 14:23:10 +0100, I have proposed a wrapper function to work
around that.)

In scenario 2, using "SET search_path" is simply not possible and
will be rejected by PostgreSQL.

* It is a bit unclear how the exact behavior is when I set a
search_path from within the functions body (e.g. due to one of the two
scenarios above). There are some examples that show some quite
surprising behavior, at least if you don't fully understand the
plan caching mechanism that is used.

Kind regards,
Jan Behrens