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
       $$;