tablefunc

Модуль tablefunc содержит ряд функций, возвращающих таблицы (то есть множества строк). Эти функции полезны и сами по себе, и как примеры написания функций на C/RUST, возвращающих множество строк.

Этот модуль считается «доверенным», то есть его могут устанавливать обычные пользователи с правом CREATE в текущей базе данных.

---

---

Предоставляемые функции

Функции, предоставляемые модулем tablefunc, перечислены в Таблице 31.

Таблица 31. Функции tablefunc

Функция
	Описание
normal_rand ( numvals integer, mean float8, stddev float8 ) → setof float8
	Выдает набор случайных значений с нормальным распределением.
crosstab ( sql text ) → setof record
	Выдает «повернутую таблицу», содержащую имена строк плюс N столбцов значений, где N определяется типом строк, заданным в вызывающем запросе.
crosstabN ( sql text ) → setof table_crosstab_N
	Выдает «повернутую таблицу», содержащую имена строк плюс N столбцов значений. Функции crosstab2, crosstab3 и crosstab4 предопределены, но можно создать дополнительные функции crosstabN, как описано ниже.
crosstab ( source_sql text, category_sql text ) → setof record
	Выдает «повернутую таблицу» со столбцами значений, заданными вторым запросом.
crosstab ( sql text, N integer ) → setof record
	Устаревшая версия crosstab(text). Параметр N теперь игнорируется, поскольку число столбцов всегда определяется вызывающим запросом.
connectby ( relname text, keyid_fld text, parent_keyid_fld text [, orderby_fld text ], start_with text, max_depth integer [, branch_delim text ] ) → setof record
	Выдает представление структуры дерева иерархии.

---

normal_rand

normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8

Функция normal_rand выдает набор случайных значений с нормальным распределением (распределение Гаусса).

Параметр numvals представляет собой количество значений, возвращаемых этой функцией. Параметр mean — это среднее (математическое ожидание) нормального распределения значений, а параметр stddev — стандартное отклонение нормального распределения значений.

Например, этот вызов запрашивает 1000 значений со средним 5 и стандартным отклонением 3:

test=# SELECT * FROM normal_rand(1000, 5, 3);
     normal_rand
----------------------
     1.56556322244898
     9.10040991424657
     5.36957140345079
   -0.369151492880995
    0.283600703686639
       .
       .
       .
     4.82992125404908
     9.71308014517282
     2.49639286969028
(1000 rows)

---

crosstab(text)

crosstab(text sql)
crosstab(text sql, int N)

Функция crosstab используется для создания «повернутых» отображений, в которых данные записываются вдоль строк, а не сверху вниз. Например, у нас могут быть данные вроде таких:

row1    val11
row1    val12
row1    val13
...
row2    val21
row2    val22
row2    val23
...

которые мы хотим отобразить как

row1    val11   val12   val13   ...
row2    val21   val22   val23   ...
...

Функция crosstab принимает в текстовом параметре запрос SQL, выдающий исходные данные, отформатированные первым способом, и выдает таблицу, отформатированную вторым способом.

Параметр sql представляет собой оператор SQL, выдающий исходный набор данных. Этот оператор должен возвращать один столбец row_name, один столбец category и один столбец value. Параметр N является устаревшим и игнорируется, если передается при вызове (раньше он должен был соответствовать числу выходных столбцов значений, но теперь это число определяется вызывающим запросом).

Например, приведенный запрос может выдавать результат вроде этого:

 row_name    cat    value
----------+-------+-------
  row1      cat1    val1
  row1      cat2    val2
  row1      cat3    val3
  row1      cat4    val4
  row2      cat1    val5
  row2      cat2    val6
  row2      cat3    val7
  row2      cat4    val8

Функция crosstab объявлена как возвращающая setof record, поэтому фактические имена и типы выходных столбцов должны определяться в предложении FROM вызывающего оператора SELECT, например так:

SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);

В этом примере выдается приблизительно такой результат:

           <== value  columns  ==>
 row_name   category_1   category_2
----------+------------+------------
  row1        val1         val2
  row2        val5         val6

