F.64. tablefunc
Модуль tablefunc
содержит ряд функций, возвращающих таблицы (то есть, множества строк). Эти функции полезны и сами по себе, и как примеры написания на C функций, возвращающих наборы строк.
Данный модуль считается «доверенным», то есть его могут устанавливать обычные пользователи, имеющие право CREATE
в текущей базе данных.
F.64.1. Предоставляемые функции
Функции, предоставляемые модулем tablefunc
, перечислены в Таблице F.119.
Таблица F.119. Функции tablefunc
F.64.1.1. 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)
F.64.1.2. 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);
Этот запрос выдаст примерно такой результат:
<== столбцы значений ==> 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()
.
F.64.1.3. crosstabN
(text)
N
(text)crosstabN
(text sql)
Функции crosstab
являются примерами того, как можно создать собственные обёртки универсальной функции N
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. Например, если ваш источник данных выдаёт имена строк типа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;
F.64.1.4. 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);
При этом будет получен примерно такой результат:
<== столбцы значений ==> 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 для этой формы crosstab
называется crosstab_hash
.
F.64.1.5. 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
может вывести вложенное дерево, начиная с любой строки.
Параметры описаны в Таблице F.120.
Таблица F.120. Параметры 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» показывается путь по ключам, приведший к текущей строке. Ключи разделяются заданной строкой branch_delim
. Если выводить ветви не требуется, опустите параметр branch_delim
и столбец branch в списке выходных столбцов.
Если порядок потомков одного родителя имеет значение, добавьте параметр 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)
F.64.2. Автор
Джо Конвей (Joe Conway)