Postgresql 中文操作指南

F.43. tablefunc — functions that return tables (crosstab and others) #

tablefunc 模块包含各种返回表（即多行）的函数。这些函数既对自身有用，又作为编写返回多行的 C 函数的示例。

The tablefunc module includes various functions that return tables (that is, multiple rows). These functions are useful both in their own right and as examples of how to write C functions that return multiple rows.

此模块被认为是“受信任的”，也就是说，它可以由在当前数据库上具有 CREATE 权限的非超级用户安装。

This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.

F.43.1. Functions Provided #

Table F.32 概述了 tablefunc 模块提供的一些功能。

Table F.32 summarizes the functions provided by the tablefunc module.

Table F.32. tablefunc Functions

Table F.32. tablefunc Functions

Function

Description

normal_rand ( numvals integer, mean float8, stddev float8 ) → setof float8

Produces a set of normally distributed random values.

crosstab ( sql text ) → setof record

Produces a “pivot table” containing row names plus N value columns, where N is determined by the row type specified in the calling query.

crosstab_N ( _sql text ) → setof table_crosstabN

Produces a “pivot table” containing row names plus _N value columns. crosstab2, crosstab3, and crosstab4 are predefined, but you can create additional _crosstab_N functions as described below.

crosstab ( source_sql text, category_sql text ) → setof record

Produces a “pivot table” with the value columns specified by a second query.

crosstab ( sql text, N integer ) → setof record

Obsolete version of crosstab(text). The parameter N is now ignored, since the number of value columns is always determined by the calling query.

connectby ( relname text, keyid_fld text, parent_keyid_fld text [, orderby_fld text ], start_with text, max_depth integer [, branch_delim text ] ) → setof record

Produces a representation of a hierarchical tree structure.

F.43.1.1. normal_rand #

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

normal_rand 生成一组正态分布的随机值（高斯分布）。

normal_rand produces a set of normally distributed random values (Gaussian distribution).

numvals 是函数返回的值的数量。mean 是值的正态分布均值，stddev 是值的正态分布标准差。

numvals is the number of values to be returned from the function. mean is the mean of the normal distribution of values and stddev is the standard deviation of the normal distribution of values.

例如，此调用请求 1000 个均值为 5，标准差为 3 的值：

For example, this call requests 1000 values with a mean of 5 and a standard deviation of 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.43.1.2. crosstab(text) #

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

crosstab 函数用于生成“透视表”显示，其中数据横跨页面列出，而不是向下列出。例如，我们可能拥有如下数据：

The crosstab function is used to produce “pivot” displays, wherein data is listed across the page rather than down. For example, we might have data like

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

我们希望以下方式显示：

which we wish to display like

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

crosstab 函数需要一个文本参数，该参数是 SQL 查询，生成以第一种方式格式化的原始数据，并生成以第二种方式格式化的表。

The crosstab function takes a text parameter that is an SQL query producing raw data formatted in the first way, and produces a table formatted in the second way.

sql 参数是一个 SQL 语句，生成源数据集。此语句必须返回一个 row_name 列、一个 category 列和一个 value 列。N 是已过时参数，如果提供则忽略该参数（以前，该参数必须与输出值列的数量匹配，但现在由调用查询来确定该数量）。

The sql parameter is an SQL statement that produces the source set of data. This statement must return one row_name column, one category column, and one value column. N is an obsolete parameter, ignored if supplied (formerly this had to match the number of output value columns, but now that is determined by the calling query).

例如，提供的查询可能生成类似以下内容的集合：

For example, the provided query might produce a set something like:

 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，因此输出列的实际名称和类型必须在调用 SELECT 语句的 FROM 子句中定义，例如：

The crosstab function is declared to return setof record, so the actual names and types of the output columns must be defined in the FROM clause of the calling SELECT statement, for example:

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

此示例生成类似以下内容的集合：

This example produces a set something like:

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

FROM 子句必须将输出定义为一个 row_name 列（与 SQL 查询的第一结果列数据类型相同），后面跟 N 个 value 列（全部与 SQL 查询的第三个结果列数据类型相同）。您可以设置任意数量的输出值列。您决定输出列的名称。

The FROM clause must define the output as one row_name column (of the same data type as the first result column of the SQL query) followed by N value columns (all of the same data type as the third result column of the SQL query). You can set up as many output value columns as you wish. The names of the output columns are up to you.

crosstab 函数为具有相同 row_name 值的每组连续输入行生成一行输出。它用这些行的 value 字段从左到右填充输出 value 列。如果组中的行少于输出 value 列，则额外的输出列将用空值填充；如果有更多行，则会跳过额外的输入行。

The crosstab function produces one output row for each consecutive group of input rows with the same row_name value. It fills the output value columns, left to right, with the value fields from these rows. If there are fewer rows in a group than there are output value columns, the extra output columns are filled with nulls; if there are more rows, the extra input rows are skipped.

实际上，SQL 查询应始终指定 ORDER BY 1,2，以确保输入行按适当顺序排列，即，具有相同 row_name 的值会聚集在一起，并按行内的顺序进行排序。请注意，crosstab 本身不会关注查询结果的第二列；它只是在那里进行排序，以控制第三列值在页面中出现的顺序。

