PostgreSQL 9.3: динамическая сводная таблица для огромных записей
Я хочу отобразить сводную таблицу для следующих данных. У меня есть таблица с 10000 записей.
Таблица: Mat_test
CREATE TABLE Mat_test AS
SELECT DISTINCT 1 + floor(random() * 1500000)::integer AS id,
2 + floor(random() * 1600000)::integer AS Numbers
FROM generate_series(1, 10000) g;
Теперь создаем функцию, из которой я хочу отобразить таблицу кросс-таблицы для вышеуказанных записей:
функция: Mat_test_cross_tab
CREATE OR REPLACE FUNCTION Mat_test_cross_tab (_retType anyelement
, table_name varchar)
RETURNS SETOF anyelement AS
$BODY$
DECLARE
query varchar;
cols varchar;
cols1 varchar;
cols2 varchar;
BEGIN
cols := 'SELECT string_agg(numbers::varchar,'','') from '||table_name||'';
execute cols into cols1;
cols1 := 'SELECT REPLACE('''||cols1||''','','','' " INT,"'')';
execute cols1 into cols2;
cols2 := '"'||cols2||'" INT';
raise info '%',cols2;
query := 'SELECT * FROM Crosstab
(
'' SELECT id,numbers,count(*) as t
from '||table_name||'
group by 1,2
order by 1,2'',
$select distinct numbers from '||table_name||'$
)
AS (id integer,'||cols2')';
RAISE INFO '%',query;
RETURN QUERY EXECUTE query;
END;
$BODY$ LANGUAGE PLPGSQL;
Сейчас я создаю функцию для создания пользовательского типа для возврата набора результатов вышеупомянутой функции.
функция: Type_mat
CREATE OR REPLACE FUNCTION Type_mat(table_name varchar)
RETURNS VOID AS
$BODY$
DECLARE
query varchar;
cols varchar;
cols1 varchar;
cols2 varchar;
BEGIN
cols := 'SELECT string_agg(numbers::varchar,'','') from '||table_name||'';
execute cols into cols1;
cols1 := 'SELECT REPLACE('''||cols1||''','','',''" INT,"'')';
execute cols1 into cols2;
cols2 := '"'||cols2||'" INT';
query := 'CREATE TYPE Type_for_mat AS
(
id integer,
'||cols2||'
)';
RAISE INFO '%',query;
EXECUTE query;
END;
$BODY$ LANGUAGE PLPGSQL;
Вызов:
SELECT Type_mat('mat_test');
Получение ошибки:
ERROR: tables can have at most 1600 columns