Zeoslib Portal

(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 ?

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

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?

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!

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.