Re: error handling
От | Robert Wimmer |
---|---|
Тема | Re: error handling |
Дата | |
Msg-id | BAY116-F332C2774BC39073C9F888D0B00@phx.gbl обсуждение исходный текст |
Ответ на | Re: error handling (Bruno Wolff III <bruno@wolff.to>) |
Список | pgsql-novice |
>From: Bruno Wolff III <bruno@wolff.to> >To: Verena Ruff <lists@triosolutions.at> >CC: pgsql-novice@postgresql.org >Subject: Re: [NOVICE] error handling >Date: Thu, 27 Apr 2006 14:48:03 -0500 > >On Thu, Apr 27, 2006 at 15:29:07 +0200, > Verena Ruff <lists@triosolutions.at> wrote: > > Hello, > > > > I have a table with an UNIQUE constraint. Is it possible to have > > something like a trigger (or error handler) which is called every time > > a insert statement would break this constraint? Or in some simple cases > > that this record is just dropped silently, without reporting an error? > >Unique constraints are not deferrable in Postgres. It would be possible to >have a function do the insert and trap errors. I don't know if that will >help in your situation though. > In the following code i show two ways to handle errors in Postgres. The first one is the simple version, the second one is very usefull in client side programming. besides : if there is some interest on this topic I could write an article about it .. ------- -- error example ------- DROP SCHEMA test CASCADE; CREATE SCHEMA test; CREATE TABLE test.ref( id INTEGER NOT NULL PRIMARY KEY ); CREATE TABLE test.test( id SERIAL NOT NULL PRIMARY KEY, name TEXT NOT NULL UNIQUE, ref_id INTEGER NOT NULL REFERENCES test.ref(id) ); CREATE OR REPLACE FUNCTION test.check_on_insert() RETURNS TRIGGER AS $$ BEGIN IF (NEW.name IS NULL) OR (CHAR_LENGTH(NEW.name) = 0) THEN -- check the not null constaint RAISE NOTICE 'name must not be null or empty'; -- only in the demo version RETURN NULL; -- don't insert END IF; IF EXISTS(SELECT * FROM test.test WHERE name = NEW.name) THEN -- check the unique constaint RAISE NOTICE 'value "%" not unique in table test',new.NAME; -- it's a demo RETURN NULL; END IF; IF NOT EXISTS(SELECT * FROM test.ref WHERE id = NEW.ref_id) THEN -- check foreign constaint RAISE NOTICE 'invalid ref_id "%"',NEW.ref_id; -- it's a demo RETURN NULL; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER test_on_insert_trigger BEFORE INSERT ON test.test FOR EACH ROW EXECUTE PROCEDURE test.check_on_insert(); INSERT INTO test.ref (id) VALUES(1); INSERT INTO test.test(name,ref_id) VALUES('dummy',1); -- will work INSERT INTO test.test(name,ref_id) VALUES(NULL,1); INSERT INTO test.test(name,ref_id) VALUES('',1); INSERT INTO test.test(name,ref_id) VALUES('dummy',1); INSERT INTO test.test(name,ref_id) VALUES ('joe',1); -- will work INSERT INTO test.test(name,ref_id) VALUES('mike',2); SELECT * FROM test.test; -- ----------------------- -- 'client friendly' version -- ----------------------- DROP TRIGGER test_on_insert_trigger ON test.test; DROP FUNCTION test.check_on_insert(); CREATE TABLE test.error( id SERIAL NOT NULL, message TEXT ); INSERT INTO test.error(id,message) VALUES(-1,'column "name" > value must not be NULL or empty'); INSERT INTO test.error(id,message) VALUES(-2,'column "name" > value must be UNIQUE'); INSERT INTO test.error(id,message) VALUES(-3,'column "ref_id" > invalid reference'); -- check function CREATE OR REPLACE FUNCTION test.client_check_on_insert(pname TEXT,pref_id INTEGER) RETURNS INTEGER AS $$ BEGIN IF (pname IS NULL) OR (CHAR_LENGTH(pname) = 0) THEN -- check the not null constaint RETURN -1; END IF; IF EXISTS(SELECT * FROM test.test WHERE name = pname) THEN -- check the unique constaint RETURN -2; END IF; IF NOT EXISTS(SELECT * FROM test.ref WHERE id = pref_id) THEN -- check foreign constaint RETURN -3; END IF; RETURN 0; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION test.check_on_insert() RETURNS TRIGGER AS $$ DECLARE ret INTEGER; BEGIN ret := test.client_check_on_insert(NEW.name,NEW.ref_id); IF ret <> 0 THEN RAISE EXCEPTION '# %',ret; -- mark these errors with "#" END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER test_on_insert_trigger BEFORE INSERT ON test.test FOR EACH ROW EXECUTE PROCEDURE test.check_on_insert(); INSERT INTO test.test(name,ref_id) VALUES('hans',1); -- will work INSERT INTO test.test(name,ref_id) VALUES(NULL,1); INSERT INTO test.test(name,ref_id) VALUES('',1); INSERT INTO test.test(name,ref_id) VALUES('joe',1); INSERT INTO test.test(name,ref_id) VALUES('mike',2); /* you can now catch the error in a client program for example (java) ... try { stmt.execute("INSERT INTO test.test(name,ref_id) VALUES('blabla',23)"); } catch (SQLException e) { if (e.SQLState.equals("P0001")) return parseError(e.SWLErrorMessage); .. otherwise } */
В списке pgsql-novice по дате отправления: