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

[MrCBofBCinTX]

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?

 

[feherke]

Hi

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 [tt]sequence[/tt] value "manually". Something like this :

newid[teal]=[/teal]db[teal].[/teal][COLOR=darkgoldenrod]execQuery[/color][teal]([/teal][green][i]"select nextval('main_id_seq')"[/i][/green][teal]).[/teal][COLOR=darkgoldenrod]getField[/color][teal]([/teal][green][i]"nextval"[/i][/green][teal])[/teal]

db[teal].[/teal][COLOR=darkgoldenrod]execQuery[/color][teal]([/teal][green][i]"insert into main (id,foo) values ("[/i][/green][teal]+[/teal]newid[teal]+[/teal][green][i]","[/i][/green][teal]+[/teal]foo[teal]+[/teal][green][i]")"[/i][/green][teal])[/teal]

db[teal].[/teal][COLOR=darkgoldenrod]execQuery[/color][teal]([/teal][green][i]"insert into sub (mainid,bar) values ("[/i][/green][teal]+[/teal]newid[teal]+[/teal][green][i]","[/i][/green][teal]+[/teal]bar[teal]+[/teal][green][i]")"[/i][/green][teal])[/teal]

Feherke.

http://rootshell.be/~feherke/

 

[MrCBofBCinTX]

Wow, that works beautifully!

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

Thanks

 

[TrojanWarBlade]

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.

 

[MrCBofBCinTX]

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!