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)