Thread

Re: Inline non-SQL SRFs using SupportRequestSimplify

Pavel Stehule <pavel.stehule@gmail.com> — 2025-12-02T05:52:21Z

Hi

ne 23. 11. 2025 v 1:45 odesílatel 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 := format('SELECT %I::text FROM %I', colname, tablename);
+  IF filter IS NOT NULL THEN
+    sql := CONCAT(sql, format(' WHERE %I::text = $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
>
>
>