pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedControl reached end of trigger procedure without RETURN
9+ messages / 5 participants
[nested] [flat]
* Control reached end of trigger procedure without RETURN
@ 2011-01-22 06:32 MoNiLlO <monillo@adinf.es>
2011-01-25 09:27 ` Re: Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
0 siblings, 1 reply; 9+ messages in thread
From: MoNiLlO @ 2011-01-22 06:32 UTC (permalink / raw)
To: pgsql-sql
Buenos días:
Tengo una función que se dispara en un trigger cuya finalidad es; crear un registro en otra tabla o actualizarlo y devolviendo el id del registro creado/modificado para guardarlo en la tabla desde la que se llama. Intentaré explicarlo mejor:
tablaA --> Es la que dispara el trigger y en la que quiero guardar el id del registro modificado / creado en la tabla B.
tablaB --> Digamos que es una tabla resumen de la tablaA y se mantiene desde el trigger de la misma, creando modificando y/o borrando los registros.
Finalidad --> Eso, actualizar la tablaB desde la A y guardar en el registro de de la tablaA a que registro de la tablaB "pertenece".
Cuando hago el insert me devuelve el error del asunto
Control reached end of trigger procedure without RETURN
y me indica la línea correspondiente al INSERT.
¿Acaso no continúa el flujo if (not found) y debería hacer el return NEW tras el insert? ¿y lo mismo en el update del exception?
Agradezco de antemano vuestro tiempo y ayudas.
Un saludo.
P.D. Estructura de la función.
DECLARE
id_tablaB integer; -- Id de la tabla B.
BEGIN
IF (TG_OP = 'DELETE') THEN
-- Descontar valores
ELSIF (TG_OP = 'UPDATE') THEN
-- Modificar valores.
ELSIF (TG_OP = 'INSERT') THEN
-- Asignar valores
END IF;
-- Update con nuevos valores y retornamos el id para actualizar detalle.
UPDATE tablaB
SET campo1=valor1,
campo2=valor2
WHERE tablaA_id=NEW.tblaB_id
RETURNING tablaB_id INTO id_tablaB;
-- Si no existe, hacemos el insert y retornamos el id para actualizar detalle.
IF (NOT FOUND) THEN
BEGIN -- No existÌa el registro y lo creamos.
--
-- AQUÍ DEVUELVEL EL ERROR.
--
INSERT INTO tablaB (
campo1,
campo2)
VALUES ( valor1,
valor2 )
RETURNING tablaB_id INTO id_tablaB;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
UPDATE tablaB
SET campo1=valor1,
campo2=valor2
WHERE tablaA_id=NEW.tblaB_id
RETURNING tablaB_id INTO id_tablaB;
END;
END IF;
-- Borrado de ventas_imp si el resumen del % y factura es 0.
DELETE FROM tablaB
WHERE tablaA_id=id_tablaA AND (campo3=0 OR campo3 IS NULL);
-- Guardamos el id de la tabla ventas_imp y retornamos el record modificado.
NEW.tablaB_id := id_tablaB
return NEW;
END
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Control reached end of trigger procedure without RETURN
2011-01-22 06:32 Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
@ 2011-01-25 09:27 ` MoNiLlO <monillo@adinf.es>
2011-01-25 15:06 ` Re: Control reached end of trigger procedure without RETURN Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: MoNiLlO @ 2011-01-25 09:27 UTC (permalink / raw)
To: pgsql-sql
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 != NEW.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=suma_neto+val_neto,
suma_imp=suma_imp+val_imp,
total_con_imp=round(suma_neto+suma_imp)
WHERE ventas_cab_id=NEW.ventas_cab_id AND
por_impto=OLD.por_impto;
RAISE DEBUG ' -- ';
END IF;
-- Substract old value and add new value.
val_neto:=NEW.total_neto-OLD.total_neto;
val_imp:=NEW.valor_imp-OLD.valor_imp;
RAISE DEBUG 'Modify: net % tax % ', val_neto, val_imp;
ELSIF (TG_OP = 'INSERT') THEN
-- Add NEW value.
val_neto:=NEW.total_neto;
val_imp:=NEW.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=suma_neto + val_neto,
valor_imp=valor_imp + val_imp,
total_con_imp=round(suma_neto + valor_imp, 2)
WHERE ventas_cab_id=NEW.ventas_cab_id AND
por_impto=NEW.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=suma_neto + val_neto,
valor_imp=valor_imp + val_imp,
total_con_imp=round(suma_neto + valor_imp, 2)
WHERE ventas_cab_id=NEW.ventas_cab_id AND
por_impto=NEW.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=0.
DELETE FROM ventas_imp
WHERE ventas_cab_id=id_ventas_cab AND
(total_con_imp=0 OR total_con_imp IS NULL);
RAISE DEBUG 'Delete records with amount=0 or null ';
-- I save the record_id of table ventas_imp and return the updated record.
NEW.total_con_imp := round(total_neto + valor_imp, 2); -- Update
calculations.
NEW.ventas_imp_id := id_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 (
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Control reached end of trigger procedure without RETURN
2011-01-22 06:32 Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
2011-01-25 09:27 ` Re: Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
@ 2011-01-25 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-25 15:21 ` Re: Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
2011-01-25 15:22 ` Re: Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
2011-01-25 15:24 ` Re: Control reached end of trigger procedure without RETURN Adelo <adelo.herrero@gmail.com>
0 siblings, 3 replies; 9+ messages in thread
From: Tom Lane @ 2011-01-25 15:06 UTC (permalink / raw)
To: MoNiLlO <monillo@adinf.es>; +Cc: pgsql-sql
MoNiLlO <monillo@adinf.es> writes:
> When I launch one insert returns error:
> I put the function and the returned error.
> CREATE OR REPLACE FUNCTION ventas_det_a_ventas_imp()
> RETURNS trigger AS
> ...
> ERROR: control reached end of trigger procedure without RETURN
> CONTEXT: PL/pgSQL function "ventas_imp_a_ventas_cab"
The function that's lacking a RETURN is not the one you're showing us.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Control reached end of trigger procedure without RETURN
2011-01-22 06:32 Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
2011-01-25 09:27 ` Re: Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
2011-01-25 15:06 ` Re: Control reached end of trigger procedure without RETURN Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-01-25 15:21 ` MoNiLlO <monillo@adinf.es>
2 siblings, 0 replies; 9+ messages in thread
From: MoNiLlO @ 2011-01-25 15:21 UTC (permalink / raw)
To: pgsql-sql
El 25/01/2011 16:06, Tom Lane escribió:
> MoNiLlO <monillo@adinf.es> writes:
>> When I launch one insert returns error:
>> I put the function and the returned error.
>> CREATE OR REPLACE FUNCTION ventas_det_a_ventas_imp()
>> RETURNS trigger AS
>> ...
>> ERROR: control reached end of trigger procedure without RETURN
>> CONTEXT: PL/pgSQL function "ventas_imp_a_ventas_cab"
> The function that's lacking a RETURN is not the one you're showing us.
>
> regards, tom lane
>
Thanks for open my eyes.
Great stupidity to mine. This is a second trigger that has no code and
therefore there is no return.
I feel the lost of time.
Best regards.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Control reached end of trigger procedure without RETURN
2011-01-22 06:32 Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
2011-01-25 09:27 ` Re: Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
2011-01-25 15:06 ` Re: Control reached end of trigger procedure without RETURN Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-01-25 15:22 ` MoNiLlO <monillo@adinf.es>
2 siblings, 0 replies; 9+ messages in thread
From: MoNiLlO @ 2011-01-25 15:22 UTC (permalink / raw)
To: pgsql-sql
El 25/01/2011 16:06, Tom Lane escribió:
> MoNiLlO <monillo@adinf.es> writes:
>> When I launch one insert returns error:
>> I put the function and the returned error.
>> CREATE OR REPLACE FUNCTION ventas_det_a_ventas_imp()
>> RETURNS trigger AS
>> ...
>> ERROR: control reached end of trigger procedure without RETURN
>> CONTEXT: PL/pgSQL function "ventas_imp_a_ventas_cab"
> The function that's lacking a RETURN is not the one you're showing us.
>
> regards, tom lane
>
Thanks for open my eyes.
Great stupidity to mine. This is a second trigger that has no code and
therefore there is no return.
I feel the lost of time.
Best regards.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Control reached end of trigger procedure without RETURN
2011-01-22 06:32 Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
2011-01-25 09:27 ` Re: Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
2011-01-25 15:06 ` Re: Control reached end of trigger procedure without RETURN Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-01-25 15:24 ` Adelo <adelo.herrero@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Adelo @ 2011-01-25 15:24 UTC (permalink / raw)
To: pgsql-sql
El 25/01/2011 16:06, Tom Lane escribió:
> MoNiLlO <monillo@adinf.es> writes:
>> When I launch one insert returns error:
>> I put the function and the returned error.
>> CREATE OR REPLACE FUNCTION ventas_det_a_ventas_imp()
>> RETURNS trigger AS
>> ...
>> ERROR: control reached end of trigger procedure without RETURN
>> CONTEXT: PL/pgSQL function "ventas_imp_a_ventas_cab"
> The function that's lacking a RETURN is not the one you're showing us.
>
> regards, tom lane
>
Thanks for open my eyes.
Great stupidity to mine. This is a second trigger that has no code and
therefore there is no return.
I feel the lost of time.
Best regards.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Control reached end of trigger procedure without RETURN
@ 2011-01-22 09:37 Adelo Herrero Pérez <adelo.herrero@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Adelo Herrero Pérez @ 2011-01-22 09:37 UTC (permalink / raw)
To: pgsql-sql
Buenos días:
Tengo una función que se dispara en un trigger cuya finalidad es; crear un registro en otra tabla o actualizarlo y devolviendo el id del registro creado/modificado para guardarlo en la tabla desde la que se llama. Intentaré explicarlo mejor:
tablaA --> Es la que dispara el trigger y en la que quiero guardar el id del registro modificado / creado en la tabla B.
tablaB --> Digamos que es una tabla resumen de la tablaA y se mantiene desde el trigger de la misma, creando modificando y/o borrando los registros.
Finalidad --> Eso, actualizar la tablaB desde la A y guardar en el registro de de la tablaA a que registro de la tablaB "pertenece".
Cuando hago el insert me devuelve el error del asunto
Control reached end of trigger procedure without RETURN
y me indica la línea correspondiente al INSERT.
¿Acaso no continúa el flujo if (not found) y debería hacer el return NEW tras el insert? ¿y lo mismo en el update del exception?
Agradezco de antemano vuestro tiempo y ayudas.
Un saludo.
P.D. Estructura de la función.
DECLARE
id_tablaB integer; -- Id de la tabla B.
BEGIN
IF (TG_OP = 'DELETE') THEN
-- Descontar valores
ELSIF (TG_OP = 'UPDATE') THEN
-- Modificar valores.
ELSIF (TG_OP = 'INSERT') THEN
-- Asignar valores
END IF;
-- Update con nuevos valores y retornamos el id para actualizar detalle.
UPDATE tablaB
SET campo1=valor1,
campo2=valor2
WHERE tablaA_id=NEW.tblaB_id
RETURNING tablaB_id INTO id_tablaB;
-- Si no existe, hacemos el insert y retornamos el id para actualizar detalle.
IF (NOT FOUND) THEN
BEGIN -- No existÌa el registro y lo creamos.
--
-- AQUÍ DEVUELVEL EL ERROR.
--
INSERT INTO tablaB (
campo1,
campo2)
VALUES ( valor1,
valor2 )
RETURNING tablaB_id INTO id_tablaB;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
UPDATE tablaB
SET campo1=valor1,
campo2=valor2
WHERE tablaA_id=NEW.tblaB_id
RETURNING tablaB_id INTO id_tablaB;
END;
END IF;
-- Borrado de ventas_imp si el resumen del % y factura es 0.
DELETE FROM tablaB
WHERE tablaA_id=id_tablaA AND (campo3=0 OR campo3 IS NULL);
-- Guardamos el id de la tabla ventas_imp y retornamos el record modificado.
NEW.tablaB_id := id_tablaB
return NEW;
END
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Control reached end of trigger procedure without RETURN
@ 2011-01-25 09:33 Adelo Herrero <adelo@adinf.es>
0 siblings, 0 replies; 9+ messages in thread
From: Adelo Herrero @ 2011-01-25 09:33 UTC (permalink / raw)
To: pgsql-sql
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 (
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Control reached end of trigger procedure without RETURN
@ 2011-01-25 09:38 Adelo <adelo.herrero@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Adelo @ 2011-01-25 09:38 UTC (permalink / raw)
To: pgsql-sql
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 (
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2011-01-25 15:24 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-22 06:32 Control reached end of trigger procedure without RETURN MoNiLlO <monillo@adinf.es>
2011-01-25 09:27 ` MoNiLlO <monillo@adinf.es>
2011-01-25 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-25 15:21 ` MoNiLlO <monillo@adinf.es>
2011-01-25 15:22 ` MoNiLlO <monillo@adinf.es>
2011-01-25 15:24 ` Adelo <adelo.herrero@gmail.com>
2011-01-22 09:37 Control reached end of trigger procedure without RETURN Adelo Herrero Pérez <adelo.herrero@gmail.com>
2011-01-25 09:33 Re: Control reached end of trigger procedure without RETURN Adelo Herrero <adelo@adinf.es>
2011-01-25 09:38 Re: Control reached end of trigger procedure without RETURN Adelo <adelo.herrero@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox