INSERT or return row if entry already exists in a table with UNIQUE constraint

Question

Greetings,

I have this table, on a postgreSQL 8.4 server :

CREATE TABLE tags (
 tagid        bigserial                 PRIMARY KEY,
 name         text                      NOT NULL,
 value        text                      NOT NULL,
 UNIQUE(name,value)
);

The normal INSERT behavior is to throw an error when new values break the uniqueness constraint. I would prefer for it not to throw the error and return either the new tagid if then insertion succeeded, or the tagid of the existing entry matching the uniqueness constraint.

I use this function to do this :

CREATE OR REPLACE FUNCTION insert_tags(my_name text, my_value text)
RETURNS bigint AS $$
DECLARE
 retval bigint;
BEGIN
 SELECT tagid INTO retval FROM tags WHERE name = my_name AND value = my_value;
 IF FOUND THEN 
  RETURN retval; 
 END IF;
 INSERT INTO tags (name, value) VALUES (my_name, my_value) RETURNING tagid INTO retval;
 RETURN retval;
END;
$$ LANGUAGE plpgsql;

In the worst case two table lookups are done before insertion. Is there a better way to do it, possibly in one lookup ?

Answer 1

Just INSERT and do some exception handling:

CREATE OR REPLACE FUNCTION insert_tags(my_name text, my_value text)
RETURNS bigint AS $$
DECLARE
 retval bigint;
BEGIN
    INSERT INTO tags (name, value) VALUES (my_name, my_value) RETURNING tagid INTO retval;
    RETURN retval;

    EXCEPTION
        WHEN unique_violation THEN
            SELECT tagid INTO retval FROM tags WHERE name = my_name AND value = my_value;
            RETURN retval;
END;
$$ LANGUAGE plpgsql;

You can find more information about this in the manual: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING