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