Re: DO ... RETURNING

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 using a cursors as temporary solution?

BEGIN;
DO $$
BEGIN
OPEN mycursor AS SELECT * FROM blablabla;
END $$
FETCH FROM mycursor;

COMMIT;

Still I don't like this idea, because you should to support DO
RETURNING in other statements - like INSERT INTO DO RETURNING ???

What about local temporary functions ??

CREATE TEMPORARY FUNCTION xx(a int)
RETURNES TABLE (xxx)

SELECT * FROM xxx;

>
> Cheers,
> David.
> --
> David Fetter <david(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate