Re: returning multiple result sets from a stored procedure

On Thu, Sep 9, 2010 at 4:17 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2010/9/9 Darren Duncan <darren(at)darrenduncan(dot)net>:
>> Pavel Stehule wrote:
>>>
>>> there are lot of questions - and I am not sure if procedures
>>> implementation can be done in one release cycle. The basic questions:
>>>
>>> * should be special catalog for procedures or we will use pg_proc?
>>> * how can be implemented OUT variables - the original implementation
>>> is simple - it's just pointer, but it's not directly possible inside
>>> postgres, because we use a MemoryContexts?
>>> * how can be implement a CALL statement - as plan statement or as command?
>>> * how can be implemented variables inside psql console, if we allows them?
>>> * how can be implement an overloading of procedures - can we use for
>>> selection OUT variables too?
>>> * what is procedure? It's like void function, or it can return status
>>> code like procedures in SQL/PSM (DB2)?
>>>
>>> --- As long years a stored procedures developer, I can say, so just
>>> minimal implementation of procedures can help with writing little bit
>>> more readable code for functions that return more then one scalar
>>> result. But other features can be nice too - explicit transaction
>>> control and unbind selects. But these features are killing gun.
>>
>> I've often considered that the main distinction between a function and a
>> procedure is that the former is intended to be invoked as a value-resulting
>> expression while the latter is intended to be invoked as a
>> non-value-resulting statement.  The SQL standard uses separate FUNCTION and
>> PROCEDURE for these.
>>
>> Since Pg's FUNCTION already seems to take on both roles, so overloading the
>> meaning of the FUNCTION keyword, like what a C function or a Perl sub does,
>> where returning VOID means procedure, then what is being added by a distinct
>> PROCEDURE?  Or is the VOID-returning FUNCTION going to be deprecated or
>> discouraged at the same time?
>>
>
> the overloading of function is based only on input parameters -
> because there are not entered output variables - it is just some
> record. But overloading of procedures, can be based on input and
> output variables.
>
> so I can to write
>
> CREATE PROCEDURE foo(OUT a int)
> ...
>
> and
> CREATE PROCEDURE foo(OUT a varchar)
> ...
>
> and then when I use a statement CALL is correct procedure selected
>
> CALL foo(textvariable)

That seems like a lot of complexity for no real benefit, to me.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company