Документация по PostgreSQL 9.4.1 | |||
---|---|---|---|
Пред. | Уровень выше | Приложение E. Дополнительно поставляемые модули | След. |
E.37. tablefunc
Модуль tablefunc содержит ряд функций, возвращающих таблицы (то есть, множества строк). Эти функции полезны и сами по себе, и как примеры написания на C функций, возвращающих наборы строк.
E.37.1. Предоставляемые функции
Функции, предоставляемые модулем tablefunc, перечислены в Таблице E-28.
Таблица E-28. Функции tablefunc
Функция | Возвращает | Описание |
---|---|---|
normal_rand(int numvals, float8 mean, float8 stddev) | setof float8 | Выдаёт набор случайных значений, имеющих нормальное распределение |
crosstab(text sql) | setof record | Выдаёт "повёрнутую таблицу", содержащую имена строк плюс N колонок значений, где N определяется видом строк, заданным в вызывающем запросе |
crosstabN(text sql) | setof table_crosstab_N | Выдаёт "повёрнутую таблицу", содержащую имена строк плюс N колонок значений. Функции crosstab2 , crosstab3 и crosstab4 предопределены, но вы можете создать дополнительные функции crosstabN , как описано ниже |
crosstab(text source_sql, text category_sql) | setof record | Выдаёт "повёрнутую таблицу" с колонками значений, заданными вторым запросом |
crosstab(text sql, int N) | setof record | Устаревшая версия |
connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ]) | setof record | Выдаёт представление иерархической древовидной структуры |
E.37.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)
E.37.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 в определение представления.
E.37.1.3. 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. Например, если ваш источник данных выдаёт имена строк типа 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;
E.37.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.
E.37.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
может вывести вложенное дерево, начиная с любой строки.
Параметры описаны в Таблице E-29.
Таблица E-29. Параметры 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)
E.37.2. Автор
Джо Конвей
Пред. | Начало | След. |
sslinfo | Уровень выше | tcn |