From: Roland Roberts <roland@astrofoto.org>
To: pgsql-sql@postgresql.org
Subject: Re: INSERT question
Date: 15 Nov 2001 11:11:30 -0500
Message-ID: <m2snbgggjx.fsf@tycho.rlent.pnet> (raw)
In-Reply-To: <1f5f2b44.0111130130.17e8e57a@posting.google.com>
References: <1f5f2b44.0111130130.17e8e57a@posting.google.com>
>>>>> "Brian" == Brian <Brian@McSweeney.iol.ie> writes:
Brian> To insert values into the child table corresponding to an
Brian> entry in the parent table, how do I get a reference to the
Brian> serial primary key (so as I can reference it for the
Brian> foreign key entry)
To do this in a transaction-safe manner you need to explicitly select
the "parent" row and get it's primary key. That, of course, can be
tricky.
The way I've usually seen this done is to explicitly pull a value from
the sequence and use it for both inserts, e.g.,
select nextval('sequence_name');
insert into foo values ($seqno, ...);
insert into bar values ($seqno, ...);
The above is sketchy because I don't know how you are accessing the
database. If you are using Perl or Tcl or somesuch, you will need to
save the value from the select and use in the inserts.
Brian> Hope you understand what I mean. This should be a regular
Brian> occurance and seeing as I'm not an sql guru, I just don't
Brian> have a clue!
One question here: when you say "parent" and "child" are you referring
to derived tables or are you just trying to describe the foreign key
constraint? PostgreSQL has "child" tables in the sense of
create table foo ( a serial );
create table bar ( b varchar(32) ) inherits (foo);
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: roland@astrofoto.org
Subject: Re: INSERT question
In-Reply-To: <m2snbgggjx.fsf@tycho.rlent.pnet>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox