Correct way to insert into two tables (in TZUpdateS

Forum related to PostgreSQL

Moderators: gto, cipto_kh, EgonHugeist, olehs

marcov
Senior Boarder
Senior Boarder
Posts: 94
Joined: 24.06.2010, 09:17

Correct way to insert into two tables (in TZUpdateS

Postby marcov » 07.07.2010, 10:29

(postgres 8, zeos 7,D2009)

I've a m x n relation between table A and B, and for this I use a table AB that contains references to both.

Now, for a certain tuple in A, I need to add a relation to B, so I need to insert an item into B and AB.

So I do

insert into B (datafield1,datafield2) values (value1,value2) ;
insert into AB (AID,BID) values (selectedAID, currval('B_id_seq'))

where selected AID is the ID of the selected A tuple, and I use currval to retrieve the value of B.id

However I assume this is unsafe since inbetween the two inserts a parallel query could update currval also?

What is the advised way of doing that (within the constraints of designtime TZQuery insertsql statement ?

guidoaerts
Senior Boarder
Senior Boarder
Posts: 93
Joined: 01.07.2009, 16:07

Postby guidoaerts » 08.07.2010, 13:39

insert into B(id, datafield1, datafield2) values (default,value1,value2) RETURNING id;

then use the value of id to insert into AB

example from postgresql manual:
"
Insert a single row into table distributors, returning the sequence number generated by the DEFAULT clause:

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
RETURNING did;

"
http://www.postgresql.org/docs/8.2/stat ... nsert.html

I know, zeos doesn't support it yet, but we have the same issue in Firebird, and I think it can be done, at least in zeos6.6.5.
http://zeos.firmos.at/viewtopic.php?t=2604

Guido

marcov
Senior Boarder
Senior Boarder
Posts: 94
Joined: 24.06.2010, 09:17

Postby marcov » 08.07.2010, 21:01

Yes, I found that, and understand that could be a manual way?

But how to use that returned "id" in the next statement in a TZUpdatesql insertsql. block ?

If not I assume the logical solution would be to make a stored procedure for this?

Wild_Pointer
Expert Boarder
Expert Boarder
Posts: 159
Joined: 18.03.2008, 13:03
Contact:

Postby Wild_Pointer » 09.07.2010, 07:03

Hello marcov,

there is nothing wrong with

Code: Select all

insert into B (datafield1,datafield2) values (value1,value2) ;
insert into AB (AID,BID) values (selectedAID, currval('B_id_seq'));

because according to postgresql manual currval returns "the value most recently obtained by nextval for this sequence in the current session.". So no one can interfere there. The only pitfall is the trigger. If you have a trigger that after insertion to B launches actions that lead to calling nextval('B_id_seq') then you will have problems with your approach. If not - no problem.

Good luck!

marcov
Senior Boarder
Senior Boarder
Posts: 94
Joined: 24.06.2010, 09:17

Postby marcov » 09.07.2010, 08:01

Wild Pointer: thanks that was the kind info I was looking for.

IOW make sure the transaction regime is sane, and it will be ok.


Return to “PostgreSQL”

Who is online

Users browsing this forum: No registered users and 1 guest