In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it’s just there to be ordered by, to control the order in which the third-column values appear across the page.

以下是一个完整的示例：

Here is a complete example:

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 子句来定义输出列。这在下一节中进行了描述。另一种可能性是在视图定义中嵌入必需的 FROM 子句。

You can avoid always having to write out a FROM clause to define the output columns, by setting up a custom crosstab function that has the desired output row type wired into its definition. This is described in the next section. Another possibility is to embed the required FROM clause in a view definition.

Note

另请参见 psql 中的 _ \crosstabview_ 命令，该命令提供了与 crosstab() 类似的功能。

See also the \crosstabview command in psql, which provides functionality similar to crosstab().

F.43.1.3. crosstab_N(text)_ #

crosstabN(text sql)

crosstab_N_ 函数是为通用 crosstab 函数设置自定义包装器的示例，这样您不必在调用 SELECT 查询中写出列名和类型。 tablefunc 模块包括 crosstab2 、 crosstab3 和 crosstab4 ，它们的输出行类型定义为

The crosstab_N_ functions are examples of how to set up custom wrappers for the general crosstab function, so that you need not write out column names and types in the calling SELECT query. The tablefunc module includes crosstab2, crosstab3, and crosstab4, whose output row types are defined as

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

因此，如果输入查询生成类型为 text 的 row_name 和 value 列，并且您想要 2、3 或 4 个输出值列，则可以直接使用这些函数。在其他所有方面，它们的行为与上面为通用 crosstab 函数所述的行为完全相同。

Thus, these functions can be used directly when the input query produces row_name and value columns of type text, and you want 2, 3, or 4 output values columns. In all other ways they behave exactly as described above for the general crosstab function.

例如，上一部分中给出的示例也将作为工作方式：

For instance, the example given in the previous section would also work as

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

这些函数主要是出于说明目的而提供的。你可以基于底层 crosstab() 函数创建自己的返回类型和函数。有两种实现方法：

These functions are provided mostly for illustration purposes. You can create your own return types and functions based on the underlying crosstab() function. There are two ways to do it:

F.43.1.4. crosstab(text, text) #

crosstab(text source_sql, text category_sql)

crosstab 单参数形式的主要限制在于它将组中的所有值视为相似的，将每个值插入到第一可用的列中。如果你希望值列对应于特定数据类别，并且某些组可能没有某些类别的对应数据，这种方法则不可行。crosstab 的两参数形式通过提供对应于输出列的类别显式列表来处理这种情况。

The main limitation of the single-parameter form of crosstab is that it treats all values in a group alike, inserting each value into the first available column. If you want the value columns to correspond to specific categories of data, and some groups might not have data for some of the categories, that doesn’t work well. The two-parameter form of crosstab handles this case by providing an explicit list of the categories corresponding to the output columns.

source_sql 是一条生成数据源集的 SQL 语句。该语句必须返回一个 row_name 列、一个 category 列和一个 value 列。它还可以有一个或多个“额外”列。row_name 列必须是第一个列。category 和 value 列按此顺序必须是最后两个列。row_name 和 category 之间的任何列都将被视为“额外”列。“额外”列对于具有相同 row_name 值的所有行来说都应该相同。

source_sql is an SQL statement that produces the source set of data. This statement must return one row_name column, one category column, and one value column. It may also have one or more “extra” columns. The row_name column must be first. The category and value columns must be the last two columns, in that order. Any columns between row_name and category are treated as “extra”. The “extra” columns are expected to be the same for all rows with the same row_name value.

例如，source_sql 可能会生成类似以下内容的集：

For example, source_sql might produce a set something like:

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 可能类似于以下内容：

category_sql is an SQL statement that produces the set of categories. This statement must return only one column. It must produce at least one row, or an error will be generated. Also, it must not produce duplicate values, or an error will be generated. category_sql might be something like:

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

crosstab 函数被声明为返回 setof record，因此输出列的实际名称和类型必须在调用 SELECT 语句的 FROM 子句中定义，例如：

The crosstab function is declared to return setof record, so the actual names and types of the output columns must be defined in the FROM clause of the calling SELECT statement, for example:

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

这将生成类似以下内容的结果：

This will produce a result something like:

                  <==  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 查询结果中的行数完全相同。

The FROM clause must define the proper number of output columns of the proper data types. If there are N columns in the source_sql query’s result, the first N-2 of them must match up with the first N-2 output columns. The remaining output columns must have the type of the last column of the source_sql query’s result, and there must be exactly as many of them as there are rows in the category_sql query’s result.

crosstab 函数为具有相同 row_name 值的每个连续组的输入行生成一行输出。输出 row_name 列以及任何“额外”列都将从该组的第一行中复制。输出 value 列使用具有匹配 category 值的行中的 value 字段填充。如果某行的 category 与 category_sql 查询的任何输出不匹配，那么其 value 就会被忽略。在组的任何输入行中都不存在其匹配类别的输出列都将填充为 null。

