Re: search_path for PL/pgSQL functions partially cached?

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