Re: [PERL DBI] Insertiing data across multiple tables

[SCassidy@xxxxxxxxxxxxxxxxxxx]

pgsql-general-owner@xxxxxxxxxxxxxx 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/
----------------------------------------------------