Re: PL/pgSQL PERFORM with CTE

On 08/27/2013 08:32 PM, David E. Wheeler wrote:
> On Aug 27, 2013, at 12:30 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.

Conceptually RETURN NEXT is exactly the same as YIELD.

If you look at the SRFs at the C level, then what you do is essentially
a YIELD.
It is only postgreslql backen SRW wrapper which then collects all these
YIELDed values/rows and returns them as on set.

In other words, our SRFs do not currently do any result streaming,
though there is nothing in theory that would prevent them from doing so.

Also, very similar FDWs do streaming.

-------------------
Hannu

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