Re: error handling - Mailing list 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
  }

*/