Re: DO ... RETURNING

On 06/11/2013 06:17 AM, Pavel Stehule wrote:
> 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???
To be able to use pl/xxx languages on (almost) the same
way we use SQL now.

The current situation is akin to not being able to use queries
directly but always requiring you to create a view first and
then do "select ... from myview"

Think of DO ... RETURNING as inline function definition and
usage in one go.
> 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
If you can somehow avoid it for functions, then we could use the
same techniques for anonymous in-line functions as well.

And it is still more user friendly than creating a temporary
function for exactly the same purpose.
> * it is on way to introduce lot of NOT ANSI SQL extensions, that are
> not in other databases,
The whole pluggable language and CREATE FUNCTION is

"NOT ANSI SQL extensions", so why not make this easier to use.

> * it doesn't carry really new functionality
Yes, as pl/pgsql is (presumably :) ) Turing complete, no other
pl-s carry any "new functionality" .

What this does is making existing functionality more usable.

Again, if you have some good way to avoid specifying returns
list and deduce it from contents, i would be very interested.

This seems something relatively easy to do for SQL and
PL/PgSQL functions but much harder to achieve for
"a pl language" in general.

Almost the same functionality and syntax could be achieved
if we assume DO to always return SETOF RECORD and require
return definition on use like we do for functions returning SETOF RECORD

so instead of

hannu=# CREATE OR REPLACE FUNCTION testf(numeric) RETURNS SETOF RECORD as $$
return (('a','b','c'),('d','e','f'))
$$ LANGUAGE plpythonu;
CREATE FUNCTION
hannu=# SELECT * FROM testf(1) AS f(a text, b text, c text);
a | b | c
---+---+---
a | b | c
d | e | f
(2 rows)

one could write directly

DO LANGUAGE plpythonu $$
return (('a','b','c'),('d','e','f'))
$$ AS f(a text, b text, c text);

and get the same result.

The reason this does not work is that we would need a column definition
list even if we do not return anything:

hannu=# CREATE OR REPLACE FUNCTION test2(numeric) RETURNS SETOF RECORD as $$
return None
$$ LANGUAGE plpythonu;
CREATE FUNCTION

hannu=# select * from test2(1);
ERROR: a column definition list is required for functions returning
"record"
LINE 1: select * from storeopeninghours_tostring(1);

hannu=# select * from test2(1) as f(a text, b text, c text);
a | b | c
---+---+---
(0 rows)

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

PS: for some reason anonymous RETURNS SETOF RECORD does not work with pl/pgsql

hannu=# CREATE OR REPLACE FUNCTION test1()
RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY SELECT 'a','b','c';
END
$$ LANGUAGE plpgsql;
CREATE FUNCTION

hannu=# select * from test1() as f(a text, b text, c text);
ERROR: structure of query does not match function result type
DETAIL: Returned type unknown does not match expected type text in column 1.
CONTEXT: PL/pgSQL function test1() line 3 at RETURN QUERY

Hannu