Re: PL/pgSQL PERFORM with CTE - Mailing list pgsql-hackers

On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule <pavel.stehule@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