Re: PL/pgSQL PERFORM with CTE

On Aug 27, 2013, at 12:30 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

> I disagree - Tom K. speaking about what he likes or dislikes (and about what he didn't use) He forgot about strong points of implicit result or interesting points. Clients usually has no problem with dynamic datasets - PHP, DBI, Llibpq, GUI components .. all libs support a generic access and this generic access is often used due less dependency on queries.
>
> There are a three interesting possibilities of implicit result sets:
>
> * Possibility to return dynamic dataset - when you don't know a result before execution - typical use case is a some form of pivot tables or some analytics queries.
>
> * Possibility to return multiple results as flattening of some multidimensional data.
>
> * Possibilty to write multiresults reports for one call execution.

As a dynamic language programmer, I can see this, as long as it’s not to the exclusion of strong typing interfaces, as well.

However, I do not think it should be implicit. If a function or procedure wants to return values or query results or whatever to the caller, it should explicitly do so by using some key word. We already have RETURN, RETURN NEXT, RETURN QUERY, and RETURN EXECUTE, which is great for functions. For hypothetical functions or procedures that want to return data as it processes, rather than buffering the results and returning them all at once, perhaps we could add YIELD, YEILD QUERY, and YIELD EXECUTE. In fact, this is pretty much exactly what the key word YIELD is for in coroutines:

  https://en.wikipedia.org/wiki/Coroutine

But whatever the keyword, I think it makes sense to require one to return results to the caller. Any query that does not return, yield, or capture (select into) values should just have its results discarded.

My $0.02.

Best,

DAvid