Re: DO ... RETURNING

2013/6/10 Hannu Krosing <hannu(at)2ndquadrant(dot)com>:
> 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;

we doesn't support it, but oracle, db2 allows

SELECT * FROM TABLE(cursorname)

>
> ?
>>
>> 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 ?

it is same hard issue like TEMPORARY TABLES

Hannu, what is motivation for your proposal???

I have a two objections:

* it is not too user friendly - you have to specify returns list every
time, what is not comfort for very short life objects
* it is on way to introduce lot of NOT ANSI SQL extensions, that are
not in other databases,
* it doesn't carry really new functionality

Regards

Pavel

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