Postgres Pro
Downloads
Home   >   mailing lists

Thread: [PERL DBI] Insertiing data across multiple tables

[PERL DBI] Insertiing data across multiple tables

From
"JD Wong"
Date:
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

Re: [PERL DBI] Insertiing data across multiple tables

From
SCassidy@overlandstorage.com
Date:

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/
----------------------------------------------------

Re: [PERL DBI] Insertiing data across multiple tables

From
Klint Gore
Date:
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