DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:x-enigmail-version:content-type:content-transfer-encoding;
	b=i69auGzPjJSK4nTRt6Gj46ADCEgqKKquedhqYkTWp/2K76HEpxFd7CT1MfcXPExAsJ
	+9+X8CzTyVJfoHAOg0+2JXipwZcR99sSBHnE+rCU5rlgtX5fuTGUGd8qFY+Lr/mOaizB
	XN4ZBmNdhiti9VA2GDvt+03TlVlFDCOed5+W0=
Message-ID: <4D3E9A25.2050409@gmail.com>
Date: Tue, 25 Jan 2011 10:38:45 +0100
From: Adelo <adelo.herrero@gmail.com>
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
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

 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 (