Re: Simple Atomic Relationship Insert

John McKown wrote:
> On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco
> <robert(dot)difalco(at)gmail(dot)com <mailto:robert(dot)difalco(at)gmail(dot)com>>wrote:
>
> Let's say I have two tables like this (I'm leaving stuff out for
> simplicity):
>
> CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1;
> CREATE TABLE hometowns (
> id INTEGER DEFAULT nextval('HOMETOWN_SEQ_GEN'),
> name VARCHAR,
> PRIMARY KEY (id),
> UNIQUE(name)
> );
>
> CREATE SEQUENCE USER_SEQ_GEN START 1 INCREMENT 1;
> CREATE TABLE users (
> id BIGINT DEFAULT nextval('USER_SEQ_GEN'),
> hometown_id INTEGER,
> name VARCHAR NOT NULL,
> PRIMARY KEY (id),
> FOREIGN KEY (hometown_id) REFERENCES hometowns(id)
> );
>
> The hometowns table is populate as users are created. For example,
> a client may submit {"name":"Robert", "hometown":"Portland"}.
>
> The hometowns table will never be updated, only either queries or
> inserted.
>
> So given this I need to INSERT a row into "users" and either SELECT
> the hometowns.id <http://hometowns.id> that matches "Portland" or if
> it doesn't exist I INSERT it returning the hometowns.id
> <http://hometowns.id>".
>
> Normally I would do by first doing a SELECT on hometown. If I don't
> get anything I do an INSERT into hometown RETURNING the id. If THAT
> throws an error then I do the SELECT again. Now I'm finally ready to
> INSERT into users using the hometowns.id <http://hometowns.id> from
> the above steps.
>
> But wow, that seems like a lot of code for a simple "Add if doesn't
> exist" foreign key relationship -- but this is how I've always done.
>
> So my question. Is there a simpler, more performant, or thread-safe
> way to do this?
>
> Thanks!
>
>
> ​What occurs to me is to simply do an INSERT into the "hometowns" table
> and just ignore the "already exists" return indication. Then do a SELECT
> to get the hometowns​ id which now exists, then INSERT the users. but I
> could easily be overlooking some reason why this wouldn't work properly.

And one more approach:

CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1;
CREATE TABLE hometowns (
id INTEGER DEFAULT nextval('HOMETOWN_SEQ_GEN'),
name VARCHAR,
PRIMARY KEY (id),
UNIQUE(name)
);

CREATE SEQUENCE USER_SEQ_GEN START 1 INCREMENT 1;
CREATE TABLE users (
id BIGINT DEFAULT nextval('USER_SEQ_GEN'),
hometown_id INTEGER,
name VARCHAR NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (hometown_id) REFERENCES hometowns(id)
);

create or replace view user_town as
select
users.name as username,
hometowns.name as hometownname
from users
join hometowns
on hometowns.id = users.hometown_id;

create rule user_town_exists as on insert to user_town
where exists(select id from hometowns where (hometowns.name =
new.hometownname))
do
insert into users (name, hometown_id)
values (new.username, (select id from hometowns where
(hometowns.name = new.hometownname)));

create rule user_town_not_exists as on insert to user_town
where not exists(select id from hometowns where (hometowns.name =
new.hometownname))
do (
insert into hometowns (name) values (new.hometownname);
insert into users (name, hometown_id)
values (new.username, (select id from hometowns where
(hometowns.name = new.hometownname)));
);

create rule user_town_nothing as on insert to user_town
do instead nothing;

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com