2013/6/10 Hannu Krosing <ha...@2ndquadrant.com>: > On 06/10/2013 09:45 PM, Pavel Stehule wrote: >> 2013/6/10 David Fetter <da...@fetter.org>: >>> On Mon, Jun 10, 2013 at 09:23:19PM +0200, Pavel Stehule wrote: >>>> 2013/6/10 Hannu Krosing <ha...@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;
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Ü > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers