Well, traditionally I would create a LOOP where I tried the SELECT, if there was nothing I did the INSERT, if that raised an exception I would repeat the LOOP.
CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR) RETURNS INTEGER AS $
DECLARE hometown_id INTEGER;
BEGIN
WITH sel AS (
SELECT id FROM hometowns WHERE name = hometown_name
), ins AS (
INSERT INTO hometowns (name)
SELECT hometown_name
WHERE NOT EXISTS(SELECT 1 FROM sel)
RETURNING id
)
SELECT id INTO hometown_id FROM ins UNION ALL SELECT id FROM sel;
RETURN hometown_id;
END;
$ LANGUAGE plpgsql;
What's the best way to do it with the CTE? Currently I have the following which gives me Duplicate Key Exceptions when two sessions try to insert the same record at the same time.
CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR) RETURNS INTEGER AS $
DECLARE hometown_id INTEGER;
BEGIN
WITH sel AS (
SELECT id FROM hometowns WHERE name = hometown_name
), ins AS (
INSERT INTO hometowns (name)
SELECT hometown_name
WHERE NOT EXISTS(SELECT 1 FROM sel)
RETURNING id
)
SELECT id INTO hometown_id FROM ins UNION ALL SELECT id FROM sel;
RETURN hometown_id;
END;
$ LANGUAGE plpgsql;
And that is no bueno. Should I just put the whole thing in a LOOP?
