Выберите динамический набор столбцов из таблицы и получите сумму для каждого

Question

Можно ли сделать следующее в Postgres:

SELECT column_name FROM information_schema WHERE table_name = 'somereport' AND data_type = 'integer';

SELECT SUM(coulmn_name[0]),SUM(coulmn_name[1]) ,SUM(coulmn_name[3]) FROM somereport;

Другими словами, мне нужно выбрать группу столбцов из таблицы в зависимости от определенных критериев, а затем суммировать каждый из этих столбцов в таблице.

Я знаю, что могу делать это в цикле, поэтому я могу подсчитывать каждый столбец независимо, но, очевидно, для этого требуется запрос для каждого столбца, возвращаемый из запроса информационной схемы. Например:

FOR r IN select column_name from information_schema where report_view_name = 'somereport' and data_type = 'integer';
LOOP
    SELECT SUM(r.column_name) FROM somereport;
END

Answer 1

Этот запрос создает полный оператор DML, после которого вы:

WITH x AS (
   SELECT 'public'::text     AS _schema  -- provide schema name ..
         ,'somereport'::text AS _tbl     -- .. and table name once
   )
SELECT 'SELECT ' || string_agg('sum(' || quote_ident(column_name)
                 || ') AS sum_' || quote_ident(column_name), ', ')
       || E'\nFROM   ' || quote_ident(x._schema) || '.' || quote_ident(x._tbl)
FROM   x, information_schema.columns
WHERE  table_schema = _schema
AND    table_name = _tbl
AND    data_type = 'integer'
GROUP  BY x._schema, x._tbl;

Вы можете выполнить его отдельно или обернуть этот запрос в функцию plpgs ql и автоматически выполнить запрос с EXECUTE:

Полная автоматизация

Протестировано с PostgreSQL 9.1.4

CREATE OR REPLACE FUNCTION f_get_sums(_schema text, _tbl text)
  RETURNS TABLE(names text[], sums bigint[]) AS
$BODY$
BEGIN

RETURN QUERY EXECUTE (
    SELECT 'SELECT ''{'
           || string_agg(quote_ident(c.column_name), ', ' ORDER BY c.column_name)
           || '}''::text[],
           ARRAY['
           || string_agg('sum(' || quote_ident(c.column_name) || ')'
                                                   , ', ' ORDER BY c.column_name)
           || ']
    FROM   '
           || quote_ident(_schema) || '.' || quote_ident(_tbl)
    FROM   information_schema.columns c
    WHERE  table_schema = _schema
    AND    table_name = _tbl
    AND    data_type = 'integer'
    );

END;
$BODY$
  LANGUAGE plpgsql;

Вызов:

SELECT unnest(names) AS name, unnest (sums) AS col_sum
FROM   f_get_sums('public', 'somereport');

Возвращает:

   name        | col_sum
---------------+---------
 int_col1      |    6614
 other_int_col |    8364
 third_int_col | 2720642

объяснять

Сложность состоит в том, чтобы определить RETURN введите для функции, а число и имена возвращаемых столбцов будут различаться. Одна деталь, которая немного помогает: вы хотите только integer колонны.

Я решил это путем формирования массива bigint (sum(int_col) возвращается bigint). Кроме того, я возвращаю массив имен столбцов. Оба отсортированы в алфавитном порядке по имени столбца.

В вызове функции я разделил эти массивы с unnest() прибытие в красивый формат отображается.

Динамически создаваемый и исполняемый запрос - сложная вещь. Не запутайтесь в нескольких слоях цитат. В основном у вас есть EXECUTE для этого требуется текстовый аргумент, содержащий запрос SQL. Этот текст, в свою очередь, предоставляется вторичным SQL-запросом, который строит строку запроса первичного запроса.

Если это слишком много сразу или plpgsql это довольно новое для вас, начните с этого связанного ответа, где я объясняю основы, касающиеся гораздо более простой функции, и даю ссылки на руководство по основным функциям.

Если производительность важна, запросите каталог Postgres напрямую (pg_catalog.pg_attributes) вместо использования стандартизированного (но медленно) information_schema.columns, Вот простой пример с pg_attributes,