Re: search_path for PL/pgSQL functions partially cached?

[Laurenz Albe <laurenz.albe@xxxxxxxxxxx>]

On Sun, 2025-01-05 at 00:12 +0100, Jan Behrens wrote:
> I constructed the following new example:
> 
> ============
> 
> CREATE TABLE "tbl" ("col" NUMERIC(15, 0));
> 
> CREATE FUNCTION "foo"() RETURNS TEXT LANGUAGE plpgsql AS $$
>   BEGIN
>     RETURN '2.4';
>   END;
> $$;
> 
> BEGIN;
> 
> CREATE SCHEMA "myschema";
> SET LOCAL search_path TO 'myschema';
> 
> CREATE TABLE "tbl" ("col" NUMERIC);
> 
> CREATE FUNCTION "foo"() RETURNS TEXT LANGUAGE plpgsql AS $$
>   BEGIN
>     RETURN '5.4';
>   END;
> $$;
> 
> CREATE FUNCTION "run"() RETURNS TEXT LANGUAGE plpgsql AS $$
>   DECLARE
>     "old_search_path" TEXT;
>   BEGIN
>     "old_search_path" := current_setting('search_path');
>     SET LOCAL search_path TO "myschema";
>     -- At this point, search_path is always set to 'myschema'!
>     DECLARE
>       "variable" "tbl"."col"%TYPE;
>     BEGIN
>       "variable" := "foo"();
>       RETURN "variable";
>     END;
>     PERFORM set_config('search_path', "old_search_path", TRUE);
>   END;
> $$;
> 
> COMMIT;
> 
> 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.

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