1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
alter function insert_or_update_device(device_id, user_id, text, text, device_push_info,
device_command[], jsonb, out device) rename to insert_or_update_device_1;
create function insert_or_update_device(
p_device_id device_id,
p_user_id user_id,
p_name text,
p_type text,
p_push device_push_info,
p_available_commands device_command[],
p_location jsonb,
out result device)
returns setof device
language plpgsql
as $$
begin
update device
set name = coalesce(p_name, name),
type = coalesce(p_type, type),
push = coalesce(p_push, push),
available_commands = coalesce(p_available_commands, available_commands),
push_expired = push_expired and p_push is null,
location = coalesce(p_location, location)
where device_id = p_device_id and user_id = p_user_id
returning *
into result;
if not found then
begin
insert into device (device_id, user_id, name, type, push, push_expired,
available_commands, location)
values (p_device_id, p_user_id, p_name, p_type, p_push, p_push is null,
p_available_commands, coalesce(p_location, '{}'::jsonb))
returning *
into result;
exception
when unique_violation then
-- almost certainly updating another user's device, or we have
-- a broken RNG on the system. return nothing to signal either
-- error.
return;
end;
end if;
return next;
end
$$;
|
