Re: INSERT question

>>>>> "Brian" == Brian <Brian(at)McSweeney(dot)iol(dot)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(at)rlenter(dot)com 76-15 113th Street, Apt 3B
roland(at)astrofoto(dot)org Forest Hills, NY 11375