Re: DO ... RETURNING

On 06/10/2013 09:45 PM, Pavel Stehule wrote:
> 2013/6/10 David Fetter <david@fetter.org>:
>> On Mon, Jun 10, 2013 at 09:23:19PM +0200, Pavel Stehule wrote:
>>> 2013/6/10 Hannu Krosing <hannu@2ndquadrant.com>:
>>>> Hallo Everybody
>>>>
>>>> As far as I can see, currently you can not return
>>>> anything out of a DO (anonymous code) block.
>>>>
>>>> Something like
>>>>
>>>> DO LANGUAGE plpythonu RETURNS TABLE (name text, uid int, gid int) $$
>>>> with open('/etc/passwd') as f:
>>>>     fields = f.readline().split(':')
>>>>     while fields:
>>>>         name, uid, gid = fields[0], int(fields[2]),int(fields[3])
>>>>         yield name, uid, gid
>>>>         fields = f.readline().split(':')
>>>> $$;
>>>>
>>>> As I did not pay attention when DO was introduced,
>>>> I thought it is faster to ask here than read all possibly
>>>> relevant mails in archives
>>>>
>>>> So: has there been a discussion on extending the DO
>>>> construct with ability to rturn data out of it, similar
>>>> to what named functions do.
>>>>
>>>> If there was then what were the arguments against doing this ?
>>>>
>>>> Or was this just that it was not thought important at that time ?
>>> I don't like this idea. I know so DO is +/- function, but it is too
>>> restrict. I hope so we will have a procedures with possibility unbound
>>> queries.
>>>
>>> and then you can do
>>>
>>> DO $$
>>>   SELECT * FROM pg_class;
>>>   SELECT * FROM pg_proc;
>>>   ...
>>> $$ LANGUAGE SQL;
>>>
>>> and you don't need to define output structure - what is much more user friendly.
>> If I understand the proposal correctly, the idea is only to try to
>> return something when DO is invoked with RETURNING.
>>
>> 1.  Did I understand correctly, Hannu?
>> 2.  If I did, does this alleviate your concerns, Pavel?
> not too much. Two different concepts in one statement is not good
> idea. 
What two different concepts do you mean ?
> What using a cursors as temporary solution?
>
> BEGIN;
> DO $$
> BEGIN
>  OPEN mycursor AS SELECT * FROM blablabla;
> END $$
> FETCH FROM mycursor;
>
> COMMIT;
How would this work in an SQL query ?

SELECT * FROM (FETCH FROM mycursor ) mc;

?
>
> Still I don't like this idea, because you should to support DO
> RETURNING in other statements - like INSERT INTO DO RETURNING ???
Yes, I really would like DO to be full "set returning construct"similar to SELECT or I/U/D RETURNING.


The syntax should be either RETURNS (as in function definition) or
RETURNING as for I/U/D.

I actually like the RETURNING better as it really does immediate returnand not just defines a function returning
something.

>
> What about local temporary functions ??
>
> CREATE TEMPORARY FUNCTION xx(a int)
> RETURNES TABLE (xxx)
>
> SELECT * FROM xxx;
You mean that we define and use it in the same statement and after ';'
ends the statement it disappears from scope ?

This would probably still bloat pg_function table ?


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