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

> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> > I really don't know. But I have code that does this (no explicit BEGIN):
>
> > SELECT * FROM COMPILE($$
> > V6 := {{ abo:=true, abi:=b'DEADBEEF', anu:=123456789.987654321,
> ate:='abcdef', ati:=t'2015-12-31 23:59:58.9999' },
> > { abo:=false, abi:=b'DEADBEEF', anu:=987654321.123456789,
> > ate:='ghijklmno', ati:=t'2016-12-31 23:59:58.9999' }}
> > V6
> > $$);
>
> > And the generated code (which executes without error):
>
> > BEGIN;
> > DROP TABLE IF EXISTS "V6" ;
> > CREATE TABLE "V6" ( "abo" BOOLEAN, "abi" BYTEA, "anu" NUMERIC, "ate"
> > TEXT, "ati" TIMESTAMP, UNIQUE ( "abo", "abi", "anu", "ate", "ati" ) );
> > COMMIT; INSERT INTO "V6" ( "abo", "abi", "anu", "ate", "ati" ) VALUES
> > ( $1, $2, $3, $4, $5 ); SELECT "abo", "abi", "anu", "ate", "ati" FROM
> > "V6";
>
> Define "executes". You could shove those lines in via the wire protocol,
> sure, but SPI won't take them.

Now you really have me puzzled. What I provided is an extract from the log
of generated SQL commands sent to the SPI interface. [Obviously there also
values bound to the parameters which do not show up here.]

The code executes without error and produces exactly the output I expected.

I'll do some more checking to see if I missed something, but so far it just
works.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org