Я создал функцию «слияния», которая должна выполнять либо запрос UPDATE, либо запрос INSERT, в зависимости от существующих данных. Вместо написания upsert-wrapper для каждой таблицы (как в большинстве доступных примеров) эта функция принимает целые строки SQL. Обе строки SQL автоматически генерируются нашим приложением.
План состоит в том, чтобы вызвать функцию следующим образом:
-- hypothetical "settings" table, with a primary key of (user_id, setting):
SELECT merge(
$$UPDATE settings SET value = 'x' WHERE user_id = 42 AND setting = 'foo'$$,
$$INSERT INTO settings (user_id, setting, value) VALUES (42, 'foo', 'x')$$
);
Вот полный код функции merge():
CREATE OR REPLACE FUNCTION merge (update_sql TEXT, insert_sql TEXT) RETURNS TEXT AS
$func$
DECLARE
max_iterations INTEGER := 10;
i INTEGER := 0;
num_updated INTEGER;
BEGIN
-- usually returns before re-entering the loop
LOOP
-- first try the update
EXECUTE update_sql;
GET DIAGNOSTICS num_updated = ROW_COUNT;
IF num_updated > 0 THEN
RETURN 'UPDATE';
END IF;
-- nothing was updated: try the insert, watching out for concurrent inserts
BEGIN
EXECUTE insert_sql;
RETURN 'INSERT';
EXCEPTION WHEN unique_violation THEN
-- nop; just loop and try again from the top
END;
-- emergency brake
i := i + 1;
IF i >= max_iterations THEN
RAISE EXCEPTION 'merge(): tried looping % times, giving up now.', i;
EXIT;
END IF;
END LOOP;
END;
$func$
LANGUAGE plpgsql;
В моих тестах она работает достаточно хорошо, но я не уверен, что не упустил ничего важного, особенно в отношении одновременных запросов UPDATE/INSERT/DELETE, которые могут выполняться без использования этой функции. Я пропустил что-то важное?
Среди ресурсов, с которыми я консультировался для этой функции:
- ОБНОВИТЬ/ВСТАВИТЬ пример 40.2 в PostgreSQL руководство
- Почему UPSERT такой сложный?
- ТАК: Вставить при дублирующем обновлении (postgresql)
(Редактировать: одной из целей было избежать блокировки целевой таблицы.)
merge()заключалась в том, чтобы в конце концов всегда добиваться успеха, насколько это возможно. Конечно, передача строк SQL в функцию не очень элегантна, но а) я еще не видел сопоставимого решения и б) запросы INSERT/UPDATE генерируются автоматически, что эффективно скрывает уродство за API. 27.09.2013