I've spent today messing with making the planner substitute inline
definitions of simple SQL functions, per the comment in
src/backend/optimizer/util/clauses.c:

 * XXX Possible future improvement: if the func is SQL-language, and its
 * definition is simply "SELECT expression", we could parse and substitute
 * the expression here.  This would avoid much runtime overhead, and perhaps
 * expose opportunities for constant-folding within the expression even if
 * not all the func's input args are constants.  It'd be appropriate to do
 * that here, not in the parser, since we wouldn't want it to happen until
 * after rule substitution/rewriting.

It seems to work 99%, but I'm seeing this failure in the regression
tests:

  CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
  SELECT * FROM getfoo(1) AS t1;
! ERROR:  ExecMakeTableFunctionResult: expression is not a function call

which of course happens because the table-function expression has been
reduced to just a constant "1" by the time the executor sees it.

A grotty answer is to not apply constant-expression folding to table
function RTE entries.  A better answer would be to make
ExecMakeTableFunctionResult more flexible, but I'm not quite sure what
it should do if presented a non-function-call expression tree.  Any
thoughts?

			regards, tom lane

PS: another little problem is
	regression=# explain SELECT * FROM getfoo(1) AS t1;
	server closed the connection unexpectedly
but I'm sure that's just a lack of flexibility in explain.c ...

Sun, 01 Dec 2002 01:21:35 -0500
Tom Lane <tgl@sss.pgh.pa.us>
ExecMakeTableFunctionResult vs. pre-evaluated functions
Tom Lane wrote:
> It seems to work 99%, but I'm seeing this failure in the regression
> tests:
> 
>   CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> ! ERROR:  ExecMakeTableFunctionResult: expression is not a function call
> 
> which of course happens because the table-function expression has been
> reduced to just a constant "1" by the time the executor sees it.
> 
> A grotty answer is to not apply constant-expression folding to table
> function RTE entries.  A better answer would be to make
> ExecMakeTableFunctionResult more flexible, but I'm not quite sure what
> it should do if presented a non-function-call expression tree.  Any
> thoughts?

If presented with a non-function-call expression tree, can we always evaluate 
it to produce a scalar constant (if it isn't already)? If so, why not do that, 
create a one row, one column tuplestore, and exit? It's really no different 
than a function call that does the same, is it?

Joe



Sat, 30 Nov 2002 22:50:39 -0800
Joe Conway <mail@joeconway.com>
Re: ExecMakeTableFunctionResult vs. pre-evaluated functions
Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> A grotty answer is to not apply constant-expression folding to table
>> function RTE entries.  A better answer would be to make
>> ExecMakeTableFunctionResult more flexible, but I'm not quite sure what
>> it should do if presented a non-function-call expression tree.  Any
>> thoughts?

> If presented with a non-function-call expression tree, can we always evaluate
> it to produce a scalar constant (if it isn't already)? If so, why not do that,
> create a one row, one column tuplestore, and exit? It's really no different 
> than a function call that does the same, is it?

Yeah, that's probably a reasonable approach to take.  It would fail if
we had an expression that returned a non-scalar value (viz. a set),
but the constant-folder won't try to fold or inline anything that
returns a set, so you shouldn't see any problem in practice.

We do need to do something about this, since even without the inlining
code there's a problem: the only reason the regression example works in
7.3 is that the constant-simplifier doesn't fire.  Which it would, if
the function were marked as immutable, as would be reasonable to do.

regression=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

regression=# CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;'
regression-# LANGUAGE SQL immutable;
CREATE FUNCTION
regression=# SELECT * FROM getfoo(1) AS t1;
ERROR:  ExecMakeTableFunctionResult: expression is not a function call

			regards, tom lane

Sun, 01 Dec 2002 14:25:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: ExecMakeTableFunctionResult vs. pre-evaluated functions 
> Joe Conway <mail@joeconway.com> writes:
>> If presented with a non-function-call expression tree, can we always evaluate
>> it to produce a scalar constant (if it isn't already)? If so, why not do that,
>> create a one row, one column tuplestore, and exit? It's really no different 
>> than a function call that does the same, is it?

> Yeah, that's probably a reasonable approach to take.  It would fail if
> we had an expression that returned a non-scalar value (viz. a set),
> but the constant-folder won't try to fold or inline anything that
> returns a set, so you shouldn't see any problem in practice.

Actually, it turns out to be easy to make ExecMakeTableFunctionResult
cope with expressions returning sets as well.  It's the same as the
ValuePerCall protocol we defined for table functions (no surprise,
since that protocol was deliberately modeled on the existing
implementation of set-returning expressions).  So it's done.

			regards, tom lane

Sun, 01 Dec 2002 15:29:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: ExecMakeTableFunctionResult vs. pre-evaluated functions 