"returning" in postgresql request
Hi,
I try to deploy an Application with Oracle Database to a solution with postgresql.
the Oracle system exists and we use a request which return an int in a variable nb by "returning nb_lock into nb"
UPDATE xdb_ancestors_lock SET nb_lock=nb_lock+1 WHERE doc_id=? AND ele_id=? returning nb_lock INTO nb;
I'd like to know if there is equivalent solution under postgresql or if i'm obliged to do a select before my update.
Best regards,
Nico
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.
GIROIRE, Nicolas (COFRAMI) wrote:
Hi,
I try to deploy an Application with Oracle Database to a solution
with postgresql. the Oracle system exists and we use a request which
return an int in a variable nb by "returning nb_lock into nb"UPDATE xdb_ancestors_lock SET nb_lock=nb_lock+1 WHERE doc_id=? AND
ele_id=? returning nb_lock INTO nb;I'd like to know if there is equivalent solution under postgresql or
if i'm obliged to do a select before my update.
I think something similar has been discussed, but there's nothing like
it at the moment. You'll have to SELECT FOR UPDATE; UPDATE
--
Richard Huxton
Archonet Ltd
Am Donnerstag, den 09.06.2005, 16:30 +0200 schrieb GIROIRE, Nicolas
(COFRAMI):
Hi,
I try to deploy an Application with Oracle Database to a solution with postgresql.
the Oracle system exists and we use a request which return an int in a variable nb by "returning nb_lock into nb"UPDATE xdb_ancestors_lock SET nb_lock=nb_lock+1 WHERE doc_id=? AND ele_id=? returning nb_lock INTO nb;
I'd like to know if there is equivalent solution under postgresql or if i'm obliged to do a select before my update.
Best regards,
Nico
Looks like you really want:
UPDATE xdb_ancestors_lock SET nb_lock=nextval('nb_lock_sequence') WHERE
doc_id=? AND ele_id=?;
SELECT currval('nb_lock_sequence');
if you created a sequence and want assign just another free key.
If not, you need SELECT ... FOR UPDATE instead.
--
Tino Wildenhain <tino@wildenhain.de>
deploy an Application with Oracle Database to a solution with postgresql.
...
UPDATE xdb_ancestors_lock SET nb_lock=nb_lock+1 WHERE doc_id=? AND >
ele_id=? returning nb_lock INTO nb;
Looks like you really want:
UPDATE xdb_ancestors_lock SET nb_lock=nextval('nb_lock_sequence') WHERE
doc_id=? AND ele_id=?;
SELECT currval('nb_lock_sequence');
We have similar code in our Oracle-but-hopefully-soon-to-be-PostgreSQL
apps. However, in our case the sequence generator is used in an insert
trigger to populate a column. So, although I could use "currval" after
the insert to see what the trigger used, that would force the currval-
invoking code to know the internals of the insert trigger. This is a
bit of an abstraction violation, I think.