Path: csiph.com!news.swapon.de!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: Dimitri Fontaine Newsgroups: comp.databases.postgresql Subject: Re: exceptions Date: Sun, 01 Nov 2015 22:13:12 +0100 Organization: A noiseless patient Spider Lines: 37 Message-ID: References: <7431a992-80e1-4a46-b9a8-e24e431c3f30@googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain Injection-Info: mx02.eternal-september.org; posting-host="d82aa4f1883ab7155c995516303c9b46"; logging-data="20915"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+hIW7FCTC7XKTb3hNS4Il2" User-Agent: Gnus/5.13001 (Ma Gnus v0.10) Emacs/24.5 (darwin) Cancel-Lock: sha1:UrniDCwAeZsrzEr7bMBoDoRip/E= sha1:NZOEfib/2XGfcgUEjxSIgOv630Q= Xref: csiph.com comp.databases.postgresql: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