Предложение FROM должно определять выводимые данные как один столбец row_name (с тем же типом данных, что и у первого результирующего столбца запроса SQL), за которым следуют N столбцов значений (все с тем же типом данных, что и третий результирующий столбец запроса SQL). Можно задать любое желаемое количество выходных столбцов значений, и имена выходных столбцов также определяете вы сами.

Функция crosstab выдает одну выходную строку для каждой последовательной группы входных строк с одним значением row_name. Она заполняет выходные столбцы значений слева направо полями value из этих строк. Если в группе оказывается меньше строк, чем выходных столбцов значений, лишние столбцы заполняются значениями NULL; если же строк оказывается больше, лишние строки игнорируются.

На практике в запросе SQL всегда следует указывать ORDER BY 1,2, чтобы входные строки были отсортированы должным образом, то есть чтобы значения с одинаковым row_name собирались вместе и правильно упорядочивались в строке. Заметьте, что сама crosstab не обращает никакого внимания на второй столбец в результате запроса; он присутствует только для упорядочивания, чтобы определять порядок, в котором значения третьего столбца будут находиться в строке.

Вот полный пример:

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');

SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

 row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
 test1    | val2       | val3       |
 test2    | val6       | val7       |
(2 rows)

Можно избежать необходимости постоянно писать предложение FROM для определения выходных столбцов, собрав функцию crosstab под себя и зашив в ее определение желаемый тип выходной строки. Это описывается в следующем подразделе. Еще один вариант — включить требуемое предложение FROM в определение представления.

Примечание
Также изучите команду \crosstabview в psql, предоставляющую функциональность, схожую с crosstab().

---

crosstabN(text)

crosstabN(text sql)

Функции crosstabN являются примерами того, как можно создать собственные обертки универсальной функции crosstab, чтобы не приходилось писать имена и типы столбцов в вызывающем запросе SELECT. Модуль tablefunc содержит функции crosstab2, crosstab3 и crosstab4, у которых типы выходных строк определяются как

CREATE TYPE tablefunc_crosstab_N AS (
    row_name TEXT,
    category_1 TEXT,
    category_2 TEXT,
        .
        .
        .
    category_N TEXT
);

Таким образом, эти функции могут применяться непосредственно, когда входной запрос выдает столбцы row_name и value типа text и хочется получить 2, 3 или 4 выходных столбца значений. В остальном эти функции ведут себя точно так же, как описанная выше универсальная функция crosstab.

Так, пример, приведенный в предыдущем подразделе, сработает и в таком виде:

SELECT *
FROM crosstab3(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2');

Эти функции предоставлены в основном в демонстрационных целях. Вы можете создать свои типы возвращаемых данных и функции на базе нижележащей функции crosstab(). Существует два способа это сделать:

• Создать составной тип, описывающий желаемые выходные столбцы, подобно тому, как это делается в примерах в contrib/tablefunc/tablefunc--1.0.sql/. Затем определить уникальное имя для функции, принимающей один параметр text и возвращающей setof имя_вашего_типа, но связать его с той же нижележащей функцией crosstab на C/RUST. Например, если ваши исходные данные выдают имена строк типа text и значения типа float8, и вы хотите получить 5 столбцов значений:

  CREATE TYPE my_crosstab_float8_5_cols AS (
      my_row_name text,
      my_category_1 float8,
      my_category_2 float8,
      my_category_3 float8,
      my_category_4 float8,
      my_category_5 float8
  );
  
  CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
      RETURNS setof my_crosstab_float8_5_cols
      AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
  

• Использовать выходные параметры (OUT), чтобы явно определить возвращаемый тип. Тот же пример можно реализовать и таким способом:

  CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(
      IN text,
      OUT my_row_name text,
      OUT my_category_1 float8,
      OUT my_category_2 float8,
      OUT my_category_3 float8,
      OUT my_category_4 float8,
      OUT my_category_5 float8)
    RETURNS setof record
    AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
  

---

crosstab(text, text)

crosstab(text source_sql, text category_sql)

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

Параметр source_sql — это оператор SQL, выдающий исходный набор данных. Этот оператор должен возвращать один столбец row_name, один столбец category и один столбец value. Также он может выдавать один и более «дополнительных» столбцов. Столбец row_name должен быть первым. Столбцы category и value должны быть последними двумя и именно в таком порядке. Все столбцы между row_name и category воспринимаются «дополнительные». Ожидается, что «дополнительные» столбцы будут содержать одинаковые значения для всех строк с одним значением row_name.

Например, source_sql может выдавать набор данных вроде этого:

SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;

 row_name    extra_col   cat    value
----------+------------+-----+---------
  row1         extra1    cat1    val1
  row1         extra1    cat2    val2
  row1         extra1    cat4    val4
  row2         extra2    cat1    val5
  row2         extra2    cat2    val6
  row2         extra2    cat3    val7
  row2         extra2    cat4    val8

Параметр category_sql — это оператор SQL, выдающий набор категорий. Этот оператор должен возвращать только один столбец. Он должен выдать как минимум одну строку, иначе произойдет ошибка. Кроме того, он не должен выдавать повторяющиеся значения, так как это тоже вызовет ошибку. Например, category_sql может выглядеть так:

SELECT DISTINCT cat FROM foo ORDER BY 1;
    cat
  -------
    cat1
    cat2
    cat3
    cat4

Функция crosstab объявлена как возвращающая тип setof record, поэтому фактические имена и типы выходных столбцов должны определяться в предложении FROM вызывающего оператора SELECT, например:

SELECT * FROM crosstab('...', '...')
    AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);

При этом будет получен примерно такой результат:

                  <==  value  columns   ==>
row_name   extra   cat1   cat2   cat3   cat4
---------+-------+------+------+------+------
  row1     extra1  val1   val2          val4
  row2     extra2  val5   val6   val7   val8

В предложении FROM должно определяться требуемое количество выходных столбцов надлежащих типов данных. Если в результате запроса source_sql содержится N столбцов, первые N-2 из них должны соответствовать первым N-2 выходным столбцам. Оставшиеся выходные столбцы должны иметь тип последнего столбца результата запроса source_sql и их должно быть ровно столько, сколько строк содержится в результате запроса category_sql.

Функция crosstab выдает по одной выходной строке для каждой последовательной группы входных строк с одинаковым значением row_name. Выходной столбец row_name плюс все «дополнительные» столбцы копируются из первой строки группы. Выходные столбцы значений заполняются содержимым полей value из строк с соответствующими значениями category. Если значение поля category не соответствует ни одному выходному значению запроса category_sql, содержимое поля value в этой строке игнорируется. Выходные столбцы, для которых соответствующая категория не представлена ни в одной входной строке группы, заполняются значениями NULL.

На практике в запросе source_sql всегда следует указывать ORDER BY 1, чтобы все значения с одинаковым row_name гарантированно выводились вместе. При этом порядок категорий внутри группы не важен. Кроме того, крайне важно, чтобы порядок выходных данных запроса category_sql соответствовал заданному порядку выходных столбцов.

Ниже приводится два законченных примера:

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
(2 rows)

CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');

SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS
(
       rowid text,
       rowdt timestamp,
       temperature int4,
       test_result text,
       test_startdate timestamp,
       volts float8
);
 rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
-------+--------------------------+-------------+-------------+--------------------------+--------
 test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
 test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
(2 rows)

Чтобы не выписывать в каждом запросе имена и типы результирующих столбцов, можно создать предопределенные функции. См. примеры в предыдущем подразделе. Нижележащая функция на C/RUST для этой формы crosstab называется crosstab_hash.

---

connectby

connectby(text relname, text keyid_fld, text parent_keyid_fld
          [, text orderby_fld ], text start_with, int max_depth
          [, text branch_delim ])

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

Параметры функции описаны в Таблице 29.

Таблица 32. Параметры connectby

Параметр	Описание
relname	Имя исходного отношения
keyid_fld	Имя поля ключа
parent_keyid_fld	Имя поля ключа родителя
orderby_fld	Имя поля, по которому сортируются потомки (необязательно)
start_with	Значение ключа начальной строки
max_depth	Максимальная глубина, на которую можно погрузиться, или ноль для неограниченной глубины
branch_delim	Строка, разделяющая ключи в выводе ветви (необязательно)