The crosstab function produces one output row for each consecutive group of input rows with the same row_name value. The output row_name column, plus any “extra” columns, are copied from the first row of the group. The output value columns are filled with the value fields from rows having matching category values. If a row’s category does not match any output of the category_sql query, its value is ignored. Output columns whose matching category is not present in any input row of the group are filled with nulls.

实际上，source_sql 查询应该始终指定 ORDER BY 1 以确保具有相同 row_name 的值被归置在一起。但是，一个组内类别的排序并不重要。此外，务必确保 category_sql 查询的输出与指定的输出列次序相匹配。

In practice the source_sql query should always specify ORDER BY 1 to ensure that values with the same row_name are brought together. However, ordering of the categories within a group is not important. Also, it is essential to be sure that the order of the category_sql query’s output matches the specified output column order.

以下是两个完整示例：

Here are two complete examples:

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)

你可以创建预定义函数以避免在每次查询中写出结果列的名称和类型。请参见上一部分中的示例。crosstab 这种形式的底层 C 函数被命名为 crosstab_hash。

You can create predefined functions to avoid having to write out the result column names and types in each query. See the examples in the previous section. The underlying C function for this form of crosstab is named crosstab_hash.

F.43.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 可以显示任何行开始的子树。

The connectby function produces a display of hierarchical data that is stored in a table. The table must have a key field that uniquely identifies rows, and a parent-key field that references the parent (if any) of each row. connectby can display the sub-tree descending from any row.

Table F.33 说明了一些参数。

Table F.33 explains the parameters.

Table F.33. connectby Parameters

Table F.33. connectby Parameters

Parameter

Description

relname

Name of the source relation

keyid_fld

Name of the key field

parent_keyid_fld

Name of the parent-key field

orderby_fld

Name of the field to order siblings by (optional)

start_with

Key value of the row to start at

max_depth

Maximum depth to descend to, or zero for unlimited depth

branch_delim

String to separate keys with in branch output (optional)

键和父键字段可以是任何数据类型，但它们必须是相同类型。请注意，无论键字段的类型如何，都必须把 start_with 值作为一个文本字符串输入。

The key and parent-key fields can be any data type, but they must be the same type. Note that the start_with value must be entered as a text string, regardless of the type of the key field.

connectby 函数被声明为返回 setof record，因此必须在调用 SELECT 语句的 FROM 子句中定义输出列的实际名称和类型，例如：

The connectby function is declared to return setof record, so the actual names and types of the output columns must be defined in the FROM clause of the calling SELECT statement, for example:

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。

The first two output columns are used for the current row’s key and its parent row’s key; they must match the type of the table’s key field. The third output column is the depth in the tree and must be of type integer. If a branch_delim parameter was given, the next output column is the branch display and must be of type text. Finally, if an orderby_fld parameter was given, the last output column is a serial number, and must be of type integer.

“分支”输出列显示了达到当前行的键路径。这些键使用指定的 branch_delim 字符串分隔。如果不需要分支显示，请同时在输出列列表中省略 branch_delim 参数和分支列。

The “branch” output column shows the path of keys taken to reach the current row. The keys are separated by the specified branch_delim string. If no branch display is wanted, omit both the branch_delim parameter and the branch column in the output column list.

如果相同父级关系兄弟姐妹的顺序很重要，请使用 orderby_fld 参数指定依据哪个字段对兄弟姐妹进行排序。这个字段可以是任何可排序的数据类型。输出列列表一定包括一个最终的整数序列号列，且仅在指定了 orderby_fld 时才如此。

If the ordering of siblings of the same parent is important, include the orderby_fld parameter to specify which field to order siblings by. This field can be of any sortable data type. The output column list must include a final integer serial-number column, if and only if orderby_fld is specified.

表示表和字段名称的参数在 connectby 内部生成时会原样复制到 SQL 查询中。因此，如果这些名称使用混合大小写或包含特殊字符，请包含双引号。你可能还需要对表名进行模式限定。

The parameters representing table and field names are copied as-is into the SQL queries that connectby generates internally. Therefore, include double quotes if the names are mixed-case or contain special characters. You may also need to schema-qualify the table name.

在大表中，除非在父键字段上存在索引，否则性能会很差。

In large tables, performance will be poor unless there is an index on the parent-key field.

重要的是 branch_delim 字符串不会出现在任何键值中，否则 connectby 可能会错误地报告一个无限递归错误。请注意，如果未提供 branch_delim，则会使用 ~ 的默认值以进行递归检测。

It is important that the branch_delim string not appear in any key values, else connectby may incorrectly report an infinite-recursion error. Note that if branch_delim is not provided, a default value of ~ is used for recursion detection purposes.

这是一个示例：

Here is an example:

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);

-- with branch, without orderby_fld (order of results is not guaranteed)
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)

-- without branch, without orderby_fld (order of results is not guaranteed)
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)

-- with branch, with orderby_fld (notice that row5 comes before 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)

-- without branch, with orderby_fld (notice that row5 comes before 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.43.2. Author #

乔·康威

Joe Conway