Обсуждение: [PERL DBI] Insertiing data across multiple tables
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
-thanks
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