Re: how to determine OID of the row I just inserted???

> 2/6/2003 6:08:17 AM, "Jules Alberts" <jules(dot)alberts(at)arbodienst-limburg(dot)nl> wrote:
> >Here's a question I have asked some time ago and Google tells me I'm
> >not the only one with this problem, but I haven't found a solution yet
> >:-(. I have a setup like this:
> >
> > customer {id serial, name varchar, address bigint}
> > person {id serial, name varchar, address bigint}
> > address {id serial, street varchar}
> >
> > customer.address points to address.id
> > person.address points to address.id

i finally figured out the the serial datatype is really an int4
(with auto-incrementing insert as default):

\d team
Table "team"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('"_track_id_seq"'::text)
<snip>

they're int (int4) not int8. at least, on my debian box, that is.

assuming your address table's id is also auto-incremented using
the nextval() function, then here's what you can do --

insert into address(
street
)values(
'Penny Lane'
);
-- now "currval" is available for the id, this session

update
customer
set
address = currval('address_id_seq')
where
id = $yadayada;

===

but here's a question for you -- does each location exist
independelty of who it's a location for? and can customers or
persons not have more than one address? (person->work, home,
vacation, etc; customer->east, hongKong, downtown, etc)

maybe you should have a person.id or customer.id field in your
address table:

create table address_type(
id serial,
name varchar(20),
seq smallint, -- for ordering choices on an interface "menu"
primary key ( id )
);
insert into address_type(name,seq)values('Home',10);
insert into address_type(name,seq)values('Office',20);
insert into address_type(name,seq)values('Friend',100);
insert into address_type(name,seq)values('Family',110);
insert into address_type(name,seq)values('Vacation',200);

create table address(
id serial,
person int4 references person ( id ),
address_type int4 references address_type ( id ),
street varchar(40),
primary key ( id )
);

then for a particular person,

insert into address(person,address_type,street)
select
p.id,
t.id,
'Penny Lane'
from
person p,
address_type t
where
p.lname = 'Pfingston'
-- or whatever you need to specify the ONE person
and
t.name = 'Home'
;

then join address.person to person.id, instead of the other
way 'round.

===

one more note -- having perused "database design for mere
mortals" i agree it's important to separate the subjects from
their attributes...

is "address" really an appropriate name for a table? it's a bit
ambiguous, that term -- can mean "number-and-street" or
"everything needed to get the delivery taken care of", but we
take it to mean the street portion of a location spec:

329 main street <== address
suote 701 <== suite
centralburg <== city
idaho <== province/state
87654 <== postal code
usa <== country code

329 main street <== address
<== suite
galt's gulch <== city
colorado <== province/state
77665 <== postal code
usa <== country code

these are locations, right?

for any particular geographic location, we consider an address to
be one portion of the total picture. we call ours "location" of
which "address" is a field.

just a thought...

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !