Date: Sat, 4 Jan 2025 14:23:10 +0100
From: Jan Behrens <jbe-mlist@magnetkern.de>
To: Isaac Morland <isaac.morland@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, "David G. Johnston"
 <david.g.johnston@gmail.com>, "pgsql-general@lists.postgresql.org"
 <pgsql-general@lists.postgresql.org>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Message-Id: <20250104142310.7ff3bc68cc824b9af354cac7@magnetkern.de>
In-Reply-To: <CAMsGm5fVJzOZ6kNDwkiRuFZFW=cY=FZixaSOd=WRdP0QLdoqew@mail.gmail.com>
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
	<CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
	<20241228004009.267f21b78394c934f27f9974@magnetkern.de>
	<20250103135332.7910a008714abaa04d32e5e2@magnetkern.de>
	<CAKFQuwaU19_6HaB+9-L-fQhjUr8_5ACvxLAPRBhEdfLv9JVZBg@mail.gmail.com>
	<20250103174849.f071d9c6942f76a9b812157c@magnetkern.de>
	<CAKFQuwZdt+YLi=9_WraRLajuOkmw4esFzbHTXmv5MwHJemdDhQ@mail.gmail.com>
	<20250103223312.dde69ae482776e4f1b0c2258@magnetkern.de>
	<14e035fb-ab35-4a73-a637-ad0180067b07@aklaver.com>
	<20250104002203.2aed83d48d795ce6fbbd3b61@magnetkern.de>
	<CAMsGm5fVJzOZ6kNDwkiRuFZFW=cY=FZixaSOd=WRdP0QLdoqew@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20250104142310.7ff3bc68cc824b9af354cac7%40magnetkern.de>
Precedence: bulk

On Fri, 3 Jan 2025 18:36:13 -0500
Isaac Morland <isaac.morland@gmail.com> wrote:

> On Fri, 3 Jan 2025 at 18:22, Jan Behrens <jbe-mlist@magnetkern.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