MIME-Version: 1.0
In-Reply-To: <20250105001249.cdca9dd84a3061c06f936ca7@magnetkern.de>
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> <e9d0d4d0-9d92-4b58-aa19-f8140cd4b10f@aklaver.com>
 <20250105001249.cdca9dd84a3061c06f936ca7@magnetkern.de>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sat, 4 Jan 2025 17:04:49 -0700
Message-ID: <CAKFQuwYrWzaJSg9YoV4zGsAbNsBCwG9JsupzwWXm1zRM9-5XaQ@mail.gmail.com>
Subject: Re: search_path for PL/pgSQL functions partially cached?
To: Jan Behrens <jbe-mlist@magnetkern.de>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, Tom Lane <tgl@sss.pgh.pa.us>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b41533062aea427c"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYrWzaJSg9YoV4zGsAbNsBCwG9JsupzwWXm1zRM9-5XaQ%40mail.gmail.com>
Precedence: bulk

--000000000000b41533062aea427c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Saturday, January 4, 2025, Jan Behrens <jbe-mlist@magnetkern.de> wrote:
>
>
> 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.
>
> I think this has to do with the fact that the overall structure and
> probably types(?) are parsed first?
>

I concur that this dynamic doesn=E2=80=99t seem to be discussed.  Namely th=
at in
the presence of nested blocks the parse phase resolves placeholders for all
declared variables without executing any expressions in the body of the
function; therefore all types will be resolved seeing the same search_path,
namely that of the calling session or established using SET.  Changing the
search_path within an outer function body block will not affect
declarations within an inner block. (I am not sure whether the for-loop
cases are exceptional in this.)

David J.

--000000000000b41533062aea427c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Saturday, January 4, 2025, Jan Behrens &lt;<a href=3D"mailto:jbe-mlist@m=
agnetkern.de">jbe-mlist@magnetkern.de</a>&gt; wrote:<blockquote class=3D"gm=
ail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-le=
ft:1ex">
<br>
Even if<br>
<br>
DECLARE &quot;variable&quot; &quot;tbl&quot;.&quot;col&quot;%TYPE;<br>
<br>
follows *after* the schema is set to &quot;myschema&quot; in the example ab=
ove, I<br>
still get differing results, depending on how the search_path was set<br>
when the function was first called.<br>
<br>
I think this has to do with the fact that the overall structure and<br>
probably types(?) are parsed first?<br>
</blockquote><div><br></div><div>I concur that this dynamic doesn=E2=80=99t=
 seem to be discussed.=C2=A0 Namely that in the presence of nested blocks t=
he parse phase resolves placeholders for all declared variables without exe=
cuting any expressions in the body of the function; therefore all types wil=
l be resolved seeing the same search_path, namely that of the calling sessi=
on or established using SET.=C2=A0 Changing the search_path within an outer=
 function body block will not affect declarations within an inner block. (I=
 am not sure whether the for-loop cases are exceptional in this.)</div><div=
><br></div><div>David J.</div><div><br></div>

--000000000000b41533062aea427c--