Hello,
I have a typical many to many join table, in this instance it is
capturing the multiplicity described as "one person can have many
rooms and one room can have many persons". Further the join expresses
where in the room the person is sitting, a seat number. I am creating
a function to abstract this away, if there is no record with the same
person and room the insert otherwise if it already exists update the
record with the new seat value.
create table person_room (
id serial,
person_id int,
room_id int,
seat varchar(255),
unique (person_id, room_id)
);
-- version 1
create or replace function add_person_to_room(person int, room int, s
varchar(255)) as $$
begin
insert into person_room(person_id, room_id, seat) values (person, room, s);
exception when unique_violation then
update person_room set seat = s where (person_id = person) and
(room_id = room);
end;
$$ language 'plpgsql';
-- version 2
create or replace function add_person_to_room(person int, room int, s
varchar(255)) as $$
declare
i int;
begin
select into i id from person_room where (person_id = person) and
(room_id = room);
if (not found) then
insert into person_room(person_id, room_id, seat) values
(person, room, s);
else
update person_room set seat = s where (person_id = person) and
(room_id = room);
end;
$$ language 'plpgsql';
Which version is faster?
Does the exception mechanism add any overhead?
Which is more cleaner?
-ravi
