Re: "stored procedures"

On Thu, Apr 21, 2011 at 1:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 3. What sort of primitive operations do you expect the SP to be
> able to execute "outside a transaction"?  The plpgsql model where
> all the primitive operations are really SQL ain't gonna work.
> I'm less sure what to do about #3.  The most attractive approach would
> probably be to make people use a non-SQL script interpreter --- perl,
> python, or whatever floats your boat --- which would likely mean that
> we have not just one SP implementation language but N of them.  But
> we've solved that problem before.

Does this mean you do or don't expect plpgsql to be able to run as
procedure? Should SPI based routines generally be able to run as a
procedure (I hope so)? If so, what API enhancements would be needed?
(I was thinking, SPI_is_proc, or something like that). I'd like to
see plpgsql work as much as possible as it does now, except obviously
you can't have exception handlers.

What about cancelling? Cancel the current running query, or the whole
procedure (I'm assuming the latter? How would that work?

> Calling another SP ... particularly one with a different implementation
> language ... could be a bit tricky too.  The above proposal assumes that
> SPs are always entered outside a transaction, but do we want to make
> that same restriction for the call-another-SP case?  And if not, how's
> it going to work?  Again, you'll have to be inside a transaction at
> least long enough to get the SP's definition out of the catalogs.

This restriction (no transaction only CALL) is ok I think. You can
always code up a function otherwise.

merlin