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@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +