Message-ID: <4D3E9764.8050904@adinf.es>
Date: Tue, 25 Jan 2011 10:27:00 +0100
From: MoNiLlO <monillo@adinf.es>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; es-ES;
	rv:1.9.2.9) Gecko/20100915 Lightning/1.0b2 Thunderbird/3.1.4
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Control reached end of trigger procedure without RETURN
References: <397A0940-896A-4DEF-873A-907976E8400B@adinf.es>
	<AANLkTikiED_f2FVDSjtArnB-6eNsQhit1Nzv8W5i-6wD@mail.gmail.com>
In-Reply-To: <AANLkTikiED_f2FVDSjtArnB-6eNsQhit1Nzv8W5i-6wD@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

 El 24/01/2011 19:19, Andrej escribi=F3:
> And, since the list is in English - please translate?

I'm sorry, I started writing and the language ...  :-(

I have a function that is triggered in a trigger whose purpose is,
create a record in another table or update and return the id of the
record created / modified to save in the table from being called. Try to
explain it better:

TableA -> Launch the trigger and I want to save the modified record id /
created in Table B.
TableB -> Is a summary table of TableA and is updated from TableA trigger=
.

Purpose -> Update TableB from TableA and store in the record of TableA
the Id of record TableB.

When I launch one insert returns error:

I put the function and the returned error.

Thanks in advance,

Best regards.

***********
CODE
***********
CREATE OR REPLACE FUNCTION ventas_det_a_ventas_imp()
  RETURNS trigger AS
$BODY$
DECLARE
id_ventas_imp  integer; -- Taxes Id.
id_ventas_cab integer; -- Bill Id.
val_neto  numeric(19,4); -- net value.
val_imp  numeric(19,4); -- tax value.
BEGIN
--=20
IF (TG_OP =3D 'DELETE') THEN
-- Descontar valor old..
val_neto:=3D -1 * OLD.total_neto;
val_imp:=3D -1 * OLD.valor_imp;
RAISE DEBUG 'Deleted: net % tax % ', val_neto, val_imp;

ELSIF (TG_OP =3D 'UPDATE') THEN
-- If you change the tax, substract the amount value.
IF ((OLD.por_impto !=3D NEW.por_impto) AND (OLD.por_impto IS NOT NULL) AN=
D
(NEW.ventas_cab_id IS NOT NULL)) THEN
RAISE DEBUG '--';
-- Substract the od value.
val_neto:=3D -1 * OLD.total_neto;
val_imp:=3D -1 * OLD.valor_imp;
UPDATE  ventas_imp
SET  suma_neto=3Dsuma_neto+val_neto,
suma_imp=3Dsuma_imp+val_imp,
total_con_imp=3Dround(suma_neto+suma_imp)
WHERE ventas_cab_id=3DNEW.ventas_cab_id AND
por_impto=3DOLD.por_impto;
RAISE DEBUG ' --  ';
END IF;
-- Substract old value and add new value.
val_neto:=3DNEW.total_neto-OLD.total_neto;
val_imp:=3DNEW.valor_imp-OLD.valor_imp;
RAISE DEBUG 'Modify: net % tax % ', val_neto, val_imp;
ELSIF (TG_OP =3D 'INSERT') THEN
-- Add NEW value.
val_neto:=3DNEW.total_neto;
val_imp:=3DNEW.valor_imp;
RAISE INFO 'New: net % tax % ', val_neto, val_imp;
END IF;

-- update with new values and returning it the "id" of the updated record=
 .
UPDATE  ventas_imp
SET suma_neto=3Dsuma_neto + val_neto,
valor_imp=3Dvalor_imp + val_imp,
total_con_imp=3Dround(suma_neto + valor_imp, 2)
WHERE ventas_cab_id=3DNEW.ventas_cab_id AND
por_impto=3DNEW.por_impto
RETURNING ventas_imp_id INTO id_ventas_imp;
RAISE DEBUG 'Update done in record_id % ', id_ventas_imp;

-- If not found, insert new record and return "id".
IF (NOT FOUND) THEN
BEGIN -- .
INSERT INTO  ventas_imp (
ventas_cab_id,
por_impto,
suma_neto,=20
valor_imp,=20
total_con_imp)
VALUES  ( NEW.ventas_cab_id,
NEW.por_impto,
val_neto,
val_imp,
round(val_neto+val_imp, 2) )
RETURNING  ventas_imp_id INTO id_ventas_imp;
RAISE DEBUG 'Not found. New record =3D  % ', id_ventas_imp;
EXCEPTION  -- Two transactions trying to create the record.
WHEN UNIQUE_VIOLATION THEN
UPDATE  ventas_imp
SET suma_neto=3Dsuma_neto + val_neto,
valor_imp=3Dvalor_imp + val_imp,
total_con_imp=3Dround(suma_neto + valor_imp, 2)
WHERE ventas_cab_id=3DNEW.ventas_cab_id AND
por_impto=3DNEW.por_impto
RETURNING ventas_imp_id INTO id_ventas_imp;
RAISE DEBUG 'Two transactions trying to create the record: I launch
update in record_id % ', id_ventas_imp;
END;
END IF;

-- Delete records with amount=3D0.
DELETE FROM ventas_imp=20
WHERE  ventas_cab_id=3Did_ventas_cab AND=20
(total_con_imp=3D0 OR total_con_imp IS NULL);

RAISE DEBUG 'Delete records with amount=3D0 or null ';
-- I save the record_id of table ventas_imp and return the updated record=
.
NEW.total_con_imp :=3D round(total_neto + valor_imp, 2);     -- Update
calculations.
NEW.ventas_imp_id :=3D id_ventas_imp;
RAISE DEBUG 'New of record values: Total with tax =3D % Record Id of tax
table=3D ', NEW.total_con_imp, NEW.ventas_imp_id;
return NEW;
END

$BODY$
  LANGUAGE 'plpgsql' VOLATILE

##############################

INFO:  Nuevo: neto 5.0000 impuesto 0.5000
ERROR:  control reached end of trigger procedure without RETURN
CONTEXT:  PL/pgSQL function "ventas_imp_a_ventas_cab"
SQL statement "INSERT INTO ventas_imp ( ventas_cab_id, por_impto,
suma_neto, valor_imp, total_con_imp) VALUES (  $1 ,  $2 ,  $3 ,  $4 ,
round( $3 + $4 , 2) ) RETURNING ventas_imp_id"
PL/pgSQL function "ventas_det_a_ventas_imp" line 56 at SQL statement

********** Error **********

ERROR: control reached end of trigger procedure without RETURN
Estado SQL:2F005
Contexto:PL/pgSQL function "ventas_imp_a_ventas_cab"
SQL statement "INSERT INTO ventas_imp ( ventas_cab_id, por_impto,
suma_neto, valor_imp, total_con_imp) VALUES (  $1 ,  $2 ,  $3 ,  $4 ,
round( $3 + $4 , 2) ) RETURNING ventas_imp_id"
PL/pgSQL function "ventas_det_a_ventas_imp" line 56 at SQL statement

##############################


the #56 line is the INSERT instruction in the block

IF (NOT FOUND) THEN
BEGIN --
INSERT INTO  ventas_imp (