Re: DO ... RETURNING

On 06/10/2013 09:45 PM, Pavel Stehule wrote:
> 2013/6/10 David Fetter <david(at)fetter(dot)org>:
>> On Mon, Jun 10, 2013 at 09:23:19PM +0200, Pavel Stehule wrote:
>>> 2013/6/10 Hannu Krosing <hannu(at)2ndquadrant(dot)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 return
and 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Ü