Re: DO ... RETURNING

On 06/11/2013 11:30 AM, Pavel Stehule wrote:
> 2013/6/11 Dimitri Fontaine <dimitri@2ndquadrant.fr>:
>> Hi,
>>
>> That topic apparently raises each year and rehash the same points.
>>
>> Pavel Stehule <pavel.stehule@gmail.com> writes:
>>> probably we can allow using DO in CTE without impact on other SQL
>>> statements, and for this purpose we need to know returned
>>> TupleDescriptor early.
>> I still think that DO being a utility statement, having it take
>> parameters and return data is going to be a wart in a part of the system
>> that has only too many of them already.
>>
>> My thinking revolves around CTE support for functions:
>>
>>   WITH FUNCTION name(param, ...)
>>         RETURNS type
>>        LANGUAGE plpgsql AS (
>>     $$ function body here $$
>>   )
>>   SELECT name(x, ...) FROM ...;
Yes, this would be another way to do "in-line pl functions".

I do not think that DO ... RETURNING and WITH FUNCTION are mutually
exclusive.

Rather effort put into implementing one would also be useful for the other.
>>
>>> so I am able accept it, although I am thinking so we are going in
>>> strange direction. We are not able do simply tasks simply (we cannot
>>> execute SQL script on server side simply) :(. But it is not problem of
>>> Hannu design.
>> With the DO utility command you can already execute SQL script on the
>> server quite simply. After all your proposals it's still unclear to me
>> where you want to process which data? (I admit this time I didn't pay
>> much attention, sorry about that)
> there are a significant limit - you cannot "simply" change a database
> when you collect statistics over databases, you cannot drop database
I can do this easily in pl/python.

Actually this is how statistics are collected in
https://github.com/postsql/pgmon_zabbix

You can also do it using pl/proxy or in plpgsql using dblink.

I have seen quite complicated data analysing utilities - with process
control running 1 to N backends depending on load -written in pl/pgsql
using dblink.

> ...
>
> you cannot return multiple returns sets 
Inability to return multiple result sets from a query is something which
was
introduced at some point after/during the move from PostQuel to SQL.

It is still there at server side at protocol level, but no client I know
of supports
it, and as far as I know it is also hard to generate on server



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