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

Groups > comp.databases.postgresql > #677 > unrolled thread

exceptions

Back to article view | Back to comp.databases.postgresql

Contents

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

#677 — exceptions

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

[toc] | [next] | [standalone]

#679

zeromorph20@gmail.com 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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web