With the single CTE I don't believe you can do a full upsert loop. If you're doing this inside of a postgres function, your changes are already atomic, so I don't believe by switching you are buying yourself much (if anything) by using a CTE query instead of something more traditional here. The advantages of switching to a CTE would be if this code was all being done inside of the app code with multiple queries. On Tue, Jan 13, 2015 at 4:39 PM, Robert DiFalco <robert.difalco@xxxxxxxxx> wrote: > 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. > > 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? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
