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 ...

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to