Re: search_path for PL/pgSQL functions partially cached?

On Fri, 3 Jan 2025 18:36:13 -0500
Isaac Morland <isaac(dot)morland(at)gmail(dot)com> wrote:

> On Fri, 3 Jan 2025 at 18:22, Jan Behrens <jbe-mlist(at)magnetkern(dot)de> wrote:
>
>
> > Instead, I plan to expect the function to receive a query string that
> > will get the data that is being processed by the function.
> >
> > That query string should be allowed to refer to tables in the
> > search_path at the caller's side.
> >
> > Therefore, I cannot use the "SET search_path FROM CURRENT" in my
> > "CREATE FUNCTION" statement, because it would overwrite the current
> > search_path on each call of the function.
> >
>
> I wonder if it would help if EXECUTE took an optional search_path to use
> while executing the query.

That wouldn't solve my problem, because the function that includes the
EXECUTE still needs to know the search_path set on the caller side.

This only works if I omit the "SET search_path FROM CURRENT" option in
the function's definition OR if I pass a search_path as an argument. I
guess I could write a wrapper:

============

BEGIN;

CREATE SCHEMA "some_schema";
SET LOCAL search_path TO "some_schema";

CREATE TYPE "some_type" AS ("name" TEXT, "height" INT8);

CREATE FUNCTION "foo_impl"("query_p" TEXT, "search_path_p" TEXT)
RETURNS "some_type"
LANGUAGE plpgsql SET search_path FROM CURRENT AS $$
DECLARE
"old_search_path" TEXT;
"result" "some_type";
BEGIN
"old_search_path" = current_setting('search_path');
PERFORM set_config('search_path', "search_path_p", TRUE);
EXECUTE "query_p" INTO "result";
PERFORM set_config('search_path', "old_search_path", TRUE);
RETURN "result";
END;
$$;

CREATE FUNCTION "foo"("query_p" TEXT)
RETURNS "some_type"
RETURN "foo_impl"("query_p", current_setting('search_path'));

COMMIT;

CREATE TABLE "tbl" ("id" SERIAL8, "name" TEXT, "height" INT8);
INSERT INTO "tbl" ("name", "height") VALUES ('Alice', 200);

SELECT * FROM "some_schema"."foo"('SELECT "name" FROM "tbl"');

============

Not sure which variant (this or my previous attempt) is better and if
either is safe/correct.

Regards,
Jan Behrens