Re: PL/pgSQL PERFORM with CTE

2013/8/28 Hannu Krosing <hannu(at)2ndquadrant(dot)com>

> On 08/28/2013 12:10 AM, Pavel Stehule wrote:
> >
> >
> > 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
> And you are perfectly ok to discard the results
> Actually it would be much more helpful to have
> "discard the results" syntax from client side, as
> in this case they take up network resources.
> >
> > 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.
> And you are perfectly ok to discard the results here as well
>

sure, depends how would to take a definition of procedure. Procedure is a
classic procedure in PL/SQL - based on ADA procedures , or more like batch
in T-SQL based on Sybase research, or some between in PSM in DB2. Every
design has some advantage and disadvantage. But hardly to say what is a
perfect design. I like a PL/SQL, but a procedures design (transaction
control) is more obscure, than in T-SQL. DB2 procedures can use parameter
list and can returns a status - it is a third design.

I don't propose procedures like syntactic sugar for current PostgreSQL
behave. If we can support procedures one times, then we should to get a new
functionality, that is not possible (or not simple possible) now.

>
> In a function I do expect the result from select but I also
> expect that I can silently ignore the result.
>

> > 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).
> So you can ignore the result in a procedure (by just skipping / not
> assigning it on client) but not in a function ?
>

SQL function that is called from SELECT statement should to return only one
result - without any side effect. It is a very good example, how clean and
simple is using PostgreSQL functions that returns scalar or table, and how
less clean and user friendly is usage functions that returns refcursors. I
like a PostgreSQL design, that use a explicit or implicit transaction for
every SELECT statement - and every function evaluation. It is simple, it is
clean, and it is significant limit for some usage, where we can work more
complexly with transactions. We must to break some code to more
cliend-server calls. T-SQL is strict in this area, and disallow any side
effect.

>
> Can you point out some other languages which *require* you
> to store the result of a function call or have a special syntax/keyword
> when you do not want to store it ?
>

ADA is very strict about it.

Regards

Pavel

>
> Cheer
>
>
>
>
>
>
>
>
>
> --
> Hannu Krosing
> PostgreSQL Consultant
> Performance, Scalability and High Availability
> 2ndQuadrant Nordic OÜ
>
>