Re: [PERL DBI] Insertiing data across multiple tables

pgsql-general-owner(at)postgresql(dot)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/
----------------------------------------------------