(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 ?
Correct way to insert into two tables (in TZUpdateS
Moderators: gto, cipto_kh, EgonHugeist, olehs
-
- Senior Boarder
- Posts: 93
- Joined: 01.07.2009, 16:07
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
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
-
- Expert Boarder
- Posts: 164
- Joined: 18.03.2008, 13:03
- Contact:
Hello marcov,
there is nothing wrong with
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!
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'));
Good luck!