MIME-Version: 1.0
References: <09de6afa-c33d-4d94-a5cb-afc6cea0d2bb@illuminatedcomputing.com>
 <ffd7937d-190c-4f09-9418-5345728b45c4@iki.fi>
 <1755478.1722020334@sss.pgh.pa.us>
 <956f8a6f-32a7-49ab-92a3-dca5c8afe112@illuminatedcomputing.com>
 <1466725.1725381721@sss.pgh.pa.us>
 <33b10538-2c88-4278-a65f-0c1e6f265cb7@illuminatedcomputing.com>
 <737313.1752528083@sss.pgh.pa.us>
 <CA+renyXxanvX8rnrqTNkZt1r9=rm59MHSG4DDVfsV2aUGe3dUQ@mail.gmail.com>
 <440458.1763858681@sss.pgh.pa.us>
In-Reply-To: <440458.1763858681@sss.pgh.pa.us>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Tue, 2 Dec 2025 06:52:21 +0100
Message-ID: 
 <CAFj8pRAH17WO_d9nUWAsV8MXVAVC18Jmn7f+KxGfakEHFY33Xg@mail.gmail.com>
Subject: Re: Inline non-SQL SRFs using SupportRequestSimplify
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Paul A Jungwirth <pj@illuminatedcomputing.com>,
 Heikki Linnakangas <hlinnaka@iki.fi>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000032481b0644f1b58a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFj8pRAH17WO_d9nUWAsV8MXVAVC18Jmn7f%2BKxGfakEHFY33Xg%40mail.gmail.com>
Precedence: bulk

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

Hi

ne 23. 11. 2025 v 1:45 odes=C3=ADlatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

> Paul A Jungwirth <pj@illuminatedcomputing.com> writes:
> > The reason for supporting more than SQL functions is to let you
> > construct the query dynamically, e.g. with user-supplied table/column
> > names, or to only include some expensive filters if needed. This would
> > be great for building functions that implement temporal
> > outer/semi/antijoin. Another use-case I personally have, which I think
> > is quite common, is building "parameterized views" for permissions
> > checks, e.g. visible_sales(user). In that case we may only need to
> > include certain joins if the user belongs to certain roles (e.g. a
> > third-party sales rep).
>
> I went through this again, and committed it with a bunch of
> mostly-cosmetic changes.  In particular, it seemed like talking
> about inlining "set-returning functions" is no longer really on-point,
> since this mechanism is perfectly capable of inlining non-SRFs.
> (The reason we haven't done that for SQL functions is mainly that
> we didn't feel like doing the analysis necessary to prove that a
> SELECT will return exactly one row, which would be necessary to
> maintain semantic equivalence for a non-SRF after inlining.
> The easy cases of that, such as "SELECT expression", are already
> sufficiently handled by regular inlining.)
>
> So after some thought I renamed inline_set_returning_function to
> inline_function_in_from, and made a bunch of other changes in names
> and comments to line up with that.
>
> Thanks for working on this!  I know it's been a long slog.
>

I checked this patch, and I think so using the body of the
function foo_from_bar is very confusing.
If I understand this patch and functionality, then the function is never
executed (when the support function has support
for SupportRequestInlineInFrom).

+CREATE FUNCTION test_inline_in_from_support_func(internal)
+    RETURNS internal
+    AS :'regresslib', 'test_inline_in_from_support_func'
+    LANGUAGE C STRICT;
+CREATE FUNCTION foo_from_bar(colname TEXT, tablename TEXT, filter TEXT)
+RETURNS SETOF TEXT
+LANGUAGE plpgsql
+AS $function$
+DECLARE
+  sql TEXT;
+BEGIN
+  sql :=3D format('SELECT %I::text FROM %I', colname, tablename);
+  IF filter IS NOT NULL THEN
+    sql :=3D CONCAT(sql, format(' WHERE %I::text =3D $1', colname));
+  END IF;
+  RETURN QUERY EXECUTE sql USING filter;
+END;
+$function$ STABLE;

More correct form should be

BEGIN
  RAISE EXCEPTION 'halt - should not be executed directly';
END;

Regards

Pavel


>
>                         regards, tom lane
>
>
>

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

<div dir=3D"ltr"><div>Hi</div><br><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr">ne 23. 11. 2025 v=C2=A01:45 o=
des=C3=ADlatel Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pg=
h.pa.us</a>&gt; napsal:<br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex">Paul A Jungwirth &lt;<a href=3D"mailto:pj@illuminatedcomputing.com"=
 target=3D"_blank">pj@illuminatedcomputing.com</a>&gt; writes:<br>
&gt; The reason for supporting more than SQL functions is to let you<br>
&gt; construct the query dynamically, e.g. with user-supplied table/column<=
br>
&gt; names, or to only include some expensive filters if needed. This would=
<br>
&gt; be great for building functions that implement temporal<br>
&gt; outer/semi/antijoin. Another use-case I personally have, which I think=
<br>
&gt; is quite common, is building &quot;parameterized views&quot; for permi=
ssions<br>
&gt; checks, e.g. visible_sales(user). In that case we may only need to<br>
&gt; include certain joins if the user belongs to certain roles (e.g. a<br>
&gt; third-party sales rep).<br>
<br>
I went through this again, and committed it with a bunch of<br>
mostly-cosmetic changes.=C2=A0 In particular, it seemed like talking<br>
about inlining &quot;set-returning functions&quot; is no longer really on-p=
oint,<br>
since this mechanism is perfectly capable of inlining non-SRFs.<br>
(The reason we haven&#39;t done that for SQL functions is mainly that<br>
we didn&#39;t feel like doing the analysis necessary to prove that a<br>
SELECT will return exactly one row, which would be necessary to<br>
maintain semantic equivalence for a non-SRF after inlining.<br>
The easy cases of that, such as &quot;SELECT expression&quot;, are already<=
br>
sufficiently handled by regular inlining.)<br>
<br>
So after some thought I renamed inline_set_returning_function to<br>
inline_function_in_from, and made a bunch of other changes in names<br>
and comments to line up with that.<br>
<br>
Thanks for working on this!=C2=A0 I know it&#39;s been a long slog.<br></bl=
ockquote><div><br></div><div>I checked this patch, and I think so using the=
 body of the function=C2=A0foo_from_bar is very confusing.</div><div>If I u=
nderstand this patch and functionality, then the function is never executed=
 (when the support function has support for=C2=A0SupportRequestInlineInFrom=
).</div><div><br></div><div>+CREATE FUNCTION test_inline_in_from_support_fu=
nc(internal)<br>+ =C2=A0 =C2=A0RETURNS internal<br>+ =C2=A0 =C2=A0AS :&#39;=
regresslib&#39;, &#39;test_inline_in_from_support_func&#39;<br>+ =C2=A0 =C2=
=A0LANGUAGE C STRICT;<br>+CREATE FUNCTION foo_from_bar(colname TEXT, tablen=
ame TEXT, filter TEXT)<br>+RETURNS SETOF TEXT<br>+LANGUAGE plpgsql<br>+AS $=
function$<br>+DECLARE<br>+ =C2=A0sql TEXT;<br>+BEGIN<br>+ =C2=A0sql :=3D fo=
rmat(&#39;SELECT %I::text FROM %I&#39;, colname, tablename);<br>+ =C2=A0IF =
filter IS NOT NULL THEN<br>+ =C2=A0 =C2=A0sql :=3D CONCAT(sql, format(&#39;=
 WHERE %I::text =3D $1&#39;, colname));<br>+ =C2=A0END IF;<br>+ =C2=A0RETUR=
N QUERY EXECUTE sql USING filter;<br>+END;<br>+$function$ STABLE;</div><div=
><br></div><div>More correct form should be</div><div><br></div><div>BEGIN<=
/div><div>=C2=A0 RAISE EXCEPTION &#39;halt - should not be executed directl=
y&#39;;=C2=A0</div><div>END;</div><div><br></div><div>Regards</div><div><br=
></div><div>Pavel</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex">
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
<br>
<br>
</blockquote></div></div>

--00000000000032481b0644f1b58a--