Re: DO ... RETURNING

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 ?

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

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.

Regards

Pavel

>
> --
> Hannu Krosing
> PostgreSQL Consultant
> Performance, Scalability and High Availability
> 2ndQuadrant Nordic OÜ
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

* Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
> 2013/6/10 Hannu Krosing <hannu(at)2ndquadrant(dot)com>:
> > If there was then what were the arguments against doing this ?

I don't recall offhand, but it would be *extremely* useful to have.

> > Or was this just that it was not thought important at that time ?

For my part, without looking at what needs to happen for it, big +1
for adding it.

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

I don't see that as an argument against adding support for what can be
done today within our existing structures and API.

> and you don't need to define output structure - what is much more user friendly.

Sure, some day this would be a nice addition. There's no need to hold
up adding support for a defined table return type for DO waiting for
this other feature to happen though.

Thanks,

Stephen

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?

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

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

On 06/10/2013 09:34 PM, David Fetter wrote:
> 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?
Yes.

Of course we could default it to "RETURNS SETOF RECORD" :)
> 2. If I did, does this alleviate your concerns, Pavel?
>
> Cheers,
> David.

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

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Ü

* Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
> not too much. Two different concepts in one statement is not good
> idea.

What are the different concepts..? We already have set returning
functions, why would set returning anonymous functions be any different?

> What using a cursors as temporary solution?

That only works when you want to just return the results of a table.
What if you want to construct the data set in the DO block? Okay, fine,
you could use a temp table, but what if you don't have rights to create
temporary tables?

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

That would certainly be neat, but it doesn't have to be there in the
first incarnation, or really, ever, if it turns out to be painful to do.

> What about local temporary functions ??

You can already create temporary functions by simply creating them in
pg_temp. I'd like to see us add explicit support for them though, but I
don't see this as related to the DO-RETURNING question.

Thanks,

Stephen

2013/6/10 Stephen Frost <sfrost(at)snowman(dot)net>:
> * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
>> not too much. Two different concepts in one statement is not good
>> idea.
>
> What are the different concepts..? We already have set returning
> functions, why would set returning anonymous functions be any different?

1. DO as function
2. DO as batch

>
>> What using a cursors as temporary solution?
>
> That only works when you want to just return the results of a table.
> What if you want to construct the data set in the DO block? Okay, fine,
> you could use a temp table, but what if you don't have rights to create
> temporary tables?
>
>> Still I don't like this idea, because you should to support DO
>> RETURNING in other statements - like INSERT INTO DO RETURNING ???
>
> That would certainly be neat, but it doesn't have to be there in the
> first incarnation, or really, ever, if it turns out to be painful to do.
>

this is reason, why I dislike it - It is introduce significant strange
SQL extension

>> What about local temporary functions ??
>
> You can already create temporary functions by simply creating them in
> pg_temp. I'd like to see us add explicit support for them though, but I
> don't see this as related to the DO-RETURNING question.

I don't think we have to introduce a new NON ANSI concept, when is
possible using current feature.

so for me -1

Pavel

>
> Thanks,
>
> Stephen

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

Pavel,

* Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
> 2013/6/10 Stephen Frost <sfrost(at)snowman(dot)net>:
> > What are the different concepts..? We already have set returning
> > functions, why would set returning anonymous functions be any different?
>
> 1. DO as function
> 2. DO as batch

We already have set returning functions.

> >> Still I don't like this idea, because you should to support DO
> >> RETURNING in other statements - like INSERT INTO DO RETURNING ???
> >
> > That would certainly be neat, but it doesn't have to be there in the
> > first incarnation, or really, ever, if it turns out to be painful to do.
> >
>
> this is reason, why I dislike it - It is introduce significant strange
> SQL extension

DO already exists and isn't in the SQL standard. This isn't a
significant diversion from that, imv.

> > You can already create temporary functions by simply creating them in
> > pg_temp. I'd like to see us add explicit support for them though, but I
> > don't see this as related to the DO-RETURNING question.
>
> I don't think we have to introduce a new NON ANSI concept, when is
> possible using current feature.

DO already exists and would cover certain cases that temproary functions
don't today.

Thanks,

Stephen

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

Hello
>
> 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"
>

ok

probably we can allow using DO in CTE without impact on other SQL
statements, and for this purpose we need to know returned
TupleDescriptor early.

so I am able accept it, although I am thinking so we are going in
strange direction. We are not able do simply tasks simply (we cannot
execute SQL script on server side simply) :(. But it is not problem of
Hannu design.

other question - can we find some readable and intuitive syntax for DO
parametrization?

Regards

Pavel

On Tue, Jun 11, 2013 at 09:30:32AM +0200, Pavel Stehule wrote:
> Hello
> >
> > 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"
> >
>
> ok
>
> probably we can allow using DO in CTE without impact on other SQL
> statements, and for this purpose we need to know returned
> TupleDescriptor early.
>
> so I am able accept it, although I am thinking so we are going in
> strange direction. We are not able do simply tasks simply (we cannot
> execute SQL script on server side simply) :(. But it is not problem of
> Hannu design.
>
> other question - can we find some readable and intuitive syntax for DO
> parametrization?

The thing I'm thinking of where this does something we don't yet do is
in the realm of access control, e.g. allowing people to use DO rather
than giving them DDL permission to create temporary functions.

Is this what you have in mind? What other things?

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

2013/6/11 David Fetter <david(at)fetter(dot)org>:
> On Tue, Jun 11, 2013 at 09:30:32AM +0200, Pavel Stehule wrote:
>> Hello
>> >
>> > 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"
>> >
>>
>> ok
>>
>> probably we can allow using DO in CTE without impact on other SQL
>> statements, and for this purpose we need to know returned
>> TupleDescriptor early.
>>
>> so I am able accept it, although I am thinking so we are going in
>> strange direction. We are not able do simply tasks simply (we cannot
>> execute SQL script on server side simply) :(. But it is not problem of
>> Hannu design.
>>
>> other question - can we find some readable and intuitive syntax for DO
>> parametrization?
>
> The thing I'm thinking of where this does something we don't yet do is
> in the realm of access control, e.g. allowing people to use DO rather
> than giving them DDL permission to create temporary functions.
>
> Is this what you have in mind? What other things?

no - I don't see any difference if we allow temporary functions or DO statement.

I am missing some simple tool for scripting any administration tasks -
cleaning schema, cleaning databases, user management, report
calculation.

For this a some procedural functionality is necessary. Now I use a
combination bash/psql/SQL and PL/pgSQL functions. It works, it works
well, but it is hard for leaning, hard for maintaining.

I had a idea - procedural enhancing of psql. When I work on prototype
I leave this idea. Probably we can implement some like C macros to
psql, but it is a end. So my second idea is a plpgsql server side
scripting. PL/pgSQL is readable and strong - "just" we need to eval
our procedural code outside a query. For these tasks a performance is
not top - because there hard bottleneck is IO. And "slow combination
Bash/psql" worked without performance problems. A server side
scripting can be consistent - probably all PL languages should be used
and enough for typical administrative tasks.

Regards

Pavel

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

Hi,

That topic apparently raises each year and rehash the same points.

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> probably we can allow using DO in CTE without impact on other SQL
> statements, and for this purpose we need to know returned
> TupleDescriptor early.

I still think that DO being a utility statement, having it take
parameters and return data is going to be a wart in a part of the system
that has only too many of them already.

My thinking revolves around CTE support for functions:

WITH FUNCTION name(param, ...)
RETURNS type
LANGUAGE plpgsql AS (
$$ function body here $$
)
SELECT name(x, ...) FROM ...;

> so I am able accept it, although I am thinking so we are going in
> strange direction. We are not able do simply tasks simply (we cannot
> execute SQL script on server side simply) :(. But it is not problem of
> Hannu design.

With the DO utility command you can already execute SQL script on the
server quite simply. After all your proposals it's still unclear to me
where you want to process which data? (I admit this time I didn't pay
much attention, sorry about that)

> other question - can we find some readable and intuitive syntax for DO
> parametrization?

See above.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

2013/6/11 Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>:
> Hi,
>
> That topic apparently raises each year and rehash the same points.
>
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> probably we can allow using DO in CTE without impact on other SQL
>> statements, and for this purpose we need to know returned
>> TupleDescriptor early.
>
> I still think that DO being a utility statement, having it take
> parameters and return data is going to be a wart in a part of the system
> that has only too many of them already.
>
> My thinking revolves around CTE support for functions:
>
> WITH FUNCTION name(param, ...)
> RETURNS type
> LANGUAGE plpgsql AS (
> $$ function body here $$
> )
> SELECT name(x, ...) FROM ...;
>
>> so I am able accept it, although I am thinking so we are going in
>> strange direction. We are not able do simply tasks simply (we cannot
>> execute SQL script on server side simply) :(. But it is not problem of
>> Hannu design.
>
> With the DO utility command you can already execute SQL script on the
> server quite simply. After all your proposals it's still unclear to me
> where you want to process which data? (I admit this time I didn't pay
> much attention, sorry about that)

there are a significant limit - you cannot "simply" change a database
when you collect statistics over databases, you cannot drop database
...

you cannot return multiple returns sets - show info about tables,
schemas, indexes in one call

what I would

DO
$$
BEGIN
FOR r IN pg_databases
LOOP
CONNECT r.dbname;
FOR table_name IN SELECT * FROM pg_class ... WHERE owner = 'GD'
AND table_name LIKE 'xx%'
LOOP
IF pg_relsize(table_name) > xxx AND ... THEN
-- show info about dropped table
SELECT xx FROM pg_class, pg_attribute .... --- SHOW STRUCTURE OF
ANY INTERESTING TABLE -- multirecordset output
EXECUTE FORMAT('DROP TABLE %I', table_name);
...

Regards

Pavel

>
>> other question - can we find some readable and intuitive syntax for DO
>> parametrization?
>
> See above.
>
> Regards,
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> FOR r IN pg_databases
> LOOP
> CONNECT r.dbname;

Do you mean that you want to run this DO block on the client side?

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On 06/11/2013 11:30 AM, Pavel Stehule wrote:
> 2013/6/11 Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>:
>> Hi,
>>
>> That topic apparently raises each year and rehash the same points.
>>
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>>> probably we can allow using DO in CTE without impact on other SQL
>>> statements, and for this purpose we need to know returned
>>> TupleDescriptor early.
>> I still think that DO being a utility statement, having it take
>> parameters and return data is going to be a wart in a part of the system
>> that has only too many of them already.
>>
>> My thinking revolves around CTE support for functions:
>>
>> WITH FUNCTION name(param, ...)
>> RETURNS type
>> LANGUAGE plpgsql AS (
>> $$ function body here $$
>> )
>> SELECT name(x, ...) FROM ...;
Yes, this would be another way to do "in-line pl functions".

I do not think that DO ... RETURNING and WITH FUNCTION are mutually
exclusive.

Rather effort put into implementing one would also be useful for the other.
>>
>>> so I am able accept it, although I am thinking so we are going in
>>> strange direction. We are not able do simply tasks simply (we cannot
>>> execute SQL script on server side simply) :(. But it is not problem of
>>> Hannu design.
>> With the DO utility command you can already execute SQL script on the
>> server quite simply. After all your proposals it's still unclear to me
>> where you want to process which data? (I admit this time I didn't pay
>> much attention, sorry about that)
> there are a significant limit - you cannot "simply" change a database
> when you collect statistics over databases, you cannot drop database
I can do this easily in pl/python.

Actually this is how statistics are collected in
https://github.com/postsql/pgmon_zabbix

You can also do it using pl/proxy or in plpgsql using dblink.

I have seen quite complicated data analysing utilities - with process
control running 1 to N backends depending on load -written in pl/pgsql
using dblink.

> ...
>
> you cannot return multiple returns sets
Inability to return multiple result sets from a query is something which
was
introduced at some point after/during the move from PostQuel to SQL.

It is still there at server side at protocol level, but no client I know
of supports
it, and as far as I know it is also hard to generate on server

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

2013/6/11 Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> FOR r IN pg_databases
>> LOOP
>> CONNECT r.dbname;
>
> Do you mean that you want to run this DO block on the client side?

no, really no.

I am thinking about some outer server side process, where these
scripts will be executed. Maybe other usage for background worker
process

Pavel

>
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Tue, Jun 11, 2013 at 6:07 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2013/6/11 Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>:
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>>> FOR r IN pg_databases
>>> LOOP
>>> CONNECT r.dbname;
>>
>> Do you mean that you want to run this DO block on the client side?
>
> no, really no.
>
> I am thinking about some outer server side process, where these
> scripts will be executed. Maybe other usage for background worker
> process

+ 1

I agree with all your comments pretty much down the line. Need top
level CALL that supports parameterization and multiple sets that
utilizes background worker (we have example spi worker that gives some
hints about how pl/pgsql could be made to work). Because it's top
level (can't even be inlined to CTE), we can access behaviors that are
not possible in current pl/pgsql, for example setting transaction
isolation in advance of snapshot and changing database connection
mid-procedure.

merlin

* Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
> I agree with all your comments pretty much down the line. Need top
> level CALL that supports parameterization and multiple sets that
> utilizes background worker (we have example spi worker that gives some
> hints about how pl/pgsql could be made to work). Because it's top
> level (can't even be inlined to CTE), we can access behaviors that are
> not possible in current pl/pgsql, for example setting transaction
> isolation in advance of snapshot and changing database connection
> mid-procedure.

And this still has next-to-nothing to do with the specific proposal that
was put forward.

I'd like actual procedures too, but it's a completely different and
distinct thing from making DO blocks able to return something.

Thanks,

Stephen

2013/6/11 Stephen Frost <sfrost(at)snowman(dot)net>:
> * Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
>> I agree with all your comments pretty much down the line. Need top
>> level CALL that supports parameterization and multiple sets that
>> utilizes background worker (we have example spi worker that gives some
>> hints about how pl/pgsql could be made to work). Because it's top
>> level (can't even be inlined to CTE), we can access behaviors that are
>> not possible in current pl/pgsql, for example setting transaction
>> isolation in advance of snapshot and changing database connection
>> mid-procedure.
>
> And this still has next-to-nothing to do with the specific proposal that
> was put forward.
>
> I'd like actual procedures too, but it's a completely different and
> distinct thing from making DO blocks able to return something.

I think so it is related - we talk about future form of DO statement -
or about future form of server side scripting.

But it is not important in this moment

Pavel

>
> Thanks,
>
> Stephen

2013/6/11 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2013/6/11 Stephen Frost <sfrost(at)snowman(dot)net>:
>> * Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
>>> I agree with all your comments pretty much down the line. Need top
>>> level CALL that supports parameterization and multiple sets that
>>> utilizes background worker (we have example spi worker that gives some
>>> hints about how pl/pgsql could be made to work). Because it's top
>>> level (can't even be inlined to CTE), we can access behaviors that are
>>> not possible in current pl/pgsql, for example setting transaction
>>> isolation in advance of snapshot and changing database connection
>>> mid-procedure.
>>
>> And this still has next-to-nothing to do with the specific proposal that
>> was put forward.
>>
>> I'd like actual procedures too, but it's a completely different and
>> distinct thing from making DO blocks able to return something.
>
> I think so it is related - we talk about future form of DO statement -
> or about future form of server side scripting.
>
> But it is not important in this moment

I wrote, so I can live with Hannu proposal.

Regards

>
> Pavel
>
>>
>> Thanks,
>>
>> Stephen

On Tue, Jun 11, 2013 at 9:45 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
>> I agree with all your comments pretty much down the line. Need top
>> level CALL that supports parameterization and multiple sets that
>> utilizes background worker (we have example spi worker that gives some
>> hints about how pl/pgsql could be made to work). Because it's top
>> level (can't even be inlined to CTE), we can access behaviors that are
>> not possible in current pl/pgsql, for example setting transaction
>> isolation in advance of snapshot and changing database connection
>> mid-procedure.
>
> And this still has next-to-nothing to do with the specific proposal that
> was put forward.

It's a complete feature but completely relevant to the discussion --
the behaviors have a lot of overlap and CALL is in the standard
whereas the ad hoc feature DO isn't. This comes up in other feature
requests too, like psql bash-like scripting features.

That said, it would be pretty cool if you could inline DO into a CTE
or more generally into a query (is that possible?) -- then you'd have
something distinct.

merlin

* Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
> 2013/6/11 Stephen Frost <sfrost(at)snowman(dot)net>:
> > And this still has next-to-nothing to do with the specific proposal that
> > was put forward.
> >
> > I'd like actual procedures too, but it's a completely different and
> > distinct thing from making DO blocks able to return something.
>
> I think so it is related - we talk about future form of DO statement -
> or about future form of server side scripting.

I don't believe there's any intent to ever have DO used for stored
procedures. Not only are stored procedures deserving of their own
top-level command (eg: CALL, as has been discussed before..), but I
believe they would necessairly have different enough semantics that
shoe-horning them into DO would end up breaking backwards compatibility.

Thanks,

Stephen

On Tue, Jun 11, 2013 at 11:00 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
>> 2013/6/11 Stephen Frost <sfrost(at)snowman(dot)net>:
>> > And this still has next-to-nothing to do with the specific proposal that
>> > was put forward.
>> >
>> > I'd like actual procedures too, but it's a completely different and
>> > distinct thing from making DO blocks able to return something.
>>
>> I think so it is related - we talk about future form of DO statement -
>> or about future form of server side scripting.
>
> I don't believe there's any intent to ever have DO used for stored
> procedures. Not only are stored procedures deserving of their own
> top-level command (eg: CALL, as has been discussed before..), but I
> believe they would necessairly have different enough semantics that
> shoe-horning them into DO would end up breaking backwards compatibility.

I was not arguing to shoe-horn them into DO, but rather that the
proposal is shoe-horning into DO what should be in CALL (but I'm
having second thoughts about that -- CALL AFAIK can't do in-line code
blocks).

merlin

2013/6/11 Stephen Frost <sfrost(at)snowman(dot)net>:
> * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
>> 2013/6/11 Stephen Frost <sfrost(at)snowman(dot)net>:
>> > And this still has next-to-nothing to do with the specific proposal that
>> > was put forward.
>> >
>> > I'd like actual procedures too, but it's a completely different and
>> > distinct thing from making DO blocks able to return something.
>>
>> I think so it is related - we talk about future form of DO statement -
>> or about future form of server side scripting.
>
> I don't believe there's any intent to ever have DO used for stored
> procedures. Not only are stored procedures deserving of their own
> top-level command (eg: CALL, as has been discussed before..), but I
> believe they would necessairly have different enough semantics that
> shoe-horning them into DO would end up breaking backwards compatibility.

In this moment, DO doesn't support any feature that is in conflict
with stored procedure functionality, because it is based on functions,
and then it have to have limited functionality

Syntax of procedures and functions is relatively well defined

CREATE FUNCTION foo(..) ----> SELECT expression contains foo call

CREATE PROCEDURE foo(..) ---> CALL foo()

Now anonymous code block is based on functions, but it can be changed
to respect context or usage without lost of compatibility.

DO $$ ... $$ -- procedural behave -- just execute server side scripts

CTE DO RETURNING $$ ... $$ -- functional behave, functional limits.

Pavel

>
> Thanks,
>
> Stephen

On Tue, Jun 11, 2013 at 11:26 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2013/6/11 Stephen Frost <sfrost(at)snowman(dot)net>:
>> * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
>>> 2013/6/11 Stephen Frost <sfrost(at)snowman(dot)net>:
>>> > And this still has next-to-nothing to do with the specific proposal that
>>> > was put forward.
>>> >
>>> > I'd like actual procedures too, but it's a completely different and
>>> > distinct thing from making DO blocks able to return something.
>>>
>>> I think so it is related - we talk about future form of DO statement -
>>> or about future form of server side scripting.
>>
>> I don't believe there's any intent to ever have DO used for stored
>> procedures. Not only are stored procedures deserving of their own
>> top-level command (eg: CALL, as has been discussed before..), but I
>> believe they would necessairly have different enough semantics that
>> shoe-horning them into DO would end up breaking backwards compatibility.
>
> In this moment, DO doesn't support any feature that is in conflict
> with stored procedure functionality, because it is based on functions,
> and then it have to have limited functionality
>
> Syntax of procedures and functions is relatively well defined
>
> CREATE FUNCTION foo(..) ----> SELECT expression contains foo call
>
> CREATE PROCEDURE foo(..) ---> CALL foo()
>
> Now anonymous code block is based on functions, but it can be changed
> to respect context or usage without lost of compatibility.
>
> DO $$ ... $$ -- procedural behave -- just execute server side scripts
>
> CTE DO RETURNING $$ ... $$ -- functional behave, functional limits.

why does it have to be CTE?

merlin

On Tuesday, June 11, 2013, Pavel Stehule wrote:
>
> > I don't believe there's any intent to ever have DO used for stored
> > procedures. Not only are stored procedures deserving of their own
> > top-level command (eg: CALL, as has been discussed before..), but I
> > believe they would necessairly have different enough semantics that
> > shoe-horning them into DO would end up breaking backwards compatibility.
>
> In this moment, DO doesn't support any feature that is in conflict
> with stored procedure functionality, because it is based on functions,
> and then it have to have limited functionality
>

Let me re-state: I don't see DO, which is entirely function oriented today,
ever being redefined as suddenly being stored procedures instead. Claiming
that it wouldn't impact existing users of DO is a level of hand-waving that
I just can't follow. If nothing else, it would certainly impact external
language support.

If we're going to continue to hand-wave at this then I would argue that
we'd be able to magically make DO .. RETURNING also happily work in "stored
procedure" mode when the time comes without impacting users.

I'm done with this thread. For my part- we can and should extend DO to
support RETURNING. DO RETURNING support is a big enough and useful enough
addition that it can go in by itself. Attempting to feature-creep it to
also cover stored procedures will simply end up killing it unfairly and
will not move us forward at all.

We should also have stored procedures. Until there's an actual patch for
stored procedures which has some real conflict with DO RETURNING, I don't
see it as being relevant.

I look forward to patches for both.

Thanks,

Stephen

On 06/11/2013 05:27 PM, Merlin Moncure wrote:
> On Tue, Jun 11, 2013 at 9:45 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> * Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
>>> I agree with all your comments pretty much down the line. Need top
>>> level CALL that supports parameterization and multiple sets that
>>> utilizes background worker (we have example spi worker that gives some
>>> hints about how pl/pgsql could be made to work). Because it's top
>>> level (can't even be inlined to CTE), we can access behaviors that are
>>> not possible in current pl/pgsql, for example setting transaction
>>> isolation in advance of snapshot and changing database connection
>>> mid-procedure.
>> And this still has next-to-nothing to do with the specific proposal that
>> was put forward.
>
> It's a complete feature but completely relevant to the discussion --
> the behaviors have a lot of overlap and CALL is in the standard
> whereas the ad hoc feature DO isn't.
Could you point to the ISO/ANSI SQL CALL definition ?
> This comes up in other feature requests too, like psql bash-like scripting features.
Client side scripting is distinct from kind-of-server-side multi
database scripting.

As I said before, it is doable now using pl/pgsql and dblink / plproxy
and is trivial in pl/python.
> That said, it would be pretty cool if you could inline DO into a CTE
> or more generally into a query (is that possible?)
Currently not possible, especially because DO does not return
anything at this moment.

Again, you can of course run a DO query from any pl (excelp maybe SQL
and pl/pgsql)

hannu=# DO language plpythonu $$
plpy.execute("DO LANGUAGE plpgsql $x$ BEGIN RAISE NOTICE 'hi!'; END;$x$")
$$;
NOTICE: hi!
CONTEXT: SQL statement "DO LANGUAGE plpgsql $x$ BEGIN RAISE NOTICE
'hi!'; END;$x$"
PL/Python anonymous code block
DO

> -- then you'd have something distinct.
I like the CTE approach better as general "inline in query" approach and
DO ... RETURNING as a way for ad-hoc procs or direct remoting from client.

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

On Tue, Jun 11, 2013 at 12:01 PM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
> Could you point to the ISO/ANSI SQL CALL definition ?

I can't: no one can because the SQL standard is not available online.
But you can look at various proxies, for example here:
http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html
or here:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000897.htm

> As I said before, it is doable now using pl/pgsql and dblink / plproxy
> and is trivial in pl/python.
>> That said, it would be pretty cool if you could inline DO into a CTE
>> or more generally into a query (is that possible?)

> Currently not possible, especially because DO does not return
> anything at this moment.

right, well, I meant hypothetically; I was following along with your
thinking and extending inline code blocks to be able to be used in any
place where a function is allowed. Specifically I was wondering if
there are technical limitations that keep them limited to CTE usage
(as is the case with data modifying WITH).

merlin

On 2013-06-11 19:01 CEST, Hannu Krosing wrote:
> On 06/11/2013 05:27 PM, Merlin Moncure wrote:
>> On Tue, Jun 11, 2013 at 9:45 AM, Stephen Frost ... wrote:
>>> * Merlin Moncure ... wrote:
>>>> I agree with all your comments pretty much down the line. Need top
>>>> level CALL that supports parameterization and multiple sets that
>>>> utilizes background worker (we have example spi worker that gives some
>>>> hints about how pl/pgsql could be made to work). Because it's top
>>>> level (can't even be inlined to CTE), we can access behaviors that are
>>>> not possible in current pl/pgsql, for example setting transaction
>>>> isolation in advance of snapshot and changing database connection
>>>> mid-procedure.
>>> And this still has next-to-nothing to do with the specific proposal that
>>> was put forward.
>>
>> It's a complete feature but completely relevant to the discussion --
>> the behaviors have a lot of overlap and CALL is in the standard
>> whereas the ad hoc feature DO isn't.
> Could you point to the ISO/ANSI SQL CALL definition ?

On the publicly available standards page of ISO:

http://standards.iso.org/ittf/PubliclyAvailableStandards/index.html

searching in it for SQL shows link of "ISO/IEC 9075-1:2008":

http://standards.iso.org/ittf/PubliclyAvailableStandards/c045498_ISO_IEC_9075-1_2008.zip

when you follow and go through:

http://standards.iso.org/ittf/licence.html

i.e. accept the granted license, you may receive a personal single copy
non-distributable PDF version of

ISO/IEC 9075-1:2008, 3rd Edition, Information technology -- Database
languages -- SQL -- Part 1: Framework (SQL/Framework), of COmmittee
JTC1/SC32

There at least in section 5.3.4 you find the pointer, that among others,
the terms CALL and RETURN are specified in ISO9075-2.

So that "points", but to follow to the end ... ;-)

but at least it is clear from this source, that CALL seems to be a
statement in SQL to invoke a procedure or whatever name juggling suits.

>> ...

HTH,
Stefan.