Re: returning multiple result sets from a stored procedure

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)

Regards

Pavel

> -- Darren Duncan
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>