Re: SQL-Invoked Procedures for 8.1

On Thu, 23 Sep 2004, Tom Lane wrote:

> > This makes the difference between procedures and functions quite
> > superficial: procedures are functions which return void and have parameter
> > modes.
>
> If you implement it that way I think it'll be very largely a waste of
> effort :-(. What you're talking about seems mere syntactic sugar and
> not a fundamental advance in capability.
>
> What I'd like to see is a "procedure" capability which is somehow
> outside the transaction system and can therefore invoke BEGIN, COMMIT,
> SAVEPOINT, etc. I have no immediate ideas about how to do this, but
> I think that's what people are really after when they ask for
> server-side procedures. They want to be able, for example, to have
> a procedure encapsulate an abort-and-retry loop around a serializable
> transaction. (It'd be great if we could do that in a function, but
> I haven't thought of a way to make it work.)

I made no reference to this in my previous email but I certainly agree
that SPs do not give people anything more than a function if they don't
have transaction management. I think your idea, mentioned else where,
about startup being in its own txn and then calling the SP outside of a
txn may work (with some modification to some areas of the code). There are
still other cases, like functions calling SPs and SPs calling SPs which
potentially provide some messy issues. Neil and I are going to try and
work out which cases exist and then see how we can adapt the code or SPs
to handle them.

>
> I concur with Grant Finnemore's objection as well: people expect
> procedures to be able to return resultsets, ie SETOF something,
> not only scalar values. Whether this is what SQL2003 says is not
> really the issue -- we have to look at what's out there in competing
> products.

Agreed. Any suggestions about how a SETOF OUT parameter would be accessed
by the client? We have a variety of options: returning the results as if
it was a normal SELECT; returning some kind of delimited string and
providing an API to scroll it on the client side? There's got to be
something better than that :-).

Gavin