Re: PL/pgSQL PERFORM with CTE

2013/8/24 Merlin Moncure <mmoncure(at)gmail(dot)com>

> 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.
>
>
Oracle has a special function for returning sets from procedures - see a
new functionality "Implicit Result Sets"
http://tkyte.blogspot.cz/2013/07/12c-implicit-result-sets.html

Although I am thinking so this feature is in T-SQL much more user friendly.

Regards

Pavel

>
> > 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
>