Re: PL/pgSQL PERFORM with CTE

On Aug 27, 2013, at 3:10 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> 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.

I am not familiar with procedures, being a long time Postgres guy, but you’re right that it never occurred to me that they be thought of as batch files.

Still, this is PL/pgSQL we’re talking about, not TSQL or SQL/PSM anything else. Perhaps your syntax suggestions make sense there, in which case, when you develop such functionality to Postgres, you would need to figure out how to get PERFORM to work with CTEs. But PL/pgSQL requires an explicit key word to return data, and I am hard pressed to see why that would change when it is used in procedures. And that makes PERFORM unnecessary, IME.

> 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).

Consistent, yes. But I’m not convinced -- and I’m *certainly* not convinced that PERFORM should be required to discard query results in PL/pgSQL *functions*, which is the issue on the table now.

Best,

David