Re: Stored procedures returning rowsets

Gregory Seidman wrote:
> Excapsulating business logic on the DB server seems to be the best reason
> I've heard. In fact, I am in the process of writing a large web application
> and, more and more, I find that I want to write server-side plpgsql
> functions to encapsulate transactions, simplifying the programmatic
> interaction with the database to SELECT Func(args, ...) for the most part,
> particularly for updates.
>
> It is not, however, clear to me the difference between a stored procedure
> which can be CALL'd and a function which must be SELECT'd. Can anyone
> explain why the distinction is important?

A SELECT'd function needs a predetermined row structure so the planner
can deal with column names of the output, joins to other row sources
(i.e. tables/views/table functions), and WHERE clause criteria. So you
must know what is to be returned (column names and types) at least in
time to specify it in the SELECT statement (ala the new anonymous
composite type just committed) Note this capability is new in the
upcoming 7.3.

A CALL'd stored procedure is stand-alone. It isn't combined with any
other row sources, you can't get just specific columns by name, and you
can't apply a WHERE clause. However, because of all these restrictions,
you also don't need to know the return row structure in advance. A
CALL'd stored procedure can produce a different structure given
different inputs, or even multiple resultsets with different structures
on one call. This can be useful (at least) while debugging your stored
proc. There are some front end report writers targeted at MS SQL Server
which can deal with these ambiguities. This capability has been
discussed, but is not planned for 7.3. And there is no agreement that it
will ever be implemented -- so if you're interested, make your voice
heard ;-).

Joe