Re: Simple Atomic Relationship Insert

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(dot)difalco(at)gmail(dot)com> 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?