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

Groups > comp.databases.postgresql > #679

Re: exceptions

Show all headers | View raw

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

Back to comp.databases.postgresql | Previous | Next — Previous 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