From: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
---|---|
To: | Brian Dunavant <brian(at)omniti(dot)com> |
Cc: | John McKown <john(dot)archie(dot)mckown(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Simple Atomic Relationship Insert |
Date: | 2015-01-13 21:39:03 |
Message-ID: | CAAXGW-x07DYNcuF-aSpCeLgDWug18-R2KT=LmBOi+iQFWB1gtA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Dunavant | 2015-01-13 21:53:33 | Re: Simple Atomic Relationship Insert |
Previous Message | Brian Dunavant | 2015-01-13 21:33:50 | Re: Simple Atomic Relationship Insert |