catch postgresql exceptions when a foreingkey not exist in the table with which it interacts.

CREATE OR REPLACE FUNCTION calculo_pensionado_prueba(text)
RETURNS text AS
$BODY$
DECLARE
mensaje TEXT;
ultimo_registro INTEGER;
BEGIN
BEGIN
RAISE NOTICE 'inicia';
ultimo_registro = (SELECT CASE WHEN max(id) NOTNULL
THEN max(id) + 1 ELSE 1 END FROM calculonomina_periodo_calculo_temp);

INSERT INTO calculonomina_periodo_calculo_temp VALUES (ultimo_registro, 330, 10.00, 10.00, 100,NULL, 15, 569, 501, 183, '{"monto":"hola"}', 9135, 35, 1, 1, 124, 56, 'Pensionado');

--RAISE NOTICE 'despues del insert';
--EXECUTE 'DROP TABLE ' || $1;

EXCEPTION
WHEN FOREIGN_KEY_VIOLATION THEN
mensaje:='viola el foreingkey';
--RAISE NOTICE 'Table % not defined. Moving on anyhow.', $1;


WHEN INVALID_FOREIGN_KEY THEN
mensaje := 'error de foreingkey invalido';
--RAISE NOTICE 'Table % not defined. Moving on anyhow.', $1;


WHEN UNDEFINED_TABLE THEN
mensaje :='No se elimino la tabla ';
--RAISE NOTICE 'Table % not defined. Moving on anyhow.', $1;
--RETURN;

when integrity_constraint_violation then
mensaje:='entra1';

when restrict_violation then
mensaje:='entra2';

when not_null_violation then
mensaje:='entra3';

when unique_violation then
mensaje:='entra4';

when check_violation then
mensaje:='entra5';

when exclusion_violation then
mensaje:='entra6';

END;
--RAISE NOTICE 'Dropped table %', $1;
RETURN mensaje;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
COST 100;






NOTICE: inicia
ERROR: insert or update on table "calculonomina_periodo_calculo_temp" violates foreign key constraint "cat_pagador_id_refs_id_da0ad7cc"
DETAIL: Key (cat_pagador_id)=(10000) is not present in table "catalogos_cat_pagador".

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

ERROR: insert or update on table "calculonomina_periodo_calculo_temp" violates foreign key constraint "cat_pagador_id_refs_id_da0ad7cc"
SQL state: 23503
Detail: Key (cat_pagador_id)=(10000) is not present in table "catalogos_cat_pagador".



I tried to pick it up but does not enter the exceptions

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

[email protected] writes:

catch postgresql exceptions when a foreingkey not exist in the table with which it interacts.

CREATE OR REPLACE FUNCTION calculo_pensionado_prueba(text)
RETURNS text AS
$BODY$
DECLARE
mensaje TEXT;
ultimo_registro INTEGER;
BEGIN
BEGIN
RAISE NOTICE 'inicia';
ultimo_registro = (SELECT CASE WHEN max(id) NOTNULL
THEN max(id) + 1 ELSE 1 END FROM calculonomina_periodo_calculo_temp);

INSERT INTO calculonomina_periodo_calculo_temp VALUES (ultimo_registro, 330, 10.00, 10.00, 100,NULL, 15, 569, 501, 183, '{"monto":"hola"}', 9135, 35, 1, 1, 124, 56, 'Pensionado');

--RAISE NOTICE 'despues del insert';
--EXECUTE 'DROP TABLE ' || $1;

EXCEPTION
WHEN FOREIGN_KEY_VIOLATION THEN

See the documentation to figure out what is the condition name that
matches with the SQL State you have:

http://www.postgresql.org/docs/9.4/interactive/errcodes-appendix.html

23503 foreign_key_violation

Also, you can see that you have two BEGIN, and it might be causing your
problem here, anyway I see no use for this construct, try removing one.

--
Dimitri Fontaine
PostgreSQL DBA, Architecte

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)