Поля ключа и ключа родителя могут быть любого типа, но он у них должен быть одинаковым. Обратите внимание, что значение start_with должно задаваться текстовой строкой, независимо от типа поля ключа.

Функция connectby объявлена как возвращающая setof record, поэтому фактические имена и типы выходных столбцов должны определяться в предложении FROM вызывающего оператора SELECT, например:

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
    AS t(keyid text, parent_keyid text, level int, branch text, pos int);

Первые два выходных столбца используются для вывода ключа текущей строки и ключа ее родительской строки; их тип должен соответствовать типу поля ключа таблицы. В третьем выходном столбце выводится глубина в дереве, и он должен иметь тип integer. Если передается параметр branch_delim, в следующем выходном столбце отображаются ветви, и он должен иметь тип text. Наконец, если передается параметр orderby_fld, в последнем выходном столбце выводится порядковый номер, и он должен иметь тип integer.

В выходном столбце «ветви» показывается путь, приведший ключи к текущей строке. Ключи разделяются заданной строкой branch_delim. Если выводить ветви не требуется, опустите параметр branch_delim и столбец ветви в списке выходных столбцов.

Если порядок потомков одного родителя важен, добавьте параметр orderby_fld для указания поля, по которому будут упорядочены потомки. Это поле может иметь любой сортируемый тип данных. Последним в списке выходных столбцов должен идти целочисленный столбец с порядковыми номерами, если и только если передается параметр orderby_fld.

Параметры, представляющие имена таблицы и полей, копируются как есть в запросы SQL, которые connectby генерирует внутри. Поэтому их следует заключить в кавычки, если они содержат буквы в разном регистре или специальные символы. Также может понадобиться дополнить имя таблицы схемой.

С большими таблицами производительность будет низкой, если не создать индекс по полю с ключом родителя.

Важно, чтобы строка branch_delim не содержалась в значениях ключа, иначе connectby может некорректно сообщить об ошибке бесконечной вложенности. Обратите внимание, что если branch_delim не передается, для выявления рекурсии используется символ по умолчанию ~.

Пример:

CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);

INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);

-- с ветвями, без orderby_fld (порядок результатов не гарантируется)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text);
 keyid | parent_keyid | level |       branch
-------+--------------+-------+---------------------
 row2  |              |     0 | row2
 row4  | row2         |     1 | row2~row4
 row6  | row4         |     2 | row2~row4~row6
 row8  | row6         |     3 | row2~row4~row6~row8
 row5  | row2         |     1 | row2~row5
 row9  | row5         |     2 | row2~row5~row9
(6 rows)

-- без ветвей и без orderby_fld (порядок результатов не гарантируется)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int);
 keyid | parent_keyid | level
-------+--------------+-------
 row2  |              |     0
 row4  | row2         |     1
 row6  | row4         |     2
 row8  | row6         |     3
 row5  | row2         |     1
 row9  | row5         |     2
(6 rows)

-- с ветвями и с orderby_fld (обратите внимание, что row5 идет перед row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text, pos int);
 keyid | parent_keyid | level |       branch        | pos
-------+--------------+-------+---------------------+-----
 row2  |              |     0 | row2                |   1
 row5  | row2         |     1 | row2~row5           |   2
 row9  | row5         |     2 | row2~row5~row9      |   3
 row4  | row2         |     1 | row2~row4           |   4
 row6  | row4         |     2 | row2~row4~row6      |   5
 row8  | row6         |     3 | row2~row4~row6~row8 |   6
(6 rows)

-- без ветвей, с orderby_fld (обратите внимание, что row5 идет перед row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int, pos int);
 keyid | parent_keyid | level | pos
-------+--------------+-------+-----
 row2  |              |     0 |   1
 row5  | row2         |     1 |   2
 row9  | row5         |     2 |   3
 row4  | row2         |     1 |   4
 row6  | row4         |     2 |   5
 row8  | row6         |     3 |   6
(6 rows)

---

Автор

Джо Конвэй (Joe Conway)