Re: Wishlist?

Chris Browne wrote:
> kleptog(at)svana(dot)org (Martijn van Oosterhout) writes:
>
>>On Mon, Jul 25, 2005 at 11:35:14AM +1000, Ezequiel Tolnay wrote:
>>
>>>Functions are not the same as stored procedures, but since PG lacks
>>>stored procedures, there is a necessity to use functions instead.
>>
>>Ok, maybe I'm missing something, but the only difference between a
>>procedure and a function is that a function returns a value and a
>>procedure doesn't. There's no difference in capabilities so I don't
>>really understand the problem here.
>
> I'm in the same boat here. I don't grasp what is the *vital*
> difference that makes a stored function, which differs from a stored
> procedure in that it returns a value, so greatly different.

The difference is quite simple, but perhaps not evident if you don't
have much experience with other RDBMSs. A function is meant to return a
result (or a set of results) of a predefined type during execution,
whilst a stored procedure returns any number of results of arbitrary
types (as well as status messages and affected rows per operation)
"during" execution, just as what you would expect by running a script
asynchronously, fetching results as the script's processed. The
convenience of a stored procedure is in short that you can store a
script (procedure), assign a name to it, thus hiding its internals
conveniently from the interface layer. The interface, of course, on turn
must be capable of handling the various results returned, if any at all.

For instance, when running a procedure that you know will take a few
hours to process, you could return every minute or so a status message
to know what's going on. Or return in one go customer details plus
transactions plus a summary. Or launch in the background without waiting
for a results at all (which requires EXECUTE as opposed to SELECT). Or
to have a feedback of the rows affected in the various steps of the
procedure to finally receive a rowset with the results.

Cheers,

Ezequiel Tolnay