Re: Control reached end of trigger procedure without RETURN

 El 24/01/2011 19:19, Andrej escribió:

> 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
--
IF (TG_OP =DELETE') THEN
-- Descontar valor old..
val_neto:=1 * OLD.total_neto;
val_imp:=1 * OLD.valor_imp;
RAISE DEBUG 'Deleted: net % tax % ', val_neto, val_imp;

ELSIF (TG_OP =UPDATE') THEN
-- If you change the tax, substract the amount value.
IF ((OLD.por_impto !=EW.por_impto) AND (OLD.por_impto IS NOT NULL) AND
(NEW.ventas_cab_id IS NOT NULL)) THEN
RAISE DEBUG '--';
-- Substract the od value.
val_neto:=1 * OLD.total_neto;
val_imp:=1 * OLD.valor_imp;
UPDATE  ventas_imp
SET  suma_neto=ma_neto+val_neto,
suma_imp=ma_imp+val_imp,
total_con_imp=und(suma_neto+suma_imp)
WHERE ventas_cab_id=W.ventas_cab_id AND
por_impto=D.por_impto;
RAISE DEBUG ' --  ';
END IF;
-- Substract old value and add new value.
val_neto:=W.total_neto-OLD.total_neto;
val_imp:=W.valor_imp-OLD.valor_imp;
RAISE DEBUG 'Modify: net % tax % ', val_neto, val_imp;
ELSIF (TG_OP =INSERT') THEN
-- Add NEW value.
val_neto:=W.total_neto;
val_imp:=W.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=ma_neto + val_neto,
valor_imp=lor_imp + val_imp,
total_con_imp=und(suma_neto + valor_imp, 2)
WHERE ventas_cab_id=W.ventas_cab_id AND
por_impto=W.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,
valor_imp,
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 =% ', id_ventas_imp;
EXCEPTION  -- Two transactions trying to create the record.
WHEN UNIQUE_VIOLATION THEN
UPDATE  ventas_imp
SET suma_neto=ma_neto + val_neto,
valor_imp=lor_imp + val_imp,
total_con_imp=und(suma_neto + valor_imp, 2)
WHERE ventas_cab_id=W.ventas_cab_id AND
por_impto=W.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=
DELETE FROM ventas_imp
WHERE  ventas_cab_id=_ventas_cab AND
(total_con_imp=OR total_con_imp IS NULL);

RAISE DEBUG 'Delete records with amount=or null ';
-- I save the record_id of table ventas_imp and return the updated record.
NEW.total_con_imp :=ound(total_neto + valor_imp, 2);     -- Update
calculations.
NEW.ventas_imp_id :=d_ventas_imp;
RAISE DEBUG 'New of record values: Total with tax = Record Id of tax
table=, 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 (