pgsql: Allow SQL-language functions to return the output of an

Log Message:
-----------
Allow SQL-language functions to return the output of an INSERT/UPDATE/DELETE
RETURNING clause, not just a SELECT as formerly.

A side effect of this patch is that when a set-returning SQL function is used
in a FROM clause, performance is improved because the output is collected into
a tuplestore within the function, rather than using the less efficient
value-per-call mechanism.

Modified Files:
--------------
pgsql/doc/src/sgml:
xfunc.sgml (r1.132 -> r1.133)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/xfunc.sgml?r1=1.132&r2=1.133)
pgsql/src/backend/executor:
execQual.c (r1.235 -> r1.236)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/execQual.c?r1=1.235&r2=1.236)
functions.c (r1.126 -> r1.127)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/functions.c?r1=1.126&r2=1.127)
pgsql/src/backend/tcop:
dest.c (r1.72 -> r1.73)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/dest.c?r1=1.72&r2=1.73)
pgsql/src/backend/utils/fmgr:
README (r1.15 -> r1.16)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/fmgr/README?r1=1.15&r2=1.16)
pgsql/src/include/executor:
functions.h (r1.31 -> r1.32)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/executor/functions.h?r1=1.31&r2=1.32)
pgsql/src/include/nodes:
execnodes.h (r1.193 -> r1.194)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/nodes/execnodes.h?r1=1.193&r2=1.194)
pgsql/src/include/tcop:
dest.h (r1.54 -> r1.55)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/tcop/dest.h?r1=1.54&r2=1.55)
pgsql/src/test/regress/expected:
rangefuncs.out (r1.19 -> r1.20)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/expected/rangefuncs.out?r1=1.19&r2=1.20)
pgsql/src/test/regress/output:
create_function_1.source (r1.33 -> r1.34)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/output/create_function_1.source?r1=1.33&r2=1.34)
pgsql/src/test/regress/sql:
rangefuncs.sql (r1.8 -> r1.9)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/sql/rangefuncs.sql?r1=1.8&r2=1.9)

This isn't the same thing as allowing RETURNING inside subqueries,
right?

greg

On 31 Oct 2008, at 07:37 PM, tgl(at)postgresql(dot)org (Tom Lane) wrote:

> Log Message:
> -----------
> Allow SQL-language functions to return the output of an INSERT/
> UPDATE/DELETE
> RETURNING clause, not just a SELECT as formerly.
>
> A side effect of this patch is that when a set-returning SQL
> function is used
> in a FROM clause, performance is improved because the output is
> collected into
> a tuplestore within the function, rather than using the less efficient
> value-per-call mechanism.
>
> Modified Files:
> --------------
> pgsql/doc/src/sgml:
> xfunc.sgml (r1.132 -> r1.133)
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/xfunc.sgml?r1=1.132&r2=1.133
> )
> pgsql/src/backend/executor:
> execQual.c (r1.235 -> r1.236)
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/execQual.c?r1=1.235&r2=1.236
> )
> functions.c (r1.126 -> r1.127)
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/functions.c?r1=1.126&r2=1.127
> )
> pgsql/src/backend/tcop:
> dest.c (r1.72 -> r1.73)
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/dest.c?r1=1.72&r2=1.73
> )
> pgsql/src/backend/utils/fmgr:
> README (r1.15 -> r1.16)
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/fmgr/README?r1=1.15&r2=1.16
> )
> pgsql/src/include/executor:
> functions.h (r1.31 -> r1.32)
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/executor/functions.h?r1=1.31&r2=1.32
> )
> pgsql/src/include/nodes:
> execnodes.h (r1.193 -> r1.194)
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/nodes/execnodes.h?r1=1.193&r2=1.194
> )
> pgsql/src/include/tcop:
> dest.h (r1.54 -> r1.55)
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/tcop/dest.h?r1=1.54&r2=1.55
> )
> pgsql/src/test/regress/expected:
> rangefuncs.out (r1.19 -> r1.20)
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/expected/rangefuncs.out?r1=1.19&r2=1.20
> )
> pgsql/src/test/regress/output:
> create_function_1.source (r1.33 -> r1.34)
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/output/create_function_1.source?r1=1.33&r2=1.34
> )
> pgsql/src/test/regress/sql:
> rangefuncs.sql (r1.8 -> r1.9)
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/sql/rangefuncs.sql?r1=1.8&r2=1.9
> )
>
> --
> Sent via pgsql-committers mailing list (pgsql-committers(at)postgresql(dot)org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-committers

Greg Stark <greg(dot)stark(at)enterprisedb(dot)com> writes:
> This isn't the same thing as allowing RETURNING inside subqueries,
> right?

Right. You could fairly easily get that now by wrapping your RETURNING
query into a SQL function ... but I'm not sure that we want to advertise
that heavily, because the question of just when the subquery gets
executed still isn't resolved with any degree of precision. It might be
that the current behavior is fine, but I'm not feeling we should swear
to it.

regards, tom lane