public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jan Behrens <[email protected]>
To: Isaac Morland <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Date: Sat, 4 Jan 2025 14:23:10 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMsGm5fVJzOZ6kNDwkiRuFZFW=cY=FZixaSOd=WRdP0QLdoqew@mail.gmail.com>
References: <[email protected]>
	<CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAKFQuwaU19_6HaB+9-L-fQhjUr8_5ACvxLAPRBhEdfLv9JVZBg@mail.gmail.com>
	<[email protected]>
	<CAKFQuwZdt+YLi=9_WraRLajuOkmw4esFzbHTXmv5MwHJemdDhQ@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAMsGm5fVJzOZ6kNDwkiRuFZFW=cY=FZixaSOd=WRdP0QLdoqew@mail.gmail.com>

On Fri, 3 Jan 2025 18:36:13 -0500
Isaac Morland <[email protected]> wrote:

> On Fri, 3 Jan 2025 at 18:22, Jan Behrens <[email protected]> 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

view thread (33+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: search_path for PL/pgSQL functions partially cached?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox