MIME-Version: 1.0
In-Reply-To: <20250104142310.7ff3bc68cc824b9af354cac7@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> <CAMsGm5fVJzOZ6kNDwkiRuFZFW=cY=FZixaSOd=WRdP0QLdoqew@mail.gmail.com>
 <20250104142310.7ff3bc68cc824b9af354cac7@magnetkern.de>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sat, 4 Jan 2025 18:19:22 -0700
Message-ID: <CAKFQuwbUwDW6Sm-QSmvJqXjrU6Rdj5VHwLc51Hax+HDmdVaa1g@mail.gmail.com>
Subject: Re: search_path for PL/pgSQL functions partially cached?
To: Jan Behrens <jbe-mlist@magnetkern.de>
Cc: Isaac Morland <isaac.morland@gmail.com>, Adrian Klaver <adrian.klaver@aklaver.com>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000596d53062aeb4d80"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwbUwDW6Sm-QSmvJqXjrU6Rdj5VHwLc51Hax%2BHDmdVaa1g%40mail.gmail.com>
Precedence: bulk

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

On Saturday, January 4, 2025, Jan Behrens <jbe-mlist@magnetkern.de> wrote:

>
> 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" =3D 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;
>   $$;
>

You might consider adding a polymorphic argument for the result type.  Then
if you call the function with two different typed inputs it will be cached
once for each.

=E2=80=9C Likewise, functions having polymorphic argument types have a sepa=
rate
statement cache for each combination of actual argument types they have
been invoked for, so that data type differences do not cause unexpected
failures.=E2=80=9D

David J.

--000000000000596d53062aeb4d80
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:<br><blockquote class=
=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padd=
ing-left:1ex"><br>
CREATE FUNCTION &quot;foo_impl&quot;(&quot;query_p&quot; TEXT, &quot;search=
_path_p&quot; TEXT)<br>
=C2=A0 RETURNS &quot;some_type&quot;<br>
=C2=A0 LANGUAGE plpgsql SET search_path FROM CURRENT AS $$<br>
=C2=A0 =C2=A0 DECLARE<br>
=C2=A0 =C2=A0 =C2=A0 &quot;old_search_path&quot; TEXT;<br>
=C2=A0 =C2=A0 =C2=A0 &quot;result&quot; &quot;some_type&quot;;<br>
=C2=A0 =C2=A0 BEGIN<br>
=C2=A0 =C2=A0 =C2=A0 &quot;old_search_path&quot; =3D current_setting(&#39;s=
earch_path&#39;)<wbr>;<br>
=C2=A0 =C2=A0 =C2=A0 PERFORM set_config(&#39;search_path&#39;, &quot;search=
_path_p&quot;, TRUE);<br>
=C2=A0 =C2=A0 =C2=A0 EXECUTE &quot;query_p&quot; INTO &quot;result&quot;;<b=
r>
=C2=A0 =C2=A0 =C2=A0 PERFORM set_config(&#39;search_path&#39;, &quot;old_se=
arch_path&quot;, TRUE);<br>
=C2=A0 =C2=A0 =C2=A0 RETURN &quot;result&quot;;<br>
=C2=A0 =C2=A0 END;<br>
=C2=A0 $$;<br>
</blockquote><div><br></div><div>You might consider adding a polymorphic ar=
gument for the result type.=C2=A0 Then if you call the function with two di=
fferent typed inputs it will be cached once for each.</div><div><br></div><=
div>=E2=80=9C<span style=3D"font-family:&quot;Open Sans&quot;,sans-serif;fo=
nt-size:14.4px">=C2=A0Likewise, functions having polymorphic argument types=
 have a separate statement cache for each combination of actual argument ty=
pes they have been invoked for, so that data type differences do not cause =
unexpected failures.=E2=80=9D</span></div><div><br></div><div>David J.</div=
><div>=C2=A0</div>

--000000000000596d53062aeb4d80--