Можно ли совместить SOLR и NHibernate Search

Если вы еще не установили дополнительный модуль tablefunc , запустите эту команду один раз для каждой базы данных:

CREATE EXTENSION tablefunc;

Ответьте на вопрос

Очень базовое решение кросс-таблицы для вашего случая:

SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- more columns?

Особая трудность заключается в том, что в базовой таблице нет категории (cat) , Для базовой 1-параметрической формы мы можем просто предоставить фиктивный столбец с фиктивным значением, служащим в качестве категории. В любом случае значение игнорируется.

Это один из редких случаев, когда второй параметр для функции crosstab() не нужен, поскольку все значения NULL отображаются только в оборванных столбцах справа по определению этой проблемы. И порядок может быть определен значением .

Если бы у нас был фактический столбец категории с именами, определяющими порядок значений в результате, мы 'нужна 2-параметрическая форма crosstab(). Здесь я синтезирую столбец категории с помощью оконной функции row_number() , на базу crosstab() на:

SELECT * FROM crosstab(
   $$
   SELECT bar, val, feh
   FROM  (
      SELECT *, 'val' || row_number() OVER (PARTITION BY bar ORDER BY feh) AS val
      FROM tbl_org
      ) x
   ORDER BY 1, 2
   $$
 , $$VALUES ('val1'), ('val2'), ('val3')$$         -- more columns?
) AS ct (bar text, val1 int, val2 int, val3 int);  -- more columns?

. Остальное - мельница. Найдите больше объяснений и ссылок в этих близких ответах.

Основы: Прочитайте это, если вы не знакомы с функцией crosstab()!

• PostgreSQL Crosstab Query

Дополнительно:

• Повернуть в несколько столбцов с помощью Tablefunc
• Объединить таблицу и журнал изменений в виде в PostgreSQL

Правильно

Неправильно динамический

], но, поскольку @Clodoaldo прокомментировал . PLGsql трудно достичь динамических типов возврата. Но там есть пути вокруг него - с некоторыми ограничениями .

Поэтому, чтобы не усложнять остальные, я демонстрирую с помощью более простого :

CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
   ('A', 'val1', 10)
 , ('A', 'val2', 20)
 , ('B', 'val1', 3)
 , ('B', 'val2', 4)
 , ('C', 'val1', 5)
 , ('D', 'val3', 8)
 , ('D', 'val1', 6)
 , ('D', 'val2', 7);

Вызов:

SELECT * FROM crosstab('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2')
AS ct (row_name text, val1 int, val2 int, val3 int);

Возвраты:

 row_name | val1 | val2 | val3
----------+------+------+------
 A        | 10   | 20   |
 B        |  3   |  4   |
 C        |  5   |      |
 D        |  6   |  7   |  8

Встроенная функция модуля tablefunc

Модуль tablefunc предоставляет простую инфраструктуру для общих вызовов crosstab() без предоставления списка определения столбцов. Ряд функций, записанных в C (обычно очень быстрый):

crosstabN()

crosstab1() - crosstab4() предварительно определены. Один второстепенный момент: они требуют и возвращают все text. Поэтому нам нужно отбросить наши значения integer. Но это упрощает вызов:

SELECT * FROM crosstab4('SELECT row_name, attrib, val::text  -- cast!
                         FROM tbl ORDER BY 1,2')

Результат:

 row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
 A        | 10         | 20         |            |
 B        | 3          | 4          |            |
 C        | 5          |            |            |
 D        | 6          | 7          | 8          |

Пользовательская функция crosstab()

Для больше столбцов или других типов данных , мы создаем нашу собственную функцию композитного типа и (один раз). Тип:

CREATE TYPE tablefunc_crosstab_int_5 AS (
  row_name text, val1 int, val2 int, val3 int, val4 int, val5 int);

Функция:

CREATE OR REPLACE FUNCTION crosstab_int_5(text)
  RETURNS SETOF tablefunc_crosstab_int_5
AS '$libdir/tablefunc', 'crosstab' LANGUAGE c STABLE STRICT;

Вызов:

SELECT * FROM crosstab_int_5('SELECT row_name, attrib, val   -- no cast!
                              FROM tbl ORDER BY 1,2');

Результат:

 row_name | val1 | val2 | val3 | val4 | val5
----------+------+------+------+------+------
 A        |   10 |   20 |      |      |
 B        |    3 |    4 |      |      |
 C        |    5 |      |      |      |
 D        |    6 |    7 |    8 |      |

Одна полиморфная, динамическая функция для всех

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

• Рефакторинг функции PL / pgSQL для возврата вывода различных запросов SELECT

1-параметрическая форма:

CREATE OR REPLACE FUNCTION crosstab_n(_qry text, _rowtype anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
   (SELECT format('SELECT * FROM crosstab(%L) t(%s)'
                , _qry
                , string_agg(quote_ident(attname) || ' ' || atttypid::regtype
                           , ', ' ORDER BY attnum))
    FROM   pg_attribute
    WHERE  attrelid = pg_typeof(_rowtype)::text::regclass
    AND    attnum > 0
    AND    NOT attisdropped);
END
$func$  LANGUAGE plpgsql;

Перегрузка с этим вариантом для двухпараметрической формы:

CREATE OR REPLACE FUNCTION crosstab_n(_qry text, _cat_qry text, _rowtype anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
   (SELECT format('SELECT * FROM crosstab(%L, %L) t(%s)'
                , _qry, _cat_qry
                , string_agg(quote_ident(attname) || ' ' || atttypid::regtype
                           , ', ' ORDER BY attnum))
    FROM   pg_attribute
    WHERE  attrelid = pg_typeof(_rowtype)::text::regclass
    AND    attnum > 0
    AND    NOT attisdropped);
END
$func$  LANGUAGE plpgsql;

pg_typeof(_rowtype)::text::regclass: существует тип строки, определенный для каждого пользовательского составного типа, так что атрибуты (столбцы) перечислены в системном каталоге pg_attribute . Быстрая полоса для ее получения: введите зарегистрированный тип (regtype) в text и отбросите text на regclass.

Создайте композитные типы один раз:

Вы должны определить один раз каждый тип возвращаемого значения, который вы собираетесь использовать:

CREATE TYPE tablefunc_crosstab_int_3 AS (
    row_name text, val1 int, val2 int, val3 int);

CREATE TYPE tablefunc_crosstab_int_4 AS (
    row_name text, val1 int, val2 int, val3 int, val4 int);

...

Для специальных вызовов вы также можете просто создать временную таблицу для того же (временного) эффекта:

CREATE TEMP TABLE temp_xtype7 AS (
    row_name text, x1 int, x2 int, x3 int, x4 int, x5 int, x6 int, x7 int);

Или используйте тип существующей таблицы, представления или материализованного представления, если они доступны.

Вызов

Использование следующих типов строк:

1 -параметр (отсутствующие значения):

SELECT * FROM crosstab_n(
   'SELECT row_name, attrib, val FROM tbl ORDER BY 1,2'
 , NULL::tablefunc_crosstab_int_3);

2-параметрическая форма (некоторые значения могут отсутствовать):

SELECT * FROM crosstab_n(
   'SELECT row_name, attrib, val FROM tbl ORDER BY 1'
 , $$VALUES ('val1'), ('val2'), ('val3')$$
 , NULL::tablefunc_crosstab_int_3);

Эта одна функция работает для всех типов возврата, в то время как структура crosstabN(), предоставляемая модулем tablefunc, нуждается в отдельной функции для каждого. Если вы назвали свои типы последовательно, как показано выше, вам нужно заменить только жирный номер. Чтобы найти максимальное количество категорий в базовой таблице:

SELECT max(count(*)) OVER () FROM tbl  -- returns 3
GROUP  BY row_name
LIMIT  1;

Это примерно так же динамично, как это получается, если вы хотите отдельные столбцы . Такие массивы, как , продемонстрированные @Clocoaldo или простое текстовое представление, или результат, заключенный в тип документа, такой как json или hstore, может работать для любого количества категорий динамически.

Отказ от ответственности: это всегда потенциально опасно, когда пользовательский ввод преобразуется в код. Убедитесь, что это не может использоваться для SQL-инъекции. Не принимайте вход от ненадежных пользователей (напрямую).

Вызовите исходный вопрос:

SELECT * FROM crosstab_n('SELECT bar, 1, feh FROM tbl_org ORDER BY 1,2'
                       , NULL::tablefunc_crosstab_int_3);