Re: returning multiple result sets from a stored procedure

2010/9/9 Robert Haas <robertmhaas(at)gmail(dot)com>:
> 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.

no, you can to specify a expected result type - it's very for some
convert or import functions. So we expect so out procedures will
supports to OUT parameters, then implementation of this mechanism has
minimal overhead to current implementation. Just to add types of OUT
parameters to searching algorithm.

More - it is just consistent with overloading idea. Why the OUT
parameters should be removed from procedure parameters?

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