Re: how to determine OID of the row I just inserted??? - Mailing list pgsql-general

> 2/6/2003 6:08:17 AM, "Jules Alberts" <jules.alberts@arbodienst-limburg.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@serensoft.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/ !