Thread: block transactions in stored procedures

 I'd like to write a plperl  stored procedure that inserts data collected from a web form into multiple tables related by means of foreign keys for integral reference's sake.

Each table uses an independent sequence as primary key.

For instance the db consists of tableA and tableB; tableA's primary key is referenced as a foreign key
by one field of tableB.
In order to insert the tableA_id of the latest  inserted row into  the foreign key field referencing to it in tableB I'd retrieve tableA_id from tableA_id_seq  before inserting the new row in both tables as follows (ala plperl):

$sql='select nextval(\'indirizzo_indirizzo_id_seq\');';

$rv = spi_exec_query($sql);

$row = $rv->{rows}[0];

$tableA_id = $row->{nextval};

$sql=insert into tableA($tableA_id,...);

$rv = spi_exec_query($sql);

$sql=insert into tableB(DEFAULT,..,$tableA_id,...);

$rv = spi_exec_query($sql);

Reading through SPI (Server Programming Api) documentation, it seems SPI_execute() runs a single sql statement per call an I myself verified that
spi_exec_query('BEGIN WORK;SELECT nextval(\'indirizzo_indirizzo_id_seq\');COMMIT WORK;') doesn't work.

Apart from my specific problem, my questioning is on how does one enforce a BEGIN WORK;
......

COMMIT WORK;

block transaction within a plperl stored procedure


Any hint or reference to tutorials about this issue would be really appreciated.

Ghiz