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

<david@andl.org> writes:
>> From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
>> Since there are no autonomous transactions in PostgreSQL, how can you have
>> BEGIN and COMMIT called from the code that is invoked by "SELECT
>> my_andl_function()"?

> 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.

            regards, tom lane