PL/pgSQL PERFORM with CTE

On Fri, Aug 23, 2013 at 5:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)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