Re: PL/pgSQL PERFORM with CTE - Mailing list pgsql-hackers

On Fri, Aug 23, 2013 at 5:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> Currently the only way to return query results to the caller is to use
>> some form of RETURN.  It is 100% consistent.
>
> I don't find it consistent at all, because what that means is that the
> data is to be returned to the SQL statement that called the function.
>
> What's more, the point of any such extension needs to be to allow
> *multiple* resultsets to be returned to the client --- if you only need
> one, you can have that functionality today with plain old SELECT FROM
> myfunction().  And returning some data but continuing execution is surely
> not consistent with RETURN.

With set returning functions, RETURN QUERY etc means 'yield this data' -- which is pretty weird -- so your point only holds true for unadorned return (not RETURN NEXT , RETURN QUERY, etc).  So I guess it's hard to claim RETURN means 'return control' though in a procedural sense.  In a perfect world, maybe a separate keyword could have been made to distinguish those cases (e.h. YIELD QUERY), so I agree (after some reflection) with the spirit of your point.  It's not good to have principle keywords do markedly different things.

> Basically it seems that we have two choices for how to represent this
> (hypothetical) future functionality:
>
> 1. Define SELECT without INTO as meaning return results directly to client;
>
> 2. Invent some new syntax to do it.
>
> In a green field I think we'd want to do #2, because #1 seems rather
> error-prone and unobvious.  The only real attraction of #1, IMO, is that
> it's consistent with T-SQL.  But that's not a terribly strong argument
> given the many existing inconsistencies between T-SQL and plpgsql.

Very good points.  I think the only compelling case for #1 that could be made would be to improve compatibility with pl/sql -- from what I can see Oracle has not defined the behavior (that is, in pl/sql select must have INTO) but maybe someone could comment on that.

> BTW, what about INSERT/UPDATE/DELETE RETURNING?  Do we want to let
> these execute and throw away the data?  The argument that this would
> be a feature seems a lot weaker than for SELECT, because after all you
> could usually just leave off the RETURNING clause.  But I'm sure somebody
> will say they want to put a function with side-effects into RETURNING
> and then ignore its output.

If we agree to relax PERFORM, those should be relaxed on the same basis.  In fact, this is conclusive evidence that PERFORM is obsolete: it hails from the days where SELECT was the only data returning DML.

merlin