Re: How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

> From: Albe Laurenz [mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at]
>
> > I am attempting to create a new language implementation. The language
> > is Andl (andl.org) so the handler is plandl.
> > This is a question about executing SPI queries from inside plandl.
> >
> > The documentation makes it clear that SPI allows nested queries; that
> > in some instances it will be necessary to call SPI_push() and
> > SPI_pop(), but in others this will be handled automatically. Se
> http://www.postgresql.org/docs/9.5/interactive/spi-spi-push.html.
> >
> > It is an important design feature of plandl to allow nested queries.
> >
> > My question is: where are the transaction boundaries if the
> > inner/outer query do or do not contain BEGIN/ABORT/COMMIT? Do they
> > nest, or does an inner COMMIT finish a transaction started by an outer
> BEGIN, or is it ignored?
>
> You cannot have BEGIN or COMMIT inside a function.

Are you sure you meant it like that? I already have BEGIN/COMMIT inside a function and it works perfectly. If it did not, then it would be impossible to use BEGIN/COMMIT in any language handler, since every call to a language handler is a call to a function.

Did you mean 'inside a nested function'? Or something else?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org