Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #677

exceptions

Show all headers | View raw

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

Back to comp.databases.postgresql | Previous | Next — Next in thread | Find similar

Thread

exceptions zeromorph20@gmail.com - 2015-10-30 13:51 -0700
  Re: exceptions Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2015-11-01 22:13 +0100

csiph-web