Re: [HACKERS] PL/pgSQL PERFORM with CTE
2013/8/28 Hannu Krosing ha...@2ndquadrant.com On 08/28/2013 12:10 AM, Pavel Stehule wrote: so if I write on client side BEGIN; SELECT 1,2; SELECT 2; SELECT 3,4; END; then I expect results 1,2 2 3,4 And you are perfectly ok to discard the results Actually it would be much more helpful to have discard the results syntax from client side, as in this case they take up network resources. Procedure is some batch moved and wrapped on server side CREATE PROCEDURE foo() BEGIN SELECT 1,2; SELECT 2; SELECT 3,4 END; And is not strange expect a result CALL foo() 1,2 2 3,4 Procedure is a script (batch) moved to server side for better performance and better reuse. And you are perfectly ok to discard the results here as well sure, depends how would to take a definition of procedure. Procedure is a classic procedure in PL/SQL - based on ADA procedures , or more like batch in T-SQL based on Sybase research, or some between in PSM in DB2. Every design has some advantage and disadvantage. But hardly to say what is a perfect design. I like a PL/SQL, but a procedures design (transaction control) is more obscure, than in T-SQL. DB2 procedures can use parameter list and can returns a status - it is a third design. I don't propose procedures like syntactic sugar for current PostgreSQL behave. If we can support procedures one times, then we should to get a new functionality, that is not possible (or not simple possible) now. In a function I do expect the result from select but I also expect that I can silently ignore the result. My proposal is consistent - no result goes to /dev/null without special mark. It is disabled (in function) or it goes to client (in procedures). So you can ignore the result in a procedure (by just skipping / not assigning it on client) but not in a function ? SQL function that is called from SELECT statement should to return only one result - without any side effect. It is a very good example, how clean and simple is using PostgreSQL functions that returns scalar or table, and how less clean and user friendly is usage functions that returns refcursors. I like a PostgreSQL design, that use a explicit or implicit transaction for every SELECT statement - and every function evaluation. It is simple, it is clean, and it is significant limit for some usage, where we can work more complexly with transactions. We must to break some code to more cliend-server calls. T-SQL is strict in this area, and disallow any side effect. Can you point out some other languages which *require* you to store the result of a function call or have a special syntax/keyword when you do not want to store it ? ADA is very strict about it. Regards Pavel Cheer -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ
Re: [HACKERS] PL/pgSQL PERFORM with CTE
2013/8/28 Robert Haas robertmh...@gmail.com On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule pavel.steh...@gmail.com wrote: what is magical? Stored procedures - we talk about this technology was a originally simple script moved from client side to server side. so if I write on client side BEGIN; SELECT 1,2; SELECT 2; SELECT 3,4; END; then I expect results 1,2 2 3,4 The biggest problem with this idea is that people will do it by accident with unacceptable frequency. During the decade or so I worked as a web programmer, I made this mistake a number of times, and judging by the comments on this thread, Josh Berkus has made it with some regularity as well. If experienced PostgreSQL hackers who know the system inside and out make such mistakes with some regularity, I think we can anticipate that novices will make them even more often. And, TBH, as others have said here, I find the requirement to use PERFORM rather than SELECT rather ridiculous. The clash with CTEs has been there since we added CTEs, and I've hit it more than once. Yeah, you can work around it, but it's annoying. And why annoy people? So +1 from me for de-requiring the use of PERFORM (though I think we should definitely continue to accept that syntax, for backward compatibility). At the end of the day, procedural languages in PostgreSQL are pluggable. So if we someday have the ability to return extra result sets on the fly, and if Pavel doesn't like the syntax we choose to use in PL/pgsql, he can (and, given previous history, very possibly will!) publish his own PL with different syntax. But I'm with the crowd that says that's not the right decision for PL/pgsql. I cannot to say what is good design for PL/pgSQL - only I feel so some variant of RETURN statement is not good, because semantic is significantly different. And I see a increasing inconsistency between a original ADA and PL/pgSQL. Sure, When I am thinking about PSM, I am thinking about T-SQL syntax, but there is little bit simpler situation - there is a precedent in PSM implementation in MySQL and some other new databases. Also, even if we did adopt Pavel's proposed meaning for SELECT 1,2, we still have a problem to solve, which is what the user should write when they want to run a query and ignore the results. The PERFORM solution was adequate at a time when all select queries started with SELECT, but now they can start with WITH or VALUES or TABLE as well, and while VALUES and TABLE may be ignorable, WITH certainly isn't. Requiring people to use silly workarounds like selecting into an otherwise-pointless dummy variable is not cool. If we reserve the undecorated-SELECT syntax to mean something else, then we've got to come up with some other way of solving David's original problem, and I don't think there are going to be many elegant options. Finally, I'd like to note that it's been longstanding frustration of mine that the PERFORM-SELECT transformation is leaky. For example, consider: rhaas=# do $$begin perform amazingly_well(); end;$$; ERROR: function amazingly_well() does not exist LINE 1: SELECT amazingly_well() I am thinking, so we are near a merit of problem - if I understand well, a PERFORM was originally designed instead a CALL statement. Due implementation it was used for some other SQL calls too. Origin PL/SQL doesn't allow SELECT without INTO. your example is good and important, because almost all described issues are related to unsuccessfully solved or a missing procedures. so main problem is a impossibility to write BEGIN CALL fce() or BEGIN fce(); A workaround in Postgres is PERFORM - and I really has nothing again to remove PERFORM for start of VOID functions! A unhelpful error message has zero relevant to topic - just almost all in PL/pgSQL is SELECT. Do you would to remove a := statement too? postgres=# do $$declare x int; begin x := notexisting(10); end; $$ ; ERROR: function notexisting(integer) does not exist LINE 1: SELECT notexisting(10) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT notexisting(10) CONTEXT: PL/pgSQL function inline_code_block line 1 at assignment Time: 148.760 ms Regards Pavel - Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 29, 2013, at 1:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I cannot to say what is good design for PL/pgSQL - only I feel so some variant of RETURN statement is not good, because semantic is significantly different. And I see a increasing inconsistency between a original ADA and PL/pgSQL. So YIELD or implement PL/PSM. Sure, When I am thinking about PSM, I am thinking about T-SQL syntax, but there is little bit simpler situation - there is a precedent in PSM implementation in MySQL and some other new databases. PL/pgSQL is not PSM. so main problem is a impossibility to write BEGIN CALL fce() or BEGIN fce(); A workaround in Postgres is PERFORM - and I really has nothing again to remove PERFORM for start of VOID functions! No reason SELECT could not work just a well. A unhelpful error message has zero relevant to topic - just almost all in PL/pgSQL is SELECT. Well, it was an aside, but points out another problem with PERFORM: It doesn't really exist. I gets replaced with SELECT internally, leading to confusing error messages. Solution: Allow SELECT instead of PERFORM. Do you would to remove a := statement too? postgres=# do $$declare x int; begin x := notexisting(10); end; $$ ; ERROR: function notexisting(integer) does not exist LINE 1: SELECT notexisting(10) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT notexisting(10) CONTEXT: PL/pgSQL function inline_code_block line 1 at assignment I agree it would be nice if it didn't report SELECT there, but at least it's not *removing* anything from what you see in the source. Best, David -- 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] PL/pgSQL PERFORM with CTE
2013/8/29 David E. Wheeler da...@justatheory.com On Aug 29, 2013, at 1:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I cannot to say what is good design for PL/pgSQL - only I feel so some variant of RETURN statement is not good, because semantic is significantly different. And I see a increasing inconsistency between a original ADA and PL/pgSQL. So YIELD or implement PL/PSM. We can discussed about syntax later - now it is offtopic and it is too early - still we miss a procedures. Sure, When I am thinking about PSM, I am thinking about T-SQL syntax, but there is little bit simpler situation - there is a precedent in PSM implementation in MySQL and some other new databases. PL/pgSQL is not PSM. yes, I know it well - although some syntax is shared - CASE statements so main problem is a impossibility to write BEGIN CALL fce() or BEGIN fce(); A workaround in Postgres is PERFORM - and I really has nothing again to remove PERFORM for start of VOID functions! No reason SELECT could not work just a well. No, originally, there was a target of compatibility with PL/SQL (more or less in some time), and PL/SQL disallow unbound SELECT. More - PL/SQL allow a direct procedure call - so some like PERFORM is useless there. A unhelpful error message has zero relevant to topic - just almost all in PL/pgSQL is SELECT. Well, it was an aside, but points out another problem with PERFORM: It doesn't really exist. I gets replaced with SELECT internally, leading to confusing error messages. Solution: Allow SELECT instead of PERFORM. Do you would to remove a := statement too? postgres=# do $$declare x int; begin x := notexisting(10); end; $$ ; ERROR: function notexisting(integer) does not exist LINE 1: SELECT notexisting(10) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT notexisting(10) CONTEXT: PL/pgSQL function inline_code_block line 1 at assignment I agree it would be nice if it didn't report SELECT there, but at least it's not *removing* anything from what you see in the source. It was a little bit a irony. I am think now so all problems about PERFORM is based on porting PL/SQL environment (that was a classic simplified ADA) to PostgreSQL without procedures. So PERFORM was a designed for evaluation of something like procedures - but there was nothing in this time - a VOID functions are younger. Without PERFORM we didn't do this talk. Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions. Regards Pavel Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 29, 2013, at 2:22 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions. Well, in this thread, I believe you are the only person who feels that way. And this proposal still would not let PERFORM work with CTEs. Best, David -- 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] PL/pgSQL PERFORM with CTE
On 2013-08-29 14:31:55 -0700, David E. Wheeler wrote: On Aug 29, 2013, at 2:22 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions. Well, in this thread, I believe you are the only person who feels that way. And this proposal still would not let PERFORM work with CTEs. I haven't made up my mind on whether PERFORM is a good idea or not, but independently from that we certainly could patch plpgsql to allow PERFORM WITH Doesn't look to hard to me from a quick look. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] PL/pgSQL PERFORM with CTE
On 08/29/2013 02:22 PM, Pavel Stehule wrote: Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions. You have yet to supply any arguments which support this position. Several people have pointed out that requiring PERFORM needlessly makes life hard for PL/pgSQL programmers, especially new ones. You have not given us any benefit it supplies in return. And no, I don't accept the idea that we might someday have some kind of conflicting syntax for stored procedures which nobody is working on as a valid argument. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] PL/pgSQL PERFORM with CTE
2013/8/29 David E. Wheeler da...@justatheory.com On Aug 29, 2013, at 2:22 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions. Well, in this thread, I believe you are the only person who feels that way. And this proposal still would not let PERFORM work with CTEs. I am thinking, so I propose a enough solution for you - when you use CTE for execution of VOID function, then result vill be VOID set, what we can accept as undefined result, and in this case a PERFORM should not be required. If CTE will return some result, then PERFORM should be required and PERFORM must to support CTE in all possible modes - updateable or not updateable queries. Regards Pavel Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On 08/29/2013 11:01 PM, David E. Wheeler wrote: On Aug 29, 2013, at 1:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I cannot to say what is good design for PL/pgSQL - only I feel so some variant of RETURN statement is not good, because semantic is significantly different. And I see a increasing inconsistency between a original ADA and PL/pgSQL. So YIELD or implement PL/PSM. We already have RETURN NEXT as equivalent to YIELD. -- 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] PL/pgSQL PERFORM with CTE
On Aug 29, 2013, at 2:41 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I am thinking, so I propose a enough solution for you - when you use CTE for execution of VOID function, then result vill be VOID set, what we can accept as undefined result, and in this case a PERFORM should not be required. If CTE will return some result, then PERFORM should be required and PERFORM must to support CTE in all possible modes - updateable or not updateable queries. If you can make PERFORM work with CTEs, that would be an improvement over the status quo. But I think there is no good reason not to let SELECT results be discarded, either. I know you think there are good reasons, but no one else in this thread is convince, AFAICT. Best, David -- 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] PL/pgSQL PERFORM with CTE
On 2013-08-29 14:40:24 -0700, Josh Berkus wrote: On 08/29/2013 02:22 PM, Pavel Stehule wrote: Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions. You have yet to supply any arguments which support this position. I am not convinced that's enough of a reason, but the requirement to use PERFORM for SELECTs that aren't stored anywhere actually has prevented bugs for me. I am not convinced that's worth the cost since I also have been annoyed by it several times, but it's not as crystal clear as you paint it. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] PL/pgSQL PERFORM with CTE
2013/8/29 Josh Berkus j...@agliodbs.com On 08/29/2013 02:22 PM, Pavel Stehule wrote: Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions. You have yet to supply any arguments which support this position. Several people have pointed out that requiring PERFORM needlessly makes life hard for PL/pgSQL programmers, especially new ones. You have not given us any benefit it supplies in return. And no, I don't accept the idea that we might someday have some kind of conflicting syntax for stored procedures which nobody is working on as a valid argument. The more stronger argument is not allow a useless execution. PL/pgSQL is a verbose language and it is based on very strict ADA language - a few a secure mechanism we dropped (and some from good reasons). So questions is - how much we would to go against a ADA ideas and PL/SQL rules. No think so PERFORM is a significant problem. A mayor problem for beginners is usually a fact, so PL/pgSQL is ALGOL like languages - and they don't know with these languages. Second problem is missing a more dynamic data structures. Next a really different syntax and usage of OUT variables, ... Regards Pavel -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 29, 2013, at 2:48 PM, Andres Freund and...@2ndquadrant.com wrote: You have yet to supply any arguments which support this position. I am not convinced that's enough of a reason, but the requirement to use PERFORM for SELECTs that aren't stored anywhere actually has prevented bugs for me. I am not convinced that's worth the cost since I also have been annoyed by it several times, but it's not as crystal clear as you paint it. So now we can revise Josh’s assertion to: “I have seen only tepid, unconvincing arguments which support this position.” I have thought that PERFORM was useful to mark queries that discard results in the past, but I think now that the mental load is higher, even if it can be fixed with CTEs, it’s more trouble than it’s worth. Best, David -- 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] PL/pgSQL PERFORM with CTE
2013/8/29 Pavel Stehule pavel.steh...@gmail.com 2013/8/29 Josh Berkus j...@agliodbs.com On 08/29/2013 02:22 PM, Pavel Stehule wrote: Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions. You have yet to supply any arguments which support this position. Several people have pointed out that requiring PERFORM needlessly makes life hard for PL/pgSQL programmers, especially new ones. You have not given us any benefit it supplies in return. And no, I don't accept the idea that we might someday have some kind of conflicting syntax for stored procedures which nobody is working on as a valid argument. The more stronger argument is not allow a useless execution. PL/pgSQL is a verbose language and it is based on very strict ADA language - a few a secure mechanism we dropped (and some from good reasons). So questions is - how much we would to go against a ADA ideas and PL/SQL rules. No think so PERFORM is a significant problem. A mayor problem for beginners is usually a fact, so PL/pgSQL is ALGOL like languages - and they don't know with these languages. Second problem is missing a more dynamic data structures. Next a really different syntax and usage of OUT variables, ... look to stackoverflow for often questions - the big issue is impossibility to iterate over record -- and return really dynamic result - pivot tables. Regards Pavel Regards Pavel -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On 2013-08-29 23:54:28 +0200, Pavel Stehule wrote: No think so PERFORM is a significant problem. A mayor problem for beginners is usually a fact, so PL/pgSQL is ALGOL like languages - and they don't know with these languages. Second problem is missing a more dynamic data structures. Next a really different syntax and usage of OUT variables, ... look to stackoverflow for often questions - the big issue is impossibility to iterate over record -- and return really dynamic result - pivot tables. So what? That's completely orthogonal to the discussion at hand. We're discussion about specific change, that there are other features people badly want shouldn't be stopping this. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] PL/pgSQL PERFORM with CTE
2013/8/29 David E. Wheeler da...@justatheory.com On Aug 29, 2013, at 2:48 PM, Andres Freund and...@2ndquadrant.com wrote: You have yet to supply any arguments which support this position. I am not convinced that's enough of a reason, but the requirement to use PERFORM for SELECTs that aren't stored anywhere actually has prevented bugs for me. I am not convinced that's worth the cost since I also have been annoyed by it several times, but it's not as crystal clear as you paint it. So now we can revise Josh’s assertion to: “I have seen only tepid, unconvincing arguments which support this position.” I have thought that PERFORM was useful to mark queries that discard results in the past, but I think now that the mental load is higher, even if it can be fixed with CTEs, it’s more trouble than it’s worth. when we fix a correct call of VOID function, then half of problem goes out. Second half is subjective. I remember, some years ago there was a proposal to change syntax and remove all verbosity features from PL/pgSQL - for example - using only END instead END IF, END LOOP, ... For me, this talk is similar - we have a language, that was designed be secure and verbose, what means, so developer must to write some chars more. You cannot to have both - short language and secure. Regards Pavel Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On 08/29/2013 05:31 PM, David E. Wheeler wrote: On Aug 29, 2013, at 2:22 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions. Well, in this thread, I believe you are the only person who feels that way. And this proposal still would not let PERFORM work with CTEs. Perhaps we could provide for SELECT INTO NULL or some such as a different spelling of PERFORM to indicate that the result should be discarded. cheers andrew -- 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] PL/pgSQL PERFORM with CTE
2013/8/29 Andres Freund and...@2ndquadrant.com On 2013-08-29 23:54:28 +0200, Pavel Stehule wrote: No think so PERFORM is a significant problem. A mayor problem for beginners is usually a fact, so PL/pgSQL is ALGOL like languages - and they don't know with these languages. Second problem is missing a more dynamic data structures. Next a really different syntax and usage of OUT variables, ... look to stackoverflow for often questions - the big issue is impossibility to iterate over record -- and return really dynamic result - pivot tables. So what? That's completely orthogonal to the discussion at hand. We're discussion about specific change, that there are other features people badly want shouldn't be stopping this. I am sorry, this is too offtopic. Regards Pavel Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On 08/30/2013 12:04 AM, Pavel Stehule wrote: 2013/8/29 David E. Wheeler da...@justatheory.com mailto:da...@justatheory.com On Aug 29, 2013, at 2:48 PM, Andres Freund and...@2ndquadrant.com mailto:and...@2ndquadrant.com wrote: You have yet to supply any arguments which support this position. I am not convinced that's enough of a reason, but the requirement to use PERFORM for SELECTs that aren't stored anywhere actually has prevented bugs for me. I am not convinced that's worth the cost since I also have been annoyed by it several times, but it's not as crystal clear as you paint it. So now we can revise Josh’s assertion to: “I have seen only tepid, unconvincing arguments which support this position.” I have thought that PERFORM was useful to mark queries that discard results in the past, but I think now that the mental load is higher, even if it can be fixed with CTEs, it’s more trouble than it’s worth. when we fix a correct call of VOID function, then half of problem goes out. Second half is subjective. I remember, some years ago there was a proposal to change syntax and remove all verbosity features from PL/pgSQL - for example - using only END instead END IF, END LOOP, ... This has a bit more value as it allows you to detect some (though not all) structural errors. PERFORM just forces you to replace some SELECTs just to confirm that you really did not want to capture the result. If the original aim was somehow connected with allowing direct function calls for PL/SQL compatibility, then why not just implement direct function calls instead and let us have plain SELECT back ? For me, this talk is similar - we have a language, that was designed be secure and verbose, If you want a verbose expression for ignoring the result I'd suggest something like SELECT ... IGNORING RESULT or SELECT ... INTO VOID It is self-describing like most of SQL, instead of making you wander each time if the word you want to replace SELECT with to ignore the result was PERFORM or EXECUTE :) But I can *not* see how allowing just SELECT and discarding the result has any less security, for any definition of security I can think of. For me it is just an arbitrary nuisance, with which I can live, but I'd prefer not to. Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule pavel.steh...@gmail.com wrote: what is magical? Stored procedures - we talk about this technology was a originally simple script moved from client side to server side. so if I write on client side BEGIN; SELECT 1,2; SELECT 2; SELECT 3,4; END; then I expect results 1,2 2 3,4 The biggest problem with this idea is that people will do it by accident with unacceptable frequency. During the decade or so I worked as a web programmer, I made this mistake a number of times, and judging by the comments on this thread, Josh Berkus has made it with some regularity as well. If experienced PostgreSQL hackers who know the system inside and out make such mistakes with some regularity, I think we can anticipate that novices will make them even more often. And, TBH, as others have said here, I find the requirement to use PERFORM rather than SELECT rather ridiculous. The clash with CTEs has been there since we added CTEs, and I've hit it more than once. Yeah, you can work around it, but it's annoying. And why annoy people? So +1 from me for de-requiring the use of PERFORM (though I think we should definitely continue to accept that syntax, for backward compatibility). At the end of the day, procedural languages in PostgreSQL are pluggable. So if we someday have the ability to return extra result sets on the fly, and if Pavel doesn't like the syntax we choose to use in PL/pgsql, he can (and, given previous history, very possibly will!) publish his own PL with different syntax. But I'm with the crowd that says that's not the right decision for PL/pgsql. Also, even if we did adopt Pavel's proposed meaning for SELECT 1,2, we still have a problem to solve, which is what the user should write when they want to run a query and ignore the results. The PERFORM solution was adequate at a time when all select queries started with SELECT, but now they can start with WITH or VALUES or TABLE as well, and while VALUES and TABLE may be ignorable, WITH certainly isn't. Requiring people to use silly workarounds like selecting into an otherwise-pointless dummy variable is not cool. If we reserve the undecorated-SELECT syntax to mean something else, then we've got to come up with some other way of solving David's original problem, and I don't think there are going to be many elegant options. Finally, I'd like to note that it's been longstanding frustration of mine that the PERFORM-SELECT transformation is leaky. For example, consider: rhaas=# do $$begin perform amazingly_well(); end;$$; ERROR: function amazingly_well() does not exist LINE 1: SELECT amazingly_well() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT amazingly_well() CONTEXT: PL/pgSQL function inline_code_block line 1 at PERFORM Hmm, the user might say. I didn't type the word SELECT anywhere, yet it shows up in the error message. How confusing! With a big enough hammer we could perhaps paper over this problem a bit more thoroughly, but since I've never liked the syntax to begin with, I advance this as another argument for killing it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] PL/pgSQL PERFORM with CTE
On Wed, Aug 28, 2013 at 2:59 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule pavel.steh...@gmail.com wrote: what is magical? Stored procedures - we talk about this technology was a originally simple script moved from client side to server side. so if I write on client side BEGIN; SELECT 1,2; SELECT 2; SELECT 3,4; END; then I expect results 1,2 2 3,4 The biggest problem with this idea is that people will do it by accident with unacceptable frequency. During the decade or so I worked as a web programmer, I made this mistake a number of times, and judging by the comments on this thread, Josh Berkus has made it with some regularity as well. If experienced PostgreSQL hackers who know the system inside and out make such mistakes with some regularity, I think we can anticipate that novices will make them even more often. And, TBH, as others have said here, I find the requirement to use PERFORM rather than SELECT rather ridiculous. The clash with CTEs has been there since we added CTEs, and I've hit it more than once. Yeah, you can work around it, but it's annoying. And why annoy people? So +1 from me for de-requiring the use of PERFORM (though I think we should definitely continue to accept that syntax, for backward compatibility). At the end of the day, procedural languages in PostgreSQL are pluggable. So if we someday have the ability to return extra result sets on the fly, and if Pavel doesn't like the syntax we choose to use in PL/pgsql, he can (and, given previous history, very possibly will!) publish his own PL with different syntax. But I'm with the crowd that says that's not the right decision for PL/pgsql. Also, even if we did adopt Pavel's proposed meaning for SELECT 1,2, we still have a problem to solve, which is what the user should write when they want to run a query and ignore the results. The PERFORM solution was adequate at a time when all select queries started with SELECT, but now they can start with WITH or VALUES or TABLE as well, and while VALUES and TABLE may be ignorable, WITH certainly isn't. Requiring people to use silly workarounds like selecting into an otherwise-pointless dummy variable is not cool. If we reserve the undecorated-SELECT syntax to mean something else, then we've got to come up with some other way of solving David's original problem, and I don't think there are going to be many elegant options. Finally, I'd like to note that it's been longstanding frustration of mine that the PERFORM-SELECT transformation is leaky. For example, consider: rhaas=# do $$begin perform amazingly_well(); end;$$; ERROR: function amazingly_well() does not exist LINE 1: SELECT amazingly_well() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT amazingly_well() CONTEXT: PL/pgSQL function inline_code_block line 1 at PERFORM Hmm, the user might say. I didn't type the word SELECT anywhere, yet it shows up in the error message. How confusing! With a big enough hammer we could perhaps paper over this problem a bit more thoroughly, but since I've never liked the syntax to begin with, I advance this as another argument for killing it. Right. Another pain point for me is that I frequently have to 'up-convert' functions from sql to pgsql (and sometimes the other way too). The perform requirement turns that into a headache. It looks like we are mostly ok on Oracle compatibility too. I'm a fan of David's 'YIELD' syntax concept as a line of analysis for 'mid procedure set returning' when we get there. 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] PL/pgSQL PERFORM with CTE
On 08/28/2013 09:59 PM, Robert Haas wrote: On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule pavel.steh...@gmail.com wrote: what is magical? Stored procedures - we talk about this technology was a originally simple script moved from client side to server side. so if I write on client side BEGIN; SELECT 1,2; SELECT 2; SELECT 3,4; END; then I expect results 1,2 2 3,4 The biggest problem with this idea is that people will do it by accident with unacceptable frequency. During the decade or so I worked as a web programmer, I made this mistake a number of times, and judging by the comments on this thread, Josh Berkus has made it with some regularity as well. If experienced PostgreSQL hackers who know the system inside and out make such mistakes with some regularity, I think we can anticipate that novices will make them even more often. Usually yo test your queries fom psql prompt and then copy/paste into your function. As ignoring the results need no conscious effort at psql prompt, it will always be a mild surprise that you have to jump through hoops to do it in pl/pgsql. And I can easily do this for example in pl/python - just do not assign the result from plpy.execute() and they get ignored with no extra effort whatsoever. ... Finally, I'd like to note that it's been longstanding frustration of mine that the PERFORM-SELECT transformation is leaky. For example, consider: rhaas=# do $$begin perform amazingly_well(); end;$$; ERROR: function amazingly_well() does not exist LINE 1: SELECT amazingly_well() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT amazingly_well() CONTEXT: PL/pgSQL function inline_code_block line 1 at PERFORM Hmm, the user might say. I didn't type the word SELECT anywhere, yet it shows up in the error message. How confusing! With a big enough hammer we could perhaps paper over this problem a bit more thoroughly, but since I've never liked the syntax to begin with, I advance this as another argument for killing it. Totally agree. Cheers -- 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] PL/pgSQL PERFORM with CTE
2013/8/24 Tom Lane t...@sss.pgh.pa.us Pavel Stehule pavel.steh...@gmail.com writes: Oracle has a special function for returning sets from procedures - see a new functionality Implicit Result Sets http://tkyte.blogspot.cz/2013/07/12c-implicit-result-sets.html That article is worth reading, because Tom K. points out exactly why T-SQL's approach is a bad idea compared to returning refcursors. It's not clear to me that we should be in a hurry to go there, much less try to be 100% syntax compatible with it. I disagree - Tom K. speaking about what he likes or dislikes (and about what he didn't use) He forgot about strong points of implicit result or interesting points. Clients usually has no problem with dynamic datasets - PHP, DBI, Llibpq, GUI components .. all libs support a generic access and this generic access is often used due less dependency on queries. There are a three interesting possibilities of implicit result sets: * Possibility to return dynamic dataset - when you don't know a result before execution - typical use case is a some form of pivot tables or some analytics queries. * Possibility to return multiple results as flattening of some multidimensional data. * Possibilty to write multiresults reports for one call execution. This functionality can be emulated by refcursors sets, but it is significantly less user friendly - so it is not widely used on Oracle's world. regards Pavel regards, tom lane
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 27, 2013, at 12:30 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I disagree - Tom K. speaking about what he likes or dislikes (and about what he didn't use) He forgot about strong points of implicit result or interesting points. Clients usually has no problem with dynamic datasets - PHP, DBI, Llibpq, GUI components .. all libs support a generic access and this generic access is often used due less dependency on queries. There are a three interesting possibilities of implicit result sets: * Possibility to return dynamic dataset - when you don't know a result before execution - typical use case is a some form of pivot tables or some analytics queries. * Possibility to return multiple results as flattening of some multidimensional data. * Possibilty to write multiresults reports for one call execution. As a dynamic language programmer, I can see this, as long as it’s not to the exclusion of strong typing interfaces, as well. However, I do not think it should be implicit. If a function or procedure wants to return values or query results or whatever to the caller, it should explicitly do so by using some key word. We already have RETURN, RETURN NEXT, RETURN QUERY, and RETURN EXECUTE, which is great for functions. For hypothetical functions or procedures that want to return data as it processes, rather than buffering the results and returning them all at once, perhaps we could add YIELD, YEILD QUERY, and YIELD EXECUTE. In fact, this is pretty much exactly what the key word YIELD is for in coroutines: https://en.wikipedia.org/wiki/Coroutine But whatever the keyword, I think it makes sense to require one to return results to the caller. Any query that does not return, yield, or capture (select into) values should just have its results discarded. My $0.02. Best, DAvid -- 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] PL/pgSQL PERFORM with CTE
On 08/27/2013 08:32 PM, David E. Wheeler wrote: On Aug 27, 2013, at 12:30 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I disagree - Tom K. speaking about what he likes or dislikes (and about what he didn't use) He forgot about strong points of implicit result or interesting points. Clients usually has no problem with dynamic datasets - PHP, DBI, Llibpq, GUI components .. all libs support a generic access and this generic access is often used due less dependency on queries. There are a three interesting possibilities of implicit result sets: * Possibility to return dynamic dataset - when you don't know a result before execution - typical use case is a some form of pivot tables or some analytics queries. * Possibility to return multiple results as flattening of some multidimensional data. * Possibilty to write multiresults reports for one call execution. As a dynamic language programmer, I can see this, as long as it’s not to the exclusion of strong typing interfaces, as well. However, I do not think it should be implicit. If a function or procedure wants to return values or query results or whatever to the caller, it should explicitly do so by using some key word. We already have RETURN, RETURN NEXT, RETURN QUERY, and RETURN EXECUTE, which is great for functions. For hypothetical functions or procedures that want to return data as it processes, rather than buffering the results and returning them all at once, perhaps we could add YIELD, YEILD QUERY, and YIELD EXECUTE. Conceptually RETURN NEXT is exactly the same as YIELD. If you look at the SRFs at the C level, then what you do is essentially a YIELD. It is only postgreslql backen SRW wrapper which then collects all these YIELDed values/rows and returns them as on set. In other words, our SRFs do not currently do any result streaming, though there is nothing in theory that would prevent them from doing so. Also, very similar FDWs do streaming. --- Hannu -- 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] PL/pgSQL PERFORM with CTE
2013/8/27 David E. Wheeler da...@justatheory.com On Aug 27, 2013, at 12:30 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I disagree - Tom K. speaking about what he likes or dislikes (and about what he didn't use) He forgot about strong points of implicit result or interesting points. Clients usually has no problem with dynamic datasets - PHP, DBI, Llibpq, GUI components .. all libs support a generic access and this generic access is often used due less dependency on queries. There are a three interesting possibilities of implicit result sets: * Possibility to return dynamic dataset - when you don't know a result before execution - typical use case is a some form of pivot tables or some analytics queries. * Possibility to return multiple results as flattening of some multidimensional data. * Possibilty to write multiresults reports for one call execution. As a dynamic language programmer, I can see this, as long as it’s not to the exclusion of strong typing interfaces, as well. However, I do not think it should be implicit. If a function or procedure wants to return values or query results or whatever to the caller, it should explicitly do so by using some key word. We already have RETURN, RETURN NEXT, RETURN QUERY, and RETURN EXECUTE, which is great for functions. For hypothetical functions or procedures that want to return data as it processes, rather than buffering the results and returning them all at once, perhaps we could add YIELD, YEILD QUERY, and YIELD EXECUTE. In fact, this is pretty much exactly what the key word YIELD is for in coroutines: https://en.wikipedia.org/wiki/Coroutine But whatever the keyword, I think it makes sense to require one to return results to the caller. Any query that does not return, yield, or capture (select into) values should just have its results discarded. A usual and first solution and syntax is defined by Sybase - we can define own syntax, but I don't think so it is necessary be original everywhere. My opinion is surely subjective - this feature is one from few features that are nice on T-SQL. Regards Pavel My $0.02. Best, DAvid
Re: [HACKERS] PL/pgSQL PERFORM with CTE
Pavel Stehule pavel.steh...@gmail.com writes: 2013/8/27 David E. Wheeler da...@justatheory.com But whatever the keyword, I think it makes sense to require one to return results to the caller. Any query that does not return, yield, or capture (select into) values should just have its results discarded. A usual and first solution and syntax is defined by Sybase - we can define own syntax, but I don't think so it is necessary be original everywhere. My opinion is surely subjective - this feature is one from few features that are nice on T-SQL. We aren't following T-SQL on any other syntax detail, so why would we start with this one? plpgsql is meant to follow Oracle syntax not T-SQL. I agree with David that we should use some new syntax to specify return-results-directly-to-client, assuming we ever get any such functionality. It seems like a pretty bad choice of default behavior, which is essentially what you're saying it should be. regards, tom lane -- 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] PL/pgSQL PERFORM with CTE
2013/8/27 Tom Lane t...@sss.pgh.pa.us Pavel Stehule pavel.steh...@gmail.com writes: 2013/8/27 David E. Wheeler da...@justatheory.com But whatever the keyword, I think it makes sense to require one to return results to the caller. Any query that does not return, yield, or capture (select into) values should just have its results discarded. A usual and first solution and syntax is defined by Sybase - we can define own syntax, but I don't think so it is necessary be original everywhere. My opinion is surely subjective - this feature is one from few features that are nice on T-SQL. We aren't following T-SQL on any other syntax detail, so why would we start with this one? plpgsql is meant to follow Oracle syntax not T-SQL. I agree with David that we should use some new syntax to specify return-results-directly-to-client, assuming we ever get any such functionality. It seems like a pretty bad choice of default behavior, which is essentially what you're saying it should be. this functionality should be disabled in functions. This can be allowed only for procedures started by CALL statements. I don't propose it for functions. Regards Pavel regards, tom lane
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 27, 2013, at 1:36 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I agree with David that we should use some new syntax to specify return-results-directly-to-client, assuming we ever get any such functionality. It seems like a pretty bad choice of default behavior, which is essentially what you're saying it should be. this functionality should be disabled in functions. This can be allowed only for procedures started by CALL statements. I don't propose it for functions. That does not make it a bad idea. Let me summarize: I propose to remove the requirement to use PERFORM to execute queries for which the result should be discarded. It should instead be implicit that results are discarded unless you capture them or return them. You propose to continue requiring PERFORM to execute queries for which the result should be discarded. This is so that, in the future, SQL statements can implicitly return to the caller. That sound about right to you? I *really* dislike the idea that some SQL execution implicitly returns from a PL/pgSQL function or procedure. That just seems too magical. I strongly prefer that the scope of the code executed in a function or procedure be limited to the scope of the function or procedure itself, and only return data to the caller if I explicitly tell it to. Much less magical, IMHO. Best, David -- 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] PL/pgSQL PERFORM with CTE
2013/8/27 David E. Wheeler da...@justatheory.com On Aug 27, 2013, at 1:36 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I agree with David that we should use some new syntax to specify return-results-directly-to-client, assuming we ever get any such functionality. It seems like a pretty bad choice of default behavior, which is essentially what you're saying it should be. this functionality should be disabled in functions. This can be allowed only for procedures started by CALL statements. I don't propose it for functions. That does not make it a bad idea. Let me summarize: I propose to remove the requirement to use PERFORM to execute queries for which the result should be discarded. It should instead be implicit that results are discarded unless you capture them or return them. You propose to continue requiring PERFORM to execute queries for which the result should be discarded. This is so that, in the future, SQL statements can implicitly return to the caller. That sound about right to you? I *really* dislike the idea that some SQL execution implicitly returns from a PL/pgSQL function or procedure. That just seems too magical. I strongly prefer that the scope of the code executed in a function or procedure be limited to the scope of the function or procedure itself, and only return data to the caller if I explicitly tell it to. Much less magical, IMHO. what is magical? Stored procedures - we talk about this technology was a originally simple script moved from client side to server side. so if I write on client side BEGIN; SELECT 1,2; SELECT 2; SELECT 3,4; END; then I expect results 1,2 2 3,4 Procedure is some batch moved and wrapped on server side CREATE PROCEDURE foo() BEGIN SELECT 1,2; SELECT 2; SELECT 3,4 END; And is not strange expect a result CALL foo() 1,2 2 3,4 Procedure is a script (batch) moved to server side for better performance and better reuse. You should not thinking about procedures like void functions, because it is a little bit different creature - and void functions is significantly limited in functionality. My proposal is consistent - no result goes to /dev/null without special mark. It is disabled (in function) or it goes to client (in procedures). Regards Pavel Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 27, 2013, at 3:10 PM, Pavel Stehule pavel.steh...@gmail.com wrote: CREATE PROCEDURE foo() BEGIN SELECT 1,2; SELECT 2; SELECT 3,4 END; And is not strange expect a result CALL foo() 1,2 2 3,4 Procedure is a script (batch) moved to server side for better performance and better reuse. I am not familiar with procedures, being a long time Postgres guy, but you’re right that it never occurred to me that they be thought of as batch files. Still, this is PL/pgSQL we’re talking about, not TSQL or SQL/PSM anything else. Perhaps your syntax suggestions make sense there, in which case, when you develop such functionality to Postgres, you would need to figure out how to get PERFORM to work with CTEs. But PL/pgSQL requires an explicit key word to return data, and I am hard pressed to see why that would change when it is used in procedures. And that makes PERFORM unnecessary, IME. You should not thinking about procedures like void functions, because it is a little bit different creature - and void functions is significantly limited in functionality. My proposal is consistent - no result goes to /dev/null without special mark. It is disabled (in function) or it goes to client (in procedures). Consistent, yes. But I’m not convinced -- and I’m *certainly* not convinced that PERFORM should be required to discard query results in PL/pgSQL *functions*, which is the issue on the table now. Best, David -- 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] PL/pgSQL PERFORM with CTE
On 08/28/2013 12:10 AM, Pavel Stehule wrote: so if I write on client side BEGIN; SELECT 1,2; SELECT 2; SELECT 3,4; END; then I expect results 1,2 2 3,4 And you are perfectly ok to discard the results Actually it would be much more helpful to have discard the results syntax from client side, as in this case they take up network resources. Procedure is some batch moved and wrapped on server side CREATE PROCEDURE foo() BEGIN SELECT 1,2; SELECT 2; SELECT 3,4 END; And is not strange expect a result CALL foo() 1,2 2 3,4 Procedure is a script (batch) moved to server side for better performance and better reuse. And you are perfectly ok to discard the results here as well In a function I do expect the result from select but I also expect that I can silently ignore the result. My proposal is consistent - no result goes to /dev/null without special mark. It is disabled (in function) or it goes to client (in procedures). So you can ignore the result in a procedure (by just skipping / not assigning it on client) but not in a function ? Can you point out some other languages which *require* you to store the result of a function call or have a special syntax/keyword when you do not want to store it ? Cheer -- 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] PL/pgSQL PERFORM with CTE
2013/8/24 Merlin Moncure mmonc...@gmail.com On Fri, Aug 23, 2013 at 5:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: Currently the only way to return query results to the caller is to use some form of RETURN. It is 100% consistent. I don't find it consistent at all, because what that means is that the data is to be returned to the SQL statement that called the function. What's more, the point of any such extension needs to be to allow *multiple* resultsets to be returned to the client --- if you only need one, you can have that functionality today with plain old SELECT FROM myfunction(). And returning some data but continuing execution is surely not consistent with RETURN. With set returning functions, RETURN QUERY etc means 'yield this data' -- which is pretty weird -- so your point only holds true for unadorned return (not RETURN NEXT , RETURN QUERY, etc). So I guess it's hard to claim RETURN means 'return control' though in a procedural sense. In a perfect world, maybe a separate keyword could have been made to distinguish those cases (e.h. YIELD QUERY), so I agree (after some reflection) with the spirit of your point. It's not good to have principle keywords do markedly different things. Basically it seems that we have two choices for how to represent this (hypothetical) future functionality: 1. Define SELECT without INTO as meaning return results directly to client; 2. Invent some new syntax to do it. In a green field I think we'd want to do #2, because #1 seems rather error-prone and unobvious. The only real attraction of #1, IMO, is that it's consistent with T-SQL. But that's not a terribly strong argument given the many existing inconsistencies between T-SQL and plpgsql. Very good points. I think the only compelling case for #1 that could be made would be to improve compatibility with pl/sql -- from what I can see Oracle has not defined the behavior (that is, in pl/sql select must have INTO) but maybe someone could comment on that. Oracle has a special function for returning sets from procedures - see a new functionality Implicit Result Sets http://tkyte.blogspot.cz/2013/07/12c-implicit-result-sets.html Although I am thinking so this feature is in T-SQL much more user friendly. Regards Pavel BTW, what about INSERT/UPDATE/DELETE RETURNING? Do we want to let these execute and throw away the data? The argument that this would be a feature seems a lot weaker than for SELECT, because after all you could usually just leave off the RETURNING clause. But I'm sure somebody will say they want to put a function with side-effects into RETURNING and then ignore its output. If we agree to relax PERFORM, those should be relaxed on the same basis. In fact, this is conclusive evidence that PERFORM is obsolete: it hails from the days where SELECT was the only data returning DML. merlin
Re: [HACKERS] PL/pgSQL PERFORM with CTE
Pavel Stehule pavel.steh...@gmail.com writes: Oracle has a special function for returning sets from procedures - see a new functionality Implicit Result Sets http://tkyte.blogspot.cz/2013/07/12c-implicit-result-sets.html That article is worth reading, because Tom K. points out exactly why T-SQL's approach is a bad idea compared to returning refcursors. It's not clear to me that we should be in a hurry to go there, much less try to be 100% syntax compatible with it. regards, tom lane -- 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] PL/pgSQL PERFORM with CTE
Pavel, But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL a unbound query is used to direct transfer data to client side. Are you planning to implement that in PL/pgSQL? Currently, PL/pgSQL requires RETURN in order to return a query result to the caller. Is there some reason we'd change that? If you're implementing TSQL-for-PostgreSQL, of course you might want to have different behavior with SELECT. However, TSQL is not PL/pgSQL. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] PL/pgSQL PERFORM with CTE
On Fri, Aug 23, 2013 at 12:51 PM, Josh Berkus j...@agliodbs.com wrote: Pavel, But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL a unbound query is used to direct transfer data to client side. Are you planning to implement that in PL/pgSQL? Currently, PL/pgSQL requires RETURN in order to return a query result to the caller. Is there some reason we'd change that? If you're implementing TSQL-for-PostgreSQL, of course you might want to have different behavior with SELECT. However, TSQL is not PL/pgSQL. I don't think Pavel's point makes sense in the context of functions. With stored procedures it might though -- but I don't see why that we need to reserve behavior for SELECT without INTO -- it can behave differently when executed with a hypothetical CALL. 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] PL/pgSQL PERFORM with CTE
2013/8/23 Josh Berkus j...@agliodbs.com Pavel, But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL a unbound query is used to direct transfer data to client side. Are you planning to implement that in PL/pgSQL? yes. I would to see a stored procedures with this functionality in pg Currently, PL/pgSQL requires RETURN in order to return a query result to the caller. Is there some reason we'd change that? it is different functionality. If you're implementing TSQL-for-PostgreSQL, of course you might want to have different behavior with SELECT. However, TSQL is not PL/pgSQL. I don't would to implement T-SQL. Same functionality has a PSM in MySQL. And in this moment, there is not any blocker why this should not be in Postgres. Regards Pavel -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Re: [HACKERS] PL/pgSQL PERFORM with CTE
2013/8/23 Merlin Moncure mmonc...@gmail.com On Fri, Aug 23, 2013 at 12:51 PM, Josh Berkus j...@agliodbs.com wrote: Pavel, But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL a unbound query is used to direct transfer data to client side. Are you planning to implement that in PL/pgSQL? Currently, PL/pgSQL requires RETURN in order to return a query result to the caller. Is there some reason we'd change that? If you're implementing TSQL-for-PostgreSQL, of course you might want to have different behavior with SELECT. However, TSQL is not PL/pgSQL. I don't think Pavel's point makes sense in the context of functions. With stored procedures it might though -- but I don't see why that we need to reserve behavior for SELECT without INTO -- it can behave differently when executed with a hypothetical CALL. I think so is not good if some programming language functionality does one in one context (functions) and does something else in second context (procedures). On second hand, I am thinking so requirement PERFORM is good. A query that does some, but result is ignored, is strange (and it can be a performance fault), so we should not be too friendly in this use case. PERFORM must be fixed, but should be used. Regards Pavel merlin
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Fri, Aug 23, 2013 at 1:38 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/8/23 Merlin Moncure mmonc...@gmail.com I think so is not good if some programming language functionality does one in one context (functions) and does something else in second context (procedures). It's not really different -- it means 'return if able'. Also there are a lot of things that would have to be different for other reasons especially transaction management. It's not reasonable to expect same behavior in function vs procedure context -- especially in terms of sending output to the caller. On second hand, I am thinking so requirement PERFORM is good. A query that does some, but result is ignored, is strange (and it can be a performance fault), so we should not be too friendly in this use case. Completely disagree. There are many cases where this is *not* strange. For example: SELECT writing_func(some_col) FROM foo; 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] PL/pgSQL PERFORM with CTE
2013/8/23 Merlin Moncure mmonc...@gmail.com On Fri, Aug 23, 2013 at 1:38 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/8/23 Merlin Moncure mmonc...@gmail.com I think so is not good if some programming language functionality does one in one context (functions) and does something else in second context (procedures). It's not really different -- it means 'return if able'. Also there are a lot of things that would have to be different for other reasons especially transaction management. It's not reasonable to expect same behavior in function vs procedure context -- especially in terms of sending output to the caller. On second hand, I am thinking so requirement PERFORM is good. A query that does some, but result is ignored, is strange (and it can be a performance fault), so we should not be too friendly in this use case. Completely disagree. There are many cases where this is *not* strange. For example: SELECT writing_func(some_col) FROM foo; it is about a personal taste - if you prefer more verbose or less verbose languages. I feeling a PERFORM usage as something special and you example is nice case, where I am think so PERFORM is good for verbosity. Regards Pavel merlin
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 23, 2013, at 8:51 PM, Pavel Stehule pavel.steh...@gmail.com wrote: it is about a personal taste - if you prefer more verbose or less verbose languages. I feeling a PERFORM usage as something special and you example is nice case, where I am think so PERFORM is good for verbosity. I really do not see the point of PERFORM in the current implementation of PL/pgSQL. If we were to allow SELECT to run when it is not returning a value or selecting into a variable, it would be unambiguous, since the other two cases require: * Using RETURN (or RETURN QUERY) * The INTO clause I have come around to the position that I think Tom, Josh, and Merlin have all put forward, that PERFORM is unnecessary. Unless Jan chimes in with something the rest of us have missed, it’s starting to feel like a consensus to me, other than your objections, of course. Best, David -- 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] PL/pgSQL PERFORM with CTE
2013/8/23 David E. Wheeler da...@justatheory.com On Aug 23, 2013, at 8:51 PM, Pavel Stehule pavel.steh...@gmail.com wrote: it is about a personal taste - if you prefer more verbose or less verbose languages. I feeling a PERFORM usage as something special and you example is nice case, where I am think so PERFORM is good for verbosity. I really do not see the point of PERFORM in the current implementation of PL/pgSQL. If we were to allow SELECT to run when it is not returning a value or selecting into a variable, it would be unambiguous, since the other two cases require: * Using RETURN (or RETURN QUERY) * The INTO clause I have come around to the position that I think Tom, Josh, and Merlin have all put forward, that PERFORM is unnecessary. Unless Jan chimes in with something the rest of us have missed, it’s starting to feel like a consensus to me, other than your objections, of course. ook Regards Pavel Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On 08/23/2013 11:30 AM, Pavel Stehule wrote: 2013/8/23 Josh Berkus j...@agliodbs.com Pavel, But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL a unbound query is used to direct transfer data to client side. Are you planning to implement that in PL/pgSQL? yes. I would to see a stored procedures with this functionality in pg Is there some reason we wouldn't use RETURN QUERY in that case, instead of SELECT? As I said above, it would be more consistent with existing PL/pgSQL. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] PL/pgSQL PERFORM with CTE
On 2013-08-23 22:02, Josh Berkus wrote: On 08/23/2013 11:30 AM, Pavel Stehule wrote: 2013/8/23 Josh Berkus j...@agliodbs.com Pavel, But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL a unbound query is used to direct transfer data to client side. Are you planning to implement that in PL/pgSQL? yes. I would to see a stored procedures with this functionality in pg Is there some reason we wouldn't use RETURN QUERY in that case, instead of SELECT? As I said above, it would be more consistent with existing PL/pgSQL. How would using the same syntax to do an entirely different thing be consistent? Regards, Marko Tiikkaja -- 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] PL/pgSQL PERFORM with CTE
On 08/23/2013 01:06 PM, Marko Tiikkaja wrote: Is there some reason we wouldn't use RETURN QUERY in that case, instead of SELECT? As I said above, it would be more consistent with existing PL/pgSQL. How would using the same syntax to do an entirely different thing be consistent? Currently the only way to return query results to the caller is to use some form of RETURN. It is 100% consistent. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] PL/pgSQL PERFORM with CTE
2013/8/23 Josh Berkus j...@agliodbs.com On 08/23/2013 11:30 AM, Pavel Stehule wrote: 2013/8/23 Josh Berkus j...@agliodbs.com Pavel, But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL a unbound query is used to direct transfer data to client side. Are you planning to implement that in PL/pgSQL? yes. I would to see a stored procedures with this functionality in pg Is there some reason we wouldn't use RETURN QUERY in that case, instead of SELECT? As I said above, it would be more consistent with existing PL/pgSQL. for example - multirecordset support. can be reason why distinguish between these syntax and these functionality. Regards Pavel -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Re: [HACKERS] PL/pgSQL PERFORM with CTE
Josh Berkus j...@agliodbs.com writes: On 08/23/2013 01:06 PM, Marko Tiikkaja wrote: Is there some reason we wouldn't use RETURN QUERY in that case, instead of SELECT? As I said above, it would be more consistent with existing PL/pgSQL. How would using the same syntax to do an entirely different thing be consistent? Currently the only way to return query results to the caller is to use some form of RETURN. It is 100% consistent. I don't find it consistent at all, because what that means is that the data is to be returned to the SQL statement that called the function. What's more, the point of any such extension needs to be to allow *multiple* resultsets to be returned to the client --- if you only need one, you can have that functionality today with plain old SELECT FROM myfunction(). And returning some data but continuing execution is surely not consistent with RETURN. Basically it seems that we have two choices for how to represent this (hypothetical) future functionality: 1. Define SELECT without INTO as meaning return results directly to client; 2. Invent some new syntax to do it. In a green field I think we'd want to do #2, because #1 seems rather error-prone and unobvious. The only real attraction of #1, IMO, is that it's consistent with T-SQL. But that's not a terribly strong argument given the many existing inconsistencies between T-SQL and plpgsql. BTW, what about INSERT/UPDATE/DELETE RETURNING? Do we want to let these execute and throw away the data? The argument that this would be a feature seems a lot weaker than for SELECT, because after all you could usually just leave off the RETURNING clause. But I'm sure somebody will say they want to put a function with side-effects into RETURNING and then ignore its output. regards, tom lane -- 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] PL/pgSQL PERFORM with CTE
On 08/20/2013 05:48 AM, Merlin Moncure wrote: On Tue, Aug 20, 2013 at 7:44 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/8/20 Merlin Moncure mmonc...@gmail.com On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund and...@2ndquadrant.com I think the way forward is to remove the restriction such that data returning queries must be PERFORM'd I disagree, current rule has sense. Curious what your thinking is there. I have to agree with Merlin. I've always thought the PERFORM thing was a wart we'd get around to removing eventually. In what way is it a feature? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] PL/pgSQL PERFORM with CTE
Josh Berkus j...@agliodbs.com writes: I have to agree with Merlin. I've always thought the PERFORM thing was a wart we'd get around to removing eventually. In what way is it a feature? I'd always assumed it was a PL/SQL compatibility thing, but a look in a PL/SQL reference doesn't turn up any such statement. So far as I can see, the situation in Oracle PL/SQL is: * SELECT must have an INTO clause; * there isn't any way to execute a SELECT and just discard the result. Jan might remember more about his thought process here, but I'm thinking that he copied the SELECT-must-have-INTO rule and then chose to invent a new statement for the case of wanting to discard the result. I think you could make an argument for that being good from an oversight-detection standpoint, but it's not a really strong argument. Particularly in view of the difficulty we'd have in supporting WITH ... PERFORM ... nicely, it doesn't seem unreasonable to just allow SELECT-without-INTO. regards, tom lane -- 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] PL/pgSQL PERFORM with CTE
Tom, Jan might remember more about his thought process here, but I'm thinking that he copied the SELECT-must-have-INTO rule and then chose to invent a new statement for the case of wanting to discard the result. I think you could make an argument for that being good from an oversight-detection standpoint, but it's not a really strong argument. Particularly in view of the difficulty we'd have in supporting WITH ... PERFORM ... nicely, it doesn't seem unreasonable to just allow SELECT-without-INTO. For my own part, I have to correct forgetting to substitute PERORM for SELECT around 200 times each major PL/pgSQL project. So it would be user-friendly for it to go away. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] PL/pgSQL PERFORM with CTE
2013/8/23 Josh Berkus j...@agliodbs.com Tom, Jan might remember more about his thought process here, but I'm thinking that he copied the SELECT-must-have-INTO rule and then chose to invent a new statement for the case of wanting to discard the result. I think you could make an argument for that being good from an oversight-detection standpoint, but it's not a really strong argument. Particularly in view of the difficulty we'd have in supporting WITH ... PERFORM ... nicely, it doesn't seem unreasonable to just allow SELECT-without-INTO. For my own part, I have to correct forgetting to substitute PERORM for SELECT around 200 times each major PL/pgSQL project. So it would be user-friendly for it to go away. But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL a unbound query is used to direct transfer data to client side. There BEGIN SELECT 10; END; doesn't mean ignore result of query, but it means push result to client. And we doesn't support this functionality, so I prefer doesn't allow this syntax. Regards Pavel -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Re: [HACKERS] PL/pgSQL PERFORM with CTE
Hello 2013/8/20 David E. Wheeler da...@justatheory.com Hackers, This seems reasonable: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$# SELECT * from now; david$# END; david$# $$; ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement This not so much: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$# PERFORM * from now; david$# END; david$# $$; ERROR: syntax error at or near PERFORM LINE 4: PERFORM * from now; ^ Parser bug in PL/pgSQL, perhaps? no you cannot use a PL/pgSQL statement inside SQL statement. Regards Pavel Best, David -- 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] PL/pgSQL PERFORM with CTE
Hi Pavel, On Aug 20, 2013, at 2:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$# PERFORM * from now; david$# END; david$# $$; ERROR: syntax error at or near PERFORM LINE 4: PERFORM * from now; ^ Parser bug in PL/pgSQL, perhaps? no you cannot use a PL/pgSQL statement inside SQL statement. Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select into but never otherwise use. Inelegant, IMHO. Perhaps I’m missing some other way to do it? If so, it would help if the hint suggesting the use of PERFORM pointed to such alternatives. Best, David -- 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] PL/pgSQL PERFORM with CTE
2013/8/20 David E. Wheeler da...@justatheory.com Hi Pavel, On Aug 20, 2013, at 2:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$# PERFORM * from now; david$# END; david$# $$; ERROR: syntax error at or near PERFORM LINE 4: PERFORM * from now; ^ Parser bug in PL/pgSQL, perhaps? no you cannot use a PL/pgSQL statement inside SQL statement. Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select into but never otherwise use. Inelegant, IMHO. Perhaps I’m missing some other way to do it? If so, it would help if the hint suggesting the use of PERFORM pointed to such alternatives. postgres=# DO $$ BEGIN PERFORM * FROM (WITH now AS (SELECT now()) SELECT * from now) x; END; $$; DO postgres=# Regards Pavel Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote: Hi Pavel, On Aug 20, 2013, at 2:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$# PERFORM * from now; david$# END; david$# $$; ERROR: syntax error at or near PERFORM LINE 4: PERFORM * from now; ^ Parser bug in PL/pgSQL, perhaps? no you cannot use a PL/pgSQL statement inside SQL statement. Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select into but never otherwise use. Inelegant, IMHO. Perhaps I’m missing some other way to do it? If so, it would help if the hint suggesting the use of PERFORM pointed to such alternatives. Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. I don't think the intermingled plpgsql/sql grammars allow a nice way right now. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] PL/pgSQL PERFORM with CTE
On 8/20/13 2:21 PM, Pavel Stehule wrote: 2013/8/20 David E. Wheeler da...@justatheory.com Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select into but never otherwise use. Inelegant, IMHO. Perhaps I’m missing some other way to do it? If so, it would help if the hint suggesting the use of PERFORM pointed to such alternatives. postgres=# DO $$ BEGIN PERFORM * FROM (WITH now AS (SELECT now()) SELECT * from now) x; END; $$; DO .. which doesn't work if you want to use table-modifying CTEs. Regards, Marko Tiikkaja -- 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] PL/pgSQL PERFORM with CTE
2013/8/20 Andres Freund and...@2ndquadrant.com On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote: Hi Pavel, On Aug 20, 2013, at 2:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$# PERFORM * from now; david$# END; david$# $$; ERROR: syntax error at or near PERFORM LINE 4: PERFORM * from now; ^ Parser bug in PL/pgSQL, perhaps? no you cannot use a PL/pgSQL statement inside SQL statement. Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select into but never otherwise use. Inelegant, IMHO. Perhaps I’m missing some other way to do it? If so, it would help if the hint suggesting the use of PERFORM pointed to such alternatives. Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. I don't think the intermingled plpgsql/sql grammars allow a nice way right now. +1 Pavel Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 20, 2013, at 2:24 PM, Marko Tiikkaja ma...@joh.to wrote: postgres=# DO $$ BEGIN PERFORM * FROM (WITH now AS (SELECT now()) SELECT * from now) x; END; $$; DO .. which doesn't work if you want to use table-modifying CTEs. Which, in fact, is exactly my use case (though not what I posted upthread). Best, David -- 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] PL/pgSQL PERFORM with CTE
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 2:24 PM, Marko Tiikkaja ma...@joh.to wrote: postgres=# DO $$ BEGIN PERFORM * FROM (WITH now AS (SELECT now()) SELECT * from now) x; END; $$; DO .. which doesn't work if you want to use table-modifying CTEs. Which, in fact, is exactly my use case (though not what I posted upthread). but it works postgres=# do $$begin with x as (select 10) insert into omega select * from x; end;$$; DO Regards Pavel Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 20, 2013, at 2:31 PM, Pavel Stehule pavel.steh...@gmail.com wrote: but it works postgres=# do $$begin with x as (select 10) insert into omega select * from x; end;$$; DO But this does not: david=# DO $$ david$# BEGIN david$# PERFORM * FROM ( david$# WITH inserted AS ( david$# INSERT INTO foo values (1) RETURNING id david$# ) SELECT inserted.id david$# ) x; david$# END; david$# $$; ERROR: WITH clause containing a data-modifying statement must be at the top level LINE 2: WITH inserted AS ( ^ QUERY: SELECT * FROM ( WITH inserted AS ( INSERT INTO foo values (1) RETURNING id ) SELECT inserted.id ) x CONTEXT: PL/pgSQL function inline_code_block line 3 at PERFORM Best, David -- 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] PL/pgSQL PERFORM with CTE
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 2:31 PM, Pavel Stehule pavel.steh...@gmail.com wrote: but it works postgres=# do $$begin with x as (select 10) insert into omega select * from x; end;$$; DO But this does not: david=# DO $$ david$# BEGIN david$# PERFORM * FROM ( david$# WITH inserted AS ( david$# INSERT INTO foo values (1) RETURNING id david$# ) SELECT inserted.id david$# ) x; david$# END; david$# $$; ERROR: WITH clause containing a data-modifying statement must be at the top level LINE 2: WITH inserted AS ( ^ QUERY: SELECT * FROM ( WITH inserted AS ( INSERT INTO foo values (1) RETURNING id ) SELECT inserted.id ) x CONTEXT: PL/pgSQL function inline_code_block line 3 at PERFORM yes, in this context you should not use a PERFORM PL/pgSQL protect you before useless queries - so you can use a CTE without returned result directly or CTE with result via PERFORM statement (and in this case it must be unmodifing CTE). Sorry, I don't see any problem - why you return some from CTE and then you throw this result? Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote: Hi Pavel, On Aug 20, 2013, at 2:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$# PERFORM * from now; david$# END; david$# $$; ERROR: syntax error at or near PERFORM LINE 4: PERFORM * from now; ^ Parser bug in PL/pgSQL, perhaps? no you cannot use a PL/pgSQL statement inside SQL statement. Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select into but never otherwise use. Inelegant, IMHO. Perhaps I’m missing some other way to do it? If so, it would help if the hint suggesting the use of PERFORM pointed to such alternatives. Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. I don't think the intermingled plpgsql/sql grammars allow a nice way right now. I think the way forward is to remove the restriction such that data returning queries must be PERFORM'd. 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] PL/pgSQL PERFORM with CTE
2013/8/20 Merlin Moncure mmonc...@gmail.com On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote: Hi Pavel, On Aug 20, 2013, at 2:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$# PERFORM * from now; david$# END; david$# $$; ERROR: syntax error at or near PERFORM LINE 4: PERFORM * from now; ^ Parser bug in PL/pgSQL, perhaps? no you cannot use a PL/pgSQL statement inside SQL statement. Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select into but never otherwise use. Inelegant, IMHO. Perhaps I’m missing some other way to do it? If so, it would help if the hint suggesting the use of PERFORM pointed to such alternatives. Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. I don't think the intermingled plpgsql/sql grammars allow a nice way right now. I think the way forward is to remove the restriction such that data returning queries must be PERFORM'd I disagree, current rule has sense. Pavel merlin
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 20, 2013, at 2:41 PM, Pavel Stehule pavel.steh...@gmail.com wrote: yes, in this context you should not use a PERFORM PL/pgSQL protect you before useless queries - so you can use a CTE without returned result directly or CTE with result via PERFORM statement (and in this case it must be unmodifing CTE). Sorry, I don't see any problem - why you return some from CTE and then you throw this result? I am passing the values returned from a CTE to a call to pg_notify(). I do not care to collect the output of pg_notify(), which returns VOID. Best, David -- 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] PL/pgSQL PERFORM with CTE
2013-08-20 14:35 keltezéssel, David E. Wheeler írta: On Aug 20, 2013, at 2:31 PM, Pavel Stehule pavel.steh...@gmail.com wrote: but it works postgres=# do $$begin with x as (select 10) insert into omega select * from x; end;$$; DO But this does not: david=# DO $$ david$# BEGIN david$# PERFORM * FROM ( david$# WITH inserted AS ( david$# INSERT INTO foo values (1) RETURNING id david$# ) SELECT inserted.id david$# ) x; david$# END; david$# $$; ERROR: WITH clause containing a data-modifying statement must be at the top level LINE 2: WITH inserted AS ( ^ QUERY: SELECT * FROM ( WITH inserted AS ( INSERT INTO foo values (1) RETURNING id ) SELECT inserted.id ) x CONTEXT: PL/pgSQL function inline_code_block line 3 at PERFORM This is the same error as if you put the WITH into a subquery, which is what PERFORM does. Proof: SELECT * FROM ( WITH inserted AS ( INSERT INTO foo values (1) RETURNING id ) SELECT inserted.id ) x; Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- 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] PL/pgSQL PERFORM with CTE
On Aug 20, 2013, at 2:44 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I think the way forward is to remove the restriction such that data returning queries must be PERFORM'd I disagree, current rule has sense. Perhaps a DECLARE FUNCTION attribute that turns off the functionality, then? Best, David -- 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] PL/pgSQL PERFORM with CTE
On Tue, Aug 20, 2013 at 7:44 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/8/20 Merlin Moncure mmonc...@gmail.com On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote: Hi Pavel, On Aug 20, 2013, at 2:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$# PERFORM * from now; david$# END; david$# $$; ERROR: syntax error at or near PERFORM LINE 4: PERFORM * from now; ^ Parser bug in PL/pgSQL, perhaps? no you cannot use a PL/pgSQL statement inside SQL statement. Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select into but never otherwise use. Inelegant, IMHO. Perhaps I’m missing some other way to do it? If so, it would help if the hint suggesting the use of PERFORM pointed to such alternatives. Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. I don't think the intermingled plpgsql/sql grammars allow a nice way right now. I think the way forward is to remove the restriction such that data returning queries must be PERFORM'd I disagree, current rule has sense. Curious what your thinking is there. 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] PL/pgSQL PERFORM with CTE
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 2:41 PM, Pavel Stehule pavel.steh...@gmail.com wrote: yes, in this context you should not use a PERFORM PL/pgSQL protect you before useless queries - so you can use a CTE without returned result directly or CTE with result via PERFORM statement (and in this case it must be unmodifing CTE). Sorry, I don't see any problem - why you return some from CTE and then you throw this result? I am passing the values returned from a CTE to a call to pg_notify(). I do not care to collect the output of pg_notify(), which returns VOID. it is little bit different issue - PL/pgSQL doesn't check if returned type is VOID - it can be allowed, I am thinking. So check of empty result can be enhanced. Regards Pavel Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On 8/20/13 2:53 PM, Pavel Stehule wrote: 2013/8/20 David E. Wheeler da...@justatheory.com I am passing the values returned from a CTE to a call to pg_notify(). I do not care to collect the output of pg_notify(), which returns VOID. it is little bit different issue - PL/pgSQL doesn't check if returned type is VOID - it can be allowed, I am thinking. So check of empty result can be enhanced. That still doesn't help at all in the case where the function returns something, but you simply don't care about the result. That said, I don't think this issue is big enough to start radically changing how SELECT without INTO works -- you can always get around this limitation by SELECTing into a variable, as David mentioned in his original message. It's annoying, but it works. Regards, Marko Tiikkaja -- 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] PL/pgSQL PERFORM with CTE
On Aug 20, 2013, at 2:53 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I am passing the values returned from a CTE to a call to pg_notify(). I do not care to collect the output of pg_notify(), which returns VOID. it is little bit different issue - PL/pgSQL doesn't check if returned type is VOID - it can be allowed, I am thinking. So check of empty result can be enhanced. I am confused. I do not need to check the result (except via FOUND). But I am sure I can think of other situations where I am calling something where I do not care about the result, even if it returns one. Best, David -- 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] PL/pgSQL PERFORM with CTE
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 2:53 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I am passing the values returned from a CTE to a call to pg_notify(). I do not care to collect the output of pg_notify(), which returns VOID. it is little bit different issue - PL/pgSQL doesn't check if returned type is VOID - it can be allowed, I am thinking. So check of empty result can be enhanced. I am confused. I do not need to check the result (except via FOUND). But I am sure I can think of other situations where I am calling something where I do not care about the result, even if it returns one. When you would to ignore result, then you should to use a PERFORM - actually, it is limited now and should be fixed. Have no problem with it. I don't would to enable a free unbound statement that returns result. Regards Pavel Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 20, 2013, at 3:05 PM, Pavel Stehule pavel.steh...@gmail.com wrote: When you would to ignore result, then you should to use a PERFORM - actually, it is limited now and should be fixed. Have no problem with it. Glad to have you on board. :-) I don't would to enable a free unbound statement that returns result. I have no pony in that race. I think it is useful, though I prefer to unit test things enough that I would be fine without it. But even without it, there may be times when I want to discard a result in a function that *does* return a value -- likely a different value. So there needs to be a way to distinguish statements that should return a value and those that do not. Best, David -- 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] PL/pgSQL PERFORM with CTE
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 3:05 PM, Pavel Stehule pavel.steh...@gmail.com wrote: When you would to ignore result, then you should to use a PERFORM - actually, it is limited now and should be fixed. Have no problem with it. Glad to have you on board. :-) I don't would to enable a free unbound statement that returns result. I have no pony in that race. I think it is useful, though I prefer to unit test things enough that I would be fine without it. But even without it, there may be times when I want to discard a result in a function that *does* return a value -- likely a different value. So there needs to be a way to distinguish statements that should return a value and those that do not. can you show some examples, please Pavel Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 20, 2013, at 3:18 PM, Pavel Stehule pavel.steh...@gmail.com wrote: can you show some examples, please This is not dissimilar to what I am actually doing: CREATE TABLE foo (id SERIAL PRIMARY KEY, name TEXT); CREATE OR REPLACE FUNCTION shipit ( VARIADIC things TEXT[] ) RETURNS BOOL LANGUAGE plpgsql AS $$ BEGIN WITH inserted AS ( INSERT INTO foo (name) SELECT * FROM unnest(things) RETURNING id ) PERFORM pg_notify( 'inserted ids', ARRAY(SELECT * FROM inserted)::text ); RETURN FOUND; END; $$; Only I am using a dummy row variable instead of PERFORM, of course. Best, David -- 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] PL/pgSQL PERFORM with CTE
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 3:18 PM, Pavel Stehule pavel.steh...@gmail.com wrote: can you show some examples, please This is not dissimilar to what I am actually doing: CREATE TABLE foo (id SERIAL PRIMARY KEY, name TEXT); CREATE OR REPLACE FUNCTION shipit ( VARIADIC things TEXT[] ) RETURNS BOOL LANGUAGE plpgsql AS $$ BEGIN WITH inserted AS ( INSERT INTO foo (name) SELECT * FROM unnest(things) RETURNING id ) PERFORM pg_notify( 'inserted ids', ARRAY(SELECT * FROM inserted)::text ); RETURN FOUND; END; $$; Only I am using a dummy row variable instead of PERFORM, of course. pg_notify returns void, so there are no necessary casting to void so enhanced check - so all returned columns are void should be enough Regards Pavel Best, David
Re: [HACKERS] PL/pgSQL PERFORM with CTE
On Aug 20, 2013, at 3:38 PM, Pavel Stehule pavel.steh...@gmail.com wrote: pg_notify returns void, so there are no necessary casting to void so enhanced check - so all returned columns are void should be enough What if I call another function I wrote myself that returns an INT, but I do not care about the INT? Maybe that function does the insert and returns the number of inserted rows. I can think of all kinds of reasons this might be the case; whether they are good or bad approaches is immaterial: sometimes you work with what you have. I am find with PERFORM to determine when a query's results should be discarded. I just think it needs to cover a few more cases. Best, David -- 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] PL/pgSQL PERFORM with CTE
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 3:38 PM, Pavel Stehule pavel.steh...@gmail.com wrote: pg_notify returns void, so there are no necessary casting to void so enhanced check - so all returned columns are void should be enough What if I call another function I wrote myself that returns an INT, but I do not care about the INT? Maybe that function does the insert and returns the number of inserted rows. I can think of all kinds of reasons this might be the case; whether they are good or bad approaches is immaterial: sometimes you work with what you have. I am find with PERFORM to determine when a query's results should be discarded. I just think it needs to cover a few more cases. yes I understand. I'll look, how PERFORM can be fixed Regards Pavel Best, David
