insert statements

[teroy]

Hey there if i have the following
insert into users (username) values ('troy') ;

and then straight after that i want to do
insert into customer (name,user_id) values ('troy','$id') ;

how do you get the id of the first insert statement (assuming we have a serial primary key) without doing a select statement in between. I mean the way i've bein doing it is gettin the last record in the table. But that is not safe.

Anyone have any better ways?

Cheers

Troy

 

[SteveBrett]

each serial has a corresponding sequence from which it determines the next value.

there are various ways you can get the info using statements such as

select currval('sequence_name') // gets current value
select nextval('sequence name') // gets next value

i should image you'd want to wrap it all in a transaction block.

have a look at the docs etc and alos at the programminf languages available for use with postgres that will allow you to better handle variables etc.

Steve