Re: error handling

>From: Bruno Wolff III <bruno(at)wolff(dot)to>
>To: Verena Ruff <lists(at)triosolutions(dot)at>
>CC: pgsql-novice(at)postgresql(dot)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(at)triosolutions(dot)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
}

*/