Re: procedures and plpgsql PERFORM

On Thu, Dec 14, 2017 at 9:40 PM, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>
>> On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> > Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> writes:
>> >> We allow a function to be invoked as part of PERFORM statement in
>> >> plpgsql
>> >> ...
>> >> But we do not allow a procedure to be invoked this way
>> >
>> >> Procedures fit that category and like functions, I think, we should
>> >> allow them be invoked directly without any quoting and CALL
>> >> decoration.
>> >
>> > How is that going to work? What if the procedure tries to commit the
>> > current transaction?
>> >
>> > IOW, this is not merely a syntactic-sugar question.
>>
>> BTW, We've already come to (near-but good enough) consensus that
>> PERFORM syntax is really just unnecessary, and I submitted a patch to
>> make it optional (which I really need to dust off and complete).
>
>
> Except right now PERFORM doesn't exist in SQL and is a pl/pgsql keyword to
> specify a specific limited form of the SQL SELECT command. CALL is an SQL
> command. I don't see any real upside to allowing pl/pgsql to accept
> omission of the command tag while SQL cannot - at least not without a
> use-case describe why such syntax would be beneficial. And likely those use
> cases would revolve around some looping variant as opposed to a single
> stand-alone, result-less, CALL.
>
> If we do keep "PERFORM" in the pl/pgsql vocab I'd consider the following
> enhancement:
> PERFORM func() => SELECT func()
> PERFORM proc() => CALL proc()

Right, that's what I am suggesting.

Furthermore the current error message is misleading:

do $$
begin perform dummy_proc(1); end; $$ language plpgsql;
ERROR: dummy_proc(integer) is a procedure
LINE 1: SELECT dummy_proc(1)
^
HINT: To call a procedure, use CALL.
QUERY: SELECT dummy_proc(1)
CONTEXT: PL/pgSQL function inline_code_block line 2 at PERFORM

The user never wrote SELECT dummy_proc(), it was injected by plpgsql.
Let's assume for a moment, that user infers that s/he has to use CALL
instead. Even then plpgsql doesn't support PERFORM CALL dummy_proc()
or CALL dummy_proc().

>
> I prefer Merlin's suggestion to just documenting that PERFORM is deprecated
> and works only with functions - and that to use procedures in pl/pgsql just
> use the normal SQL CALL command. And to write: "SELECT func()" to invoke
> functions, again just like one would in an SQL script.

That would simplify it, but I don't have any opinion as to whether we
should remove PERFORM or not.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company