Re: PL/pgSQL PERFORM with CTE

2013/8/27 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 27, 2013, at 1:36 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>
>