Skip site navigation (1) Skip section navigation (2)

block transactions in stored procedures

From: Ghiz x <mrghiz(at)yahoo(dot)it>
To: pgsql-novice(at)postgresql(dot)org
Subject: block transactions in stored procedures
Date: 2006-01-16 13:04:44
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
 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;
 block transaction within a plperl stored procedure
 Any hint or reference to tutorials about this issue would be really appreciated.

Yahoo! Mail: gratis 1GB per i messaggi, antispam, antivirus, POP3


pgsql-novice by date

Next:From: Andreas KretschmerDate: 2006-01-16 16:46:08
Subject: Re: Help with query
Previous:From: Christian HofmannDate: 2006-01-16 12:56:03
Subject: Re: Help with query

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group