X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id B849352839
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 20 May 2005 07:47:21 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 17293-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 20 May 2005 10:47:12 +0000 (GMT)
Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.199])
	by svr1.postgresql.org (Postfix) with ESMTP id A799652827
	for <pgsql-sql@postgresql.org>; Fri, 20 May 2005 07:47:11 -0300 (ADT)
Received: by rproxy.gmail.com with SMTP id i8so401881rne
	for <pgsql-sql@postgresql.org>; Fri, 20 May 2005 03:47:31 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=ofkKQbhO1f796qu4xsHR07AUBdJ4VKTlRRDrxtt+KFecjmvGnJuH6ajw3oMUkJB6mUbujfMv4gFarfwXHxm6DHMnnC5u6rz5SbMs5Oa4bi3/ROGFuY0WpoeUIkoAO2TbxvTPisSrPORyEpP63mZr7BG+ruSpGExLy8e7cOhuhec=
Received: by 10.38.101.64 with SMTP id y64mr1650783rnb;
	Fri, 20 May 2005 03:47:31 -0700 (PDT)
Received: by 10.38.79.24 with HTTP; Fri, 20 May 2005 03:47:31 -0700 (PDT)
Message-ID: <22df564b05052003479481da1@mail.gmail.com>
Date: Fri, 20 May 2005 12:47:31 +0200
From: Rafa Couto <rafacouto@gmail.com>
Reply-To: Rafa Couto <rafacouto@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Transaction in plpgslq
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.605 tagged_above=0 required=5
	tests=DNS_FROM_RFC_ABUSE, RCVD_BY_IP, UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200505/166
X-Sequence-Number: 21479

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=20
      WHERE contactos.operadora_id IS NULL AND contactos.actividad_id
=3D _actividad_id;

    UPDATE contactos SET operadora_id =3D _operadora_id WHERE id =3D _conta=
cto_id;

  -- COMMIT;

  INSERT INTO llamadas (contacto_id, operadora_id, fecha)=20
    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?



--=20
Rafa Couto (caligari)
mailto:rafacouto@gmail.com
urgentes (s=F3lo texto): rafa112@amena.com
PGP 0x30EC5C31 [E6BF 11EF FE55 38B1  CF7E 9380 58E5 9FA3]