Моя база данных PostgreSQL содержит таблицу для хранения экземпляров зарегистрированного объекта. Эта таблица заполняется через загрузку электронных таблиц. Веб-интерфейс позволяет оператору изменять представленную информацию. Однако исходные данные не изменяются. Все изменения сохраняются в отдельной таблице с помощью столбцов unique_id , column_name , value и updated_at .
После внесения изменений они представляются оператору, сначала запрашивая исходную таблицу, а затем запрашивая таблицу изменений (используя идентификатор экземпляра и последнюю дату изменения, сгруппированные по имени столбца). Два результата объединены в PHP и представлены в веб-интерфейсе. Это довольно жесткий способ решения этой задачи, и я бы хотел сохранить логику в SQL.
Я могу легко выбрать последние изменения для таблицы, используя следующий запрос:
SELECT fltr_chg.unique_id, fltr_chg.column_name, chg_val.value FROM changes AS chg_val JOIN ( SELECT chg_rec.unique_id, chg_rec.column_name, MAX( chg_rec.updated_at ) FROM information_schema.columns AS source JOIN changes AS chg_rec ON source.table_name = 'instances' AND source.column_name = chg_rec.column_name GROUP BY chg_rec.unique_id, chg_rec.column_name ) AS fltr_chg ON fltr_chg.unique_id = chg_val.unique_id AND fltr_chg.column_name = chg_val.column_name;
И выбор записей из таблицы instances так же просто:
SELECT * FROM instances;
Теперь, если бы был только способ преобразования прежнего результата и подстановки итоговых значений в последние, на основе unique_id и column_name , и все еще сохраняя результат в виде таблицы, проблема будет решена. Возможно ли это сделать?
Я уверен, что это не самая редкая проблема, и, скорее всего, некоторые системы отслеживают изменения данных аналогичным образом. Как они применяют их обратно к данным, если не через один из описанных выше способов (текущие и поисковые решения)?
Предполагая, что Postgres 9.1 или новее.
Я упростил / оптимизировал ваш основной запрос для получения последних значений:
SELECT DISTINCT ON (1,2) c.unique_id, a.attname AS col, c.value FROM pg_attribute a LEFT JOIN changes c ON c.column_name = a.attname AND c.table_name = 'instances' -- AND c.unique_id = 3 -- uncomment to fetch single row WHERE a.attrelid = 'instances'::regclass -- schema-qualify to be clear? AND a.attnum > 0 -- no system columns AND NOT a.attisdropped -- no deleted columns ORDER BY 1, 2, c.updated_at DESC;
Я запрашиваю каталог PostgreSQL вместо стандартной информационной схемы, потому что это быстрее. Обратите внимание на специальный бросок на ::regclass .
Теперь это дает вам таблицу . Вы хотите все значения для одного unique_id в строке .
Для этого у вас есть в основном три варианта:
Один подзабор (или объединение) на столбец. Дорогой и громоздкий. Но допустимый вариант для нескольких столбцов.
Большой оператор CASE .
Функция поворота . PostgreSQL предоставляет функцию tablefunc crosstab() в дополнительном модуле tablefunc для этого.
Основные инструкции:
crosstab() таблицей crosstab() Я полностью переписал функцию:
SELECT * FROM crosstab( $x$ SELECT DISTINCT ON (1, 2) unique_id, column_name, value FROM changes WHERE table_name = 'instances' -- AND unique_id = 3 -- un-comment to fetch single row ORDER BY 1, 2, updated_at DESC; $x$, $y$ SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid = 'instances'::regclass -- possibly schema-qualify table name AND attnum > 0 AND NOT attisdropped AND attname <> 'unique_id' ORDER BY attnum $y$ ) AS tbl ( unique_id integer -- !!! You have to list all columns in order here !!! -- );
Я отделил поиск каталога от запроса значения, так как функция crosstab() с двумя параметрами предоставляет имена столбцов отдельно. Отсутствующие значения (без изменений в записи) автоматически заменяются NULL . Идеальное совпадение для этого варианта использования!
Предполагая, что attname соответствует column_name . Исключая unique_id , который играет особую роль.
Обращение к вашему комментарию : есть способ автоматически предоставить список определения столбцов. Впрочем, это не для слабонервных.
Я использую несколько расширенных функций Postgres здесь: crosstab() , функция plpgsql с динамическим SQL, обработка составного типа, расширенная котировка доллара, поиск по каталогу, агрегированная функция, функция окна, тип идентификатора объекта, …
Тестовая среда:
CREATE TABLE instances ( unique_id int , col1 text , col2 text -- two columns are enough for the demo ); INSERT INTO instances VALUES (1, 'foo1', 'bar1') , (2, 'foo2', 'bar2') , (3, 'foo3', 'bar3') , (4, 'foo4', 'bar4'); CREATE TABLE changes ( unique_id int , table_name text , column_name text , value text , updated_at timestamp ); INSERT INTO changes VALUES (1, 'instances', 'col1', 'foo11', '2012-04-12 00:01') , (1, 'instances', 'col1', 'foo12', '2012-04-12 00:02') , (1, 'instances', 'col1', 'foo1x', '2012-04-12 00:03') , (1, 'instances', 'col2', 'bar11', '2012-04-12 00:11') , (1, 'instances', 'col2', 'bar17', '2012-04-12 00:12') , (1, 'instances', 'col2', 'bar1x', '2012-04-12 00:13') , (2, 'instances', 'col1', 'foo2x', '2012-04-12 00:01') , (2, 'instances', 'col2', 'bar2x', '2012-04-12 00:13') -- NO change for col1 of row 3 - to test NULLs , (3, 'instances', 'col2', 'bar3x', '2012-04-12 00:13'); -- NO changes at all for row 4 - to test NULLs
CREATE OR REPLACE FUNCTION f_curr_instance(int, OUT t public.instances) AS $func$ BEGIN EXECUTE $f$ SELECT * FROM crosstab($x$ SELECT DISTINCT ON (1,2) unique_id, column_name, value FROM changes WHERE table_name = 'instances' AND unique_id = $f$ || $1 || $f$ ORDER BY 1, 2, updated_at DESC; $x$ , $y$ SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid = 'public.instances'::regclass AND attnum > 0 AND NOT attisdropped AND attname <> 'unique_id' ORDER BY attnum $y$) AS tbl ($f$ || (SELECT string_agg(attname || ' ' || atttypid::regtype::text , ', ' ORDER BY attnum) -- must be in order FROM pg_catalog.pg_attribute WHERE attrelid = 'public.instances'::regclass AND attnum > 0 AND NOT attisdropped) || ')' INTO t; END $func$ LANGUAGE plpgsql;
instances таблиц жестко запрограммированы, схема может быть однозначной. Обратите внимание на использование типа таблицы в качестве типа возврата. Существует тип строки, зарегистрированный автоматически для каждой таблицы в PostgreSQL. Это связано с возвращаемым типом функции crosstab() .
Это связывает функцию с типом таблицы:
DROP table ALTER TABLE . Вы должны воссоздать его (без изменений). Я считаю это ошибкой в 9.1. ALTER TABLE не должен молча прерывать функцию, но вызывает ошибку. Это очень хорошо.
Вызов:
SELECT * FROM f_curr_instance(3); unique_id | col1 | col2 ----------+-------+----- 3 |<NULL> | bar3x
Обратите внимание, что здесь col1 здесь имеет NULL .
Используйте в запросе для отображения экземпляра с его последними значениями:
SELECT i.unique_id , COALESCE(c.col1, i.col1) , COALESCE(c.col2, i.col2) FROM instances i LEFT JOIN f_curr_instance(3) c USING (unique_id) WHERE i.unique_id = 3;
(Добавлено 2016. Это динамит.)
Требуется Postgres 9.1 или новее. (Может быть сделано для работы с pg 8.4, но я не потрудился отменить.)
CREATE OR REPLACE FUNCTION f_curr_instance(_id int, INOUT _t ANYELEMENT) AS $func$ DECLARE _type text := pg_typeof(_t); BEGIN EXECUTE ( SELECT format ($f$ SELECT * FROM crosstab( $x$ SELECT DISTINCT ON (1,2) unique_id, column_name, value FROM changes WHERE table_name = %1$L AND unique_id = %2$s ORDER BY 1, 2, updated_at DESC; $x$ , $y$ SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid = %1$L::regclass AND attnum > 0 AND NOT attisdropped AND attname <> 'unique_id' ORDER BY attnum $y$) AS ct (%3$s) $f$ , _type, _id , string_agg(attname || ' ' || atttypid::regtype::text , ', ' ORDER BY attnum) -- must be in order ) FROM pg_catalog.pg_attribute WHERE attrelid = _type::regclass AND attnum > 0 AND NOT attisdropped ) INTO _t; END $func$ LANGUAGE plpgsql;
Вызов (предоставление типа таблицы с помощью NULL::public.instances :
SELECT * FROM f_curr_instance(3, NULL::public.instances);
Связанный: