public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Jan Behrens <[email protected]>
To: Adrian Klaver <[email protected]>
To: Tom Lane <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Date: Sun, 05 Jan 2025 07:48:56 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
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]>
	<[email protected]>
	<[email protected]>

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

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], [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