Предполагая, что 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 предоставляет для этого функцию crosstab()
в дополнительном модуле tablefunc
.< br> Основные инструкции:
Базовая сводная таблица с 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
таблицы
- Ваша функция завершится ошибкой после
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 или более поздней версии. (Можно сделать так, чтобы он работал с пг 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);
Связанный:
person
Erwin Brandstetter
schedule
11.04.2012
string_agg()
должны быть единственными исключениями. Замените это наarray_to_string(array_agg(col1), ', ')
в 8.4. Вот пример. - person Erwin Brandstetter   schedule 13.04.2012