Re: [HACKERS] DO ... RETURNING
On 06/11/2013 06:17 AM, Pavel Stehule wrote:
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???
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
Re: [HACKERS] DO ... RETURNING
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
2013/6/11 David Fetter da...@fetter.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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
Hi, That topic apparently raises each year and rehash the same points. Pavel Stehule pavel.steh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
2013/6/11 Dimitri Fontaine dimi...@2ndquadrant.fr:
Hi,
That topic apparently raises each year and rehash the same points.
Pavel Stehule pavel.steh...@gmail.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
Pavel Stehule pavel.steh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
On 06/11/2013 11:30 AM, Pavel Stehule wrote: 2013/6/11 Dimitri Fontaine dimi...@2ndquadrant.fr: Hi, That topic apparently raises each year and rehash the same points. Pavel Stehule pavel.steh...@gmail.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Ü -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
2013/6/11 Dimitri Fontaine dimi...@2ndquadrant.fr: Pavel Stehule pavel.steh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
On Tue, Jun 11, 2013 at 6:07 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/6/11 Dimitri Fontaine dimi...@2ndquadrant.fr: Pavel Stehule pavel.steh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
* Merlin Moncure (mmonc...@gmail.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 signature.asc Description: Digital signature
Re: [HACKERS] DO ... RETURNING
2013/6/11 Stephen Frost sfr...@snowman.net: * Merlin Moncure (mmonc...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
2013/6/11 Pavel Stehule pavel.steh...@gmail.com: 2013/6/11 Stephen Frost sfr...@snowman.net: * Merlin Moncure (mmonc...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
On Tue, Jun 11, 2013 at 9:45 AM, Stephen Frost sfr...@snowman.net wrote: * Merlin Moncure (mmonc...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
* Pavel Stehule (pavel.steh...@gmail.com) wrote: 2013/6/11 Stephen Frost sfr...@snowman.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 signature.asc Description: Digital signature
Re: [HACKERS] DO ... RETURNING
2013/6/11 Stephen Frost sfr...@snowman.net: * Pavel Stehule (pavel.steh...@gmail.com) wrote: 2013/6/11 Stephen Frost sfr...@snowman.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
On Tue, Jun 11, 2013 at 11:00 AM, Stephen Frost sfr...@snowman.net wrote: * Pavel Stehule (pavel.steh...@gmail.com) wrote: 2013/6/11 Stephen Frost sfr...@snowman.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
On Tue, Jun 11, 2013 at 11:26 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/6/11 Stephen Frost sfr...@snowman.net: * Pavel Stehule (pavel.steh...@gmail.com) wrote: 2013/6/11 Stephen Frost sfr...@snowman.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
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
Re: [HACKERS] DO ... RETURNING
On 06/11/2013 05:27 PM, Merlin Moncure wrote: On Tue, Jun 11, 2013 at 9:45 AM, Stephen Frost sfr...@snowman.net wrote: * Merlin Moncure (mmonc...@gmail.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Ü -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
On Tue, Jun 11, 2013 at 12:01 PM, Hannu Krosing ha...@2ndquadrant.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/r897.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
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.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
* Pavel Stehule (pavel.steh...@gmail.com) wrote: 2013/6/10 Hannu Krosing ha...@2ndquadrant.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 signature.asc Description: Digital signature
Re: [HACKERS] DO ... RETURNING
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?
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
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 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 da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
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Ü -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
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;
?
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Ü
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
* Pavel Stehule (pavel.steh...@gmail.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 signature.asc Description: Digital signature
Re: [HACKERS] DO ... RETURNING
2013/6/10 Stephen Frost sfr...@snowman.net: * Pavel Stehule (pavel.steh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DO ... RETURNING
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
Re: [HACKERS] DO ... RETURNING
Pavel, * Pavel Stehule (pavel.steh...@gmail.com) wrote: 2013/6/10 Stephen Frost sfr...@snowman.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 signature.asc Description: Digital signature
