Re: SQL functions, INSERT/UPDATE/DELETE RETURNING,

Added to TODO:

* Allow SQL-language functions to return results from RETURNING queries

---------------------------------------------------------------------------

Tom Lane wrote:
> I wrote:
> > ISTM that ideally, a query with RETURNING ought to act like a SELECT
> > for the purposes of a SQL function --- to wit, that the result rows are
> > discarded if it's not the last query in the function, and are returned
> > as the function result if it is.
>
> The current state of affairs is that the first part of that works as
> expected, and the second part fails like so:
>
> regression=# create function foo8(bigint,bigint) returns setof int8_tbl as
> $$ insert into int8_tbl values($1,$2) returning * $$
> language sql;
> ERROR: return type mismatch in function declared to return int8_tbl
> DETAIL: Function's final statement must be a SELECT.
> CONTEXT: SQL function "foo8"
> regression=#
>
> While this is certainly undesirable, it looks more like a missing
> feature than a bug, especially since the documentation says exactly
> that:
>
> ... the final command must be a SELECT that returns whatever is
> specified as the function's return type.
>
> I spent some time looking at what it would take to fix it, and I find
> that the changes are a bit bigger than I want to be making in mid-beta.
> So my recommendation is that for now we just add a TODO item:
>
> * Allow SQL-language functions to return results from RETURNING queries
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +