PostgreSQL function that inserts only if does not exist and returns row id.

postgresql_insert_or_select.sql

-- PostgreSQL 9.3.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
CREATE TABLE func_table (
  id                                      serial                NOT NULL   PRIMARY KEY,
  value                                   integer               NOT NULL
);

--
-- Make column unique.
--
ALTER TABLE func_table ADD CONSTRAINT unique_value UNIQUE (value);

--
-- Create a function to insert or select if already exists.
-- Returns ID of the row.
--
-- CALL WITH: select insert_or_select(123) as result;
--
CREATE OR REPLACE FUNCTION insert_or_select(val1 integer) 
    RETURNS int 
AS $$ 
DECLARE 
  l_id integer;
BEGIN

    --
    -- If row already exists, get its ID.
    --
    -- If we don't do this and just attempt an insert, the auto_increment id
    -- will increase.
    --
    SELECT id FROM func_table INTO l_id WHERE value = val1;

    --
    -- If row dows not exist, attempt inserting it.
    --
    IF l_id IS NULL THEN
        INSERT INTO func_table ("value") VALUES (val1) RETURNING id INTO l_id;
    --  RAISE NOTICE 'INSERTed with id %', l_id;
    END IF;

    --
    -- Return the row's ID.
    --
    RETURN l_id;

EXCEPTION

    WHEN OTHERS THEN

    --
    -- This should only happen on race conditions:
    --
    --    +---------------------------------+-------------------------+
    --    |  Thread A                       | Thread B                |
    --    |  ========                       | ========                |
    --    |  First select failed.           |                         |
    --    |                                 | First select failed.    |
    --    |                                 | Insert row.             |
    --    |                                 | Return ID.              |
    --    |  Insert row.                    |                         |
    --    |  ! EXCEPTION !                  |                         |
    --    |  (Assume duplicate is problem.) |                         |
    --    |  Re-select and return ID.       |                         |
    --    +---------------------------------+-------------------------+
    --
    SELECT id FROM func_table INTO l_id WHERE value = val1;
--  RAISE NOTICE 'Record already existed; SELECT found %', l_id;

    --
    -- Return the row's ID.
    --
    RETURN l_id;

END; 
$$ LANGUAGE plpgsql;