[PERL DBI] Insertiing data across multiple tables

pgsql-general-owner@postgresql.org wrote on 07/24/2008 01:36:27 PM:

Does anybody know how to insert data over multiple tables
transactionally? The relationship is 1:1 with the latter table
having a foreign key constraint. In order to add data to Table2 I
need to know the primary key value of the same record in Table1
before it's committed, which in this case is a serial integer which
of course is regulated by an implicit index. Does anybody know how
to access a column's index in the Perl DBI?
-thanks

Table2 needs to have the foreign key constraint defined as INITIALLY
DEFERRED.

First, turn AutoCommit off (starts a transaction).
Then, get the nextval of the sequence used for the foreign key.
Insert master table row, using the key value.
Insert second table row, again using the key value.
Commit
Set AutoCommit back on, if the execution will continue, and you will need
it again.

I sometimes set up primary key columns like: "id integer PRIMARY KEY
DEFAULT nextval('tblname_seq'),
that way I have a sequence name with a meaningful name, easy to reference.

Susan

----------------------------------------------------
Tiered Data Protection Made Simple
http://www.overlandstorage.com/
----------------------------------------------------

JD Wong wrote:

Does anybody know how to insert data over multiple tables
transactionally? The relationship is 1:1 with the latter table having
a foreign key constraint. In order to add data to Table2 I need to
know the primary key value of the same record in Table1 before it's
committed, which in this case is a serial integer which of course is
regulated by an implicit index.

Have a look at INSERT ... RETURNING. It can feed back the new rows to
your application so that you can make your 2nd insert without having to
pre/reselect them (assuming your running 8.2+)

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au