-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 The manual is correct, you can not do transactions within a procedure since the procedure must be called within a transaction. If you're working with postgres 8, you can achieve similar functionality using checkpoints. But that won't solve the problem you have below.
The solution to your problem is locking (or concurrency control if you prefer). While we're at it, we might as well optimize your statement a little too using ORDER BY with LIMIT instead of min(). SELECT id INTO _contacto_id FROM contactos WHERE contactos.operadora_id IS NULL AND contactos.actividad_id > = _actividad_id ORDER BY id LIMIT 1 FOR UPDATE; Take a look at the "FOR UPDATE" section of the SELECT description for an explanation of how this works. http://www.postgresql.org/docs/8.0/static/sql-select.html If you still have questions, then you might want to take a look at the concurrency control section of the manual. http://www.postgresql.org/docs/8.0/static/mvcc.html - -- Andrew Hammond 416-673-4138 [EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A Rafa Couto wrote: > I have got a plpgsql function: > > CREATE FUNCTION nueva_llamada(integer, integer) RETURNS integer > > as > > DECLARE > _operadora_id ALIAS FOR $1; > _actividad_id ALIAS FOR $2; > _contacto_id integer; > > BEGIN > > -- BEGIN; > > SELECT min(id) INTO _contacto_id FROM contactos > WHERE contactos.operadora_id IS NULL AND contactos.actividad_id > = _actividad_id; > > UPDATE contactos SET operadora_id = _operadora_id WHERE id = _contacto_id; > > -- COMMIT; > > INSERT INTO llamadas (contacto_id, operadora_id, fecha) > VALUES (_contacto_id, _operadora_id, now()); > > RETURN _contacto_id; > END > > and it works right, but I need atomic execution from --BEGIN and > --COMMIT, and manual says it is not possible to have transactions in > PL/pgSQL procedures :-( > > May be with LOCK TABLE? > > > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFCjiRXgfzn5SevSpoRAlZRAJ4pg7UohNBy+RhgoOfbqy0W9wbIXQCff6F1 VEPjPfo4tSxn+kMg6snBbSI= =bzri -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
