X-Received: by 10.129.73.22 with SMTP id w22mr7899611ywa.19.1446238317192; Fri, 30 Oct 2015 13:51:57 -0700 (PDT)
X-Received: by 10.140.102.45 with SMTP id v42mr213843qge.9.1446238317173; Fri, 30 Oct 2015 13:51:57 -0700 (PDT)
Path: csiph.com!au2pb.net!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!peer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!c107no3497445qgd.1!news-out.google.com!j14ni17264qge.0!nntp.google.com!c107no3497441qgd.1!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.postgresql
Date: Fri, 30 Oct 2015 13:51:56 -0700 (PDT)
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=131.0.124.2; posting-account=B8UCGQoAAABexTqTiGJjBwydXwJz7lLA
NNTP-Posting-Host: 131.0.124.2
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <7431a992-80e1-4a46-b9a8-e24e431c3f30@googlegroups.com>
Subject: exceptions
From: zeromorph20@gmail.com
Injection-Date: Fri, 30 Oct 2015 20:51:57 +0000
Content-Type: text/plain; charset=ISO-8859-1
X-Received-Bytes: 3169
X-Received-Body-CRC: 1836484058
Xref: csiph.com comp.databases.postgresql:677


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