Re: 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(dot)stehule(at)gmail(dot)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Ü