×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Trying to add to two tables, but second needs new id from first PosSQL

Trying to add to two tables, but second needs new id from first PosSQL

Trying to add to two tables, but second needs new id from first PosSQL

(OP)
I'm trying to add a new customer to a PostgreSQL table, customers. Since many customers have multiple jobsite addresses, often different from billing addresses, I want to go ahead and force adding a jobsite set of info at same time.

Problem is, I have set up jobsite table to depend on foreign key integer cust_id, which I won't know until after successfully adding new customer, but then I have no way (that I can see) to get this new value automatically.

Am I missing something obvious here? Is there a way to get this newly created cust_id value during insert process to customers table?

RE: Trying to add to two tables, but second needs new id from first PosSQL

Hi

Quote (MrCBofBCinTX):

Is there a way to get this newly created cust_id value during insert process to customers table?
I not followed the new features in PostgreSQL, so I may be wrong.

In versions 7.x there was no such thing, so we used to get the next sequence value "manually". Something like this :

CODE --> pseudocode

newid=db.execQuery("select nextval('main_id_seq')").getField("nextval")

db.execQuery("insert into main (id,foo) values ("+newid+","+foo+")")

db.execQuery("insert into sub (mainid,bar) values ("+newid+","+bar+")")
 

Feherke.
http://rootshell.be/~feherke/

RE: Trying to add to two tables, but second needs new id from first PosSQL

(OP)
Wow, that works beautifully!

Works under psql and DBI.
Had to drop .getField("nextval") part.

Thanks

RE: Trying to add to two tables, but second needs new id from first PosSQL

Just a thought,

Since you want both tables updated you should probably consider some form of transaction processing so that if your code fails part way through you don't end up with an entry in one table without the other.

 

Trojan.

RE: Trying to add to two tables, but second needs new id from first PosSQL

(OP)
Yeah, that's probably a good idea until I can upgrade to Perfect OS and Flawless Hardware tm

Luckily, my programming is absolutely bug-free!!

Which reminds me to get a fresh, local copy of my pg_dump, which I just did!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close