summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/tablefunc.sgml
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--doc/src/sgml/tablefunc.sgml865
1 files changed, 865 insertions, 0 deletions
diff --git a/doc/src/sgml/tablefunc.sgml b/doc/src/sgml/tablefunc.sgml
new file mode 100644
index 0000000..e10fe70
--- /dev/null
+++ b/doc/src/sgml/tablefunc.sgml
@@ -0,0 +1,865 @@
+<!-- doc/src/sgml/tablefunc.sgml -->
+
+<sect1 id="tablefunc" xreflabel="tablefunc">
+ <title>tablefunc &mdash; functions that return tables (<function>crosstab</function> and others)</title>
+
+ <indexterm zone="tablefunc">
+ <primary>tablefunc</primary>
+ </indexterm>
+
+ <para>
+ The <filename>tablefunc</filename> 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.
+ </para>
+
+ <para>
+ This module is considered <quote>trusted</quote>, that is, it can be
+ installed by non-superusers who have <literal>CREATE</literal> privilege
+ on the current database.
+ </para>
+
+ <sect2 id="tablefunc-functions-sect">
+ <title>Functions Provided</title>
+
+ <para>
+ <xref linkend="tablefunc-functions"/> summarizes the functions provided
+ by the <filename>tablefunc</filename> module.
+ </para>
+
+ <table id="tablefunc-functions">
+ <title><filename>tablefunc</filename> Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>normal_rand</function> ( <parameter>numvals</parameter> <type>integer</type>, <parameter>mean</parameter> <type>float8</type>, <parameter>stddev</parameter> <type>float8</type> )
+ <returnvalue>setof float8</returnvalue>
+ </para>
+ <para>
+ Produces a set of normally distributed random values.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>crosstab</function> ( <parameter>sql</parameter> <type>text</type> )
+ <returnvalue>setof record</returnvalue>
+ </para>
+ <para>
+ Produces a <quote>pivot table</quote> containing
+ row names plus <replaceable>N</replaceable> value columns, where
+ <replaceable>N</replaceable> is determined by the row type specified
+ in the calling query.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>crosstab<replaceable>N</replaceable></function> ( <parameter>sql</parameter> <type>text</type> )
+ <returnvalue>setof table_crosstab_<replaceable>N</replaceable></returnvalue>
+ </para>
+ <para>
+ Produces a <quote>pivot table</quote> containing
+ row names plus <replaceable>N</replaceable> value columns.
+ <function>crosstab2</function>, <function>crosstab3</function>, and
+ <function>crosstab4</function> are predefined, but you can create additional
+ <function>crosstab<replaceable>N</replaceable></function> functions as described below.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>crosstab</function> ( <parameter>source_sql</parameter> <type>text</type>, <parameter>category_sql</parameter> <type>text</type> )
+ <returnvalue>setof record</returnvalue>
+ </para>
+ <para>
+ Produces a <quote>pivot table</quote>
+ with the value columns specified by a second query.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>crosstab</function> ( <parameter>sql</parameter> <type>text</type>, <parameter>N</parameter> <type>integer</type> )
+ <returnvalue>setof record</returnvalue>
+ </para>
+ <para>
+ Obsolete version of <function>crosstab(text)</function>.
+ The parameter <parameter>N</parameter> is now ignored, since the
+ number of value columns is always determined by the calling query.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>connectby</primary></indexterm>
+ <function>connectby</function> ( <parameter>relname</parameter> <type>text</type>, <parameter>keyid_fld</parameter> <type>text</type>, <parameter>parent_keyid_fld</parameter> <type>text</type>
+ <optional>, <parameter>orderby_fld</parameter> <type>text</type> </optional>, <parameter>start_with</parameter> <type>text</type>, <parameter>max_depth</parameter> <type>integer</type>
+ <optional>, <parameter>branch_delim</parameter> <type>text</type> </optional> )
+ <returnvalue>setof record</returnvalue>
+ </para>
+ <para>
+ Produces a representation of a hierarchical tree structure.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <sect3 id="tablefunc-functions-normal-rand">
+ <title><function>normal_rand</function></title>
+
+ <indexterm>
+ <primary>normal_rand</primary>
+ </indexterm>
+
+<synopsis>
+normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8
+</synopsis>
+
+ <para>
+ <function>normal_rand</function> produces a set of normally distributed random
+ values (Gaussian distribution).
+ </para>
+
+ <para>
+ <parameter>numvals</parameter> is the number of values to be returned
+ from the function. <parameter>mean</parameter> is the mean of the normal
+ distribution of values and <parameter>stddev</parameter> is the standard
+ deviation of the normal distribution of values.
+ </para>
+
+ <para>
+ For example, this call requests 1000 values with a mean of 5 and a
+ standard deviation of 3:
+ </para>
+
+<screen>
+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)
+</screen>
+ </sect3>
+
+ <sect3 id="tablefunc-functions-crosstab-text">
+ <title><function>crosstab(text)</function></title>
+
+ <indexterm>
+ <primary>crosstab</primary>
+ </indexterm>
+
+<synopsis>
+crosstab(text sql)
+crosstab(text sql, int N)
+</synopsis>
+
+ <para>
+ The <function>crosstab</function> function is used to produce <quote>pivot</quote>
+ displays, wherein data is listed across the page rather than down.
+ For example, we might have data like
+<programlisting>
+row1 val11
+row1 val12
+row1 val13
+...
+row2 val21
+row2 val22
+row2 val23
+...
+</programlisting>
+ which we wish to display like
+<programlisting>
+row1 val11 val12 val13 ...
+row2 val21 val22 val23 ...
+...
+</programlisting>
+ The <function>crosstab</function> 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.
+ </para>
+
+ <para>
+ The <parameter>sql</parameter> parameter is an SQL statement that produces
+ the source set of data. This statement must return one
+ <structfield>row_name</structfield> column, one
+ <structfield>category</structfield> column, and one
+ <structfield>value</structfield> column. <parameter>N</parameter> 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).
+ </para>
+
+ <para>
+ For example, the provided query might produce a set something like:
+<programlisting>
+ 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
+</programlisting>
+ </para>
+
+ <para>
+ The <function>crosstab</function> function is declared to return <type>setof
+ record</type>, so the actual names and types of the output columns must be
+ defined in the <literal>FROM</literal> clause of the calling <command>SELECT</command>
+ statement, for example:
+<programlisting>
+SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);
+</programlisting>
+ This example produces a set something like:
+<programlisting>
+ &lt;== value columns ==&gt;
+ row_name category_1 category_2
+----------+------------+------------
+ row1 val1 val2
+ row2 val5 val6
+</programlisting>
+ </para>
+
+ <para>
+ The <literal>FROM</literal> clause must define the output as one
+ <structfield>row_name</structfield> column (of the same data type as the first result
+ column of the SQL query) followed by N <structfield>value</structfield> 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.
+ </para>
+
+ <para>
+ The <function>crosstab</function> function produces one output row for each
+ consecutive group of input rows with the same
+ <structfield>row_name</structfield> value. It fills the output
+ <structfield>value</structfield> columns, left to right, with the
+ <structfield>value</structfield> fields from these rows. If there
+ are fewer rows in a group than there are output <structfield>value</structfield>
+ columns, the extra output columns are filled with nulls; if there are
+ more rows, the extra input rows are skipped.
+ </para>
+
+ <para>
+ In practice the SQL query should always specify <literal>ORDER BY 1,2</literal>
+ to ensure that the input rows are properly ordered, that is, values with
+ the same <structfield>row_name</structfield> are brought together and
+ correctly ordered within the row. Notice that <function>crosstab</function>
+ 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.
+ </para>
+
+ <para>
+ Here is a complete example:
+<programlisting>
+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)
+</programlisting>
+ </para>
+
+ <para>
+ You can avoid always having to write out a <literal>FROM</literal> 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 <literal>FROM</literal> clause in a view definition.
+ </para>
+
+ <note>
+ <para>
+ See also the <command><link linkend="app-psql-meta-commands-crosstabview">\crosstabview</link></command>
+ command in <application>psql</application>, which provides functionality similar
+ to <function>crosstab()</function>.
+ </para>
+ </note>
+
+ </sect3>
+
+ <sect3 id="tablefunc-functions-crosstab-n-text">
+ <title><function>crosstab<replaceable>N</replaceable>(text)</function></title>
+
+ <indexterm>
+ <primary>crosstab</primary>
+ </indexterm>
+
+<synopsis>
+crosstab<replaceable>N</replaceable>(text sql)
+</synopsis>
+
+ <para>
+ The <function>crosstab<replaceable>N</replaceable></function> functions are examples of how
+ to set up custom wrappers for the general <function>crosstab</function> function,
+ so that you need not write out column names and types in the calling
+ <command>SELECT</command> query. The <filename>tablefunc</filename> module includes
+ <function>crosstab2</function>, <function>crosstab3</function>, and
+ <function>crosstab4</function>, whose output row types are defined as
+ </para>
+
+<programlisting>
+CREATE TYPE tablefunc_crosstab_N AS (
+ row_name TEXT,
+ category_1 TEXT,
+ category_2 TEXT,
+ .
+ .
+ .
+ category_N TEXT
+);
+</programlisting>
+
+ <para>
+ Thus, these functions can be used directly when the input query produces
+ <structfield>row_name</structfield> and <structfield>value</structfield> columns of type
+ <type>text</type>, and you want 2, 3, or 4 output values columns.
+ In all other ways they behave exactly as described above for the
+ general <function>crosstab</function> function.
+ </para>
+
+ <para>
+ For instance, the example given in the previous section would also
+ work as
+<programlisting>
+SELECT *
+FROM crosstab3(
+ 'select rowid, attribute, value
+ from ct
+ where attribute = ''att2'' or attribute = ''att3''
+ order by 1,2');
+</programlisting>
+ </para>
+
+ <para>
+ These functions are provided mostly for illustration purposes. You
+ can create your own return types and functions based on the
+ underlying <function>crosstab()</function> function. There are two ways
+ to do it:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create a composite type describing the desired output columns,
+ similar to the examples in
+ <filename>contrib/tablefunc/tablefunc--1.0.sql</filename>.
+ Then define a
+ unique function name accepting one <type>text</type> parameter and returning
+ <type>setof your_type_name</type>, but linking to the same underlying
+ <function>crosstab</function> C function. For example, if your source data
+ produces row names that are <type>text</type>, and values that are
+ <type>float8</type>, and you want 5 value columns:
+<programlisting>
+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;
+</programlisting>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Use <literal>OUT</literal> parameters to define the return type implicitly.
+ The same example could also be done this way:
+<programlisting>
+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;
+</programlisting>
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ </sect3>
+
+ <sect3 id="tablefunc-functions-crosstab-text-2">
+ <title><function>crosstab(text, text)</function></title>
+
+ <indexterm>
+ <primary>crosstab</primary>
+ </indexterm>
+
+<synopsis>
+crosstab(text source_sql, text category_sql)
+</synopsis>
+
+ <para>
+ The main limitation of the single-parameter form of <function>crosstab</function>
+ 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 <function>crosstab</function> handles this case by
+ providing an explicit list of the categories corresponding to the
+ output columns.
+ </para>
+
+ <para>
+ <parameter>source_sql</parameter> is an SQL statement that produces the
+ source set of data. This statement must return one
+ <structfield>row_name</structfield> column, one
+ <structfield>category</structfield> column, and one
+ <structfield>value</structfield> column. It may also have one or more
+ <quote>extra</quote> columns.
+ The <structfield>row_name</structfield> column must be first. The
+ <structfield>category</structfield> and <structfield>value</structfield>
+ columns must be the last two columns, in that order. Any columns between
+ <structfield>row_name</structfield> and
+ <structfield>category</structfield> are treated as <quote>extra</quote>.
+ The <quote>extra</quote> columns are expected to be the same for all rows
+ with the same <structfield>row_name</structfield> value.
+ </para>
+
+ <para>
+ For example, <parameter>source_sql</parameter> might produce a set
+ something like:
+<programlisting>
+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
+</programlisting>
+ </para>
+
+ <para>
+ <parameter>category_sql</parameter> 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. <parameter>category_sql</parameter> might be something like:
+
+<programlisting>
+SELECT DISTINCT cat FROM foo ORDER BY 1;
+ cat
+ -------
+ cat1
+ cat2
+ cat3
+ cat4
+</programlisting>
+ </para>
+
+ <para>
+ The <function>crosstab</function> function is declared to return <type>setof
+ record</type>, so the actual names and types of the output columns must be
+ defined in the <literal>FROM</literal> clause of the calling <command>SELECT</command>
+ statement, for example:
+
+<programlisting>
+SELECT * FROM crosstab('...', '...')
+ AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);
+</programlisting>
+ </para>
+
+ <para>
+ This will produce a result something like:
+<programlisting>
+ &lt;== value columns ==&gt;
+row_name extra cat1 cat2 cat3 cat4
+---------+-------+------+------+------+------
+ row1 extra1 val1 val2 val4
+ row2 extra2 val5 val6 val7 val8
+</programlisting>
+ </para>
+
+ <para>
+ The <literal>FROM</literal> clause must define the proper number of output
+ columns of the proper data types. If there are <replaceable>N</replaceable>
+ columns in the <parameter>source_sql</parameter> query's result, the first
+ <replaceable>N</replaceable>-2 of them must match up with the first
+ <replaceable>N</replaceable>-2 output columns. The remaining output columns
+ must have the type of the last column of the <parameter>source_sql</parameter>
+ query's result, and there must be exactly as many of them as there
+ are rows in the <parameter>category_sql</parameter> query's result.
+ </para>
+
+ <para>
+ The <function>crosstab</function> function produces one output row for each
+ consecutive group of input rows with the same
+ <structfield>row_name</structfield> value. The output
+ <structfield>row_name</structfield> column, plus any <quote>extra</quote>
+ columns, are copied from the first row of the group. The output
+ <structfield>value</structfield> columns are filled with the
+ <structfield>value</structfield> fields from rows having matching
+ <structfield>category</structfield> values. If a row's <structfield>category</structfield>
+ does not match any output of the <parameter>category_sql</parameter>
+ query, its <structfield>value</structfield> is ignored. Output
+ columns whose matching category is not present in any input row
+ of the group are filled with nulls.
+ </para>
+
+ <para>
+ In practice the <parameter>source_sql</parameter> query should always
+ specify <literal>ORDER BY 1</literal> to ensure that values with the same
+ <structfield>row_name</structfield> 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
+ <parameter>category_sql</parameter> query's output matches the specified
+ output column order.
+ </para>
+
+ <para>
+ Here are two complete examples:
+<programlisting>
+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)
+</programlisting>
+
+<programlisting>
+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)
+</programlisting>
+ </para>
+
+ <para>
+ 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 <function>crosstab</function> is named <literal>crosstab_hash</literal>.
+ </para>
+
+ </sect3>
+
+ <sect3 id="tablefunc-functions-connectby">
+ <title><function>connectby</function></title>
+
+ <indexterm>
+ <primary>connectby</primary>
+ </indexterm>
+
+<synopsis>
+connectby(text relname, text keyid_fld, text parent_keyid_fld
+ [, text orderby_fld ], text start_with, int max_depth
+ [, text branch_delim ])
+</synopsis>
+
+ <para>
+ The <function>connectby</function> 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. <function>connectby</function> can display the
+ sub-tree descending from any row.
+ </para>
+
+ <para>
+ <xref linkend="tablefunc-connectby-parameters"/> explains the
+ parameters.
+ </para>
+
+ <table id="tablefunc-connectby-parameters">
+ <title><function>connectby</function> Parameters</title>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Parameter</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><parameter>relname</parameter></entry>
+ <entry>Name of the source relation</entry>
+ </row>
+ <row>
+ <entry><parameter>keyid_fld</parameter></entry>
+ <entry>Name of the key field</entry>
+ </row>
+ <row>
+ <entry><parameter>parent_keyid_fld</parameter></entry>
+ <entry>Name of the parent-key field</entry>
+ </row>
+ <row>
+ <entry><parameter>orderby_fld</parameter></entry>
+ <entry>Name of the field to order siblings by (optional)</entry>
+ </row>
+ <row>
+ <entry><parameter>start_with</parameter></entry>
+ <entry>Key value of the row to start at</entry>
+ </row>
+ <row>
+ <entry><parameter>max_depth</parameter></entry>
+ <entry>Maximum depth to descend to, or zero for unlimited depth</entry>
+ </row>
+ <row>
+ <entry><parameter>branch_delim</parameter></entry>
+ <entry>String to separate keys with in branch output (optional)</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The key and parent-key fields can be any data type, but they must be
+ the same type. Note that the <parameter>start_with</parameter> value must be
+ entered as a text string, regardless of the type of the key field.
+ </para>
+
+ <para>
+ The <function>connectby</function> function is declared to return <type>setof
+ record</type>, so the actual names and types of the output columns must be
+ defined in the <literal>FROM</literal> clause of the calling <command>SELECT</command>
+ statement, for example:
+ </para>
+
+<programlisting>
+SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
+ AS t(keyid text, parent_keyid text, level int, branch text, pos int);
+</programlisting>
+
+ <para>
+ 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
+ <type>integer</type>. If a <parameter>branch_delim</parameter> parameter was
+ given, the next output column is the branch display and must be of type
+ <type>text</type>. Finally, if an <parameter>orderby_fld</parameter>
+ parameter was given, the last output column is a serial number, and must
+ be of type <type>integer</type>.
+ </para>
+
+ <para>
+ The <quote>branch</quote> output column shows the path of keys taken to
+ reach the current row. The keys are separated by the specified
+ <parameter>branch_delim</parameter> string. If no branch display is
+ wanted, omit both the <parameter>branch_delim</parameter> parameter
+ and the branch column in the output column list.
+ </para>
+
+ <para>
+ If the ordering of siblings of the same parent is important,
+ include the <parameter>orderby_fld</parameter> 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
+ <parameter>orderby_fld</parameter> is specified.
+ </para>
+
+ <para>
+ The parameters representing table and field names are copied as-is
+ into the SQL queries that <function>connectby</function> 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.
+ </para>
+
+ <para>
+ In large tables, performance will be poor unless there is an index on
+ the parent-key field.
+ </para>
+
+ <para>
+ It is important that the <parameter>branch_delim</parameter> string
+ not appear in any key values, else <function>connectby</function> may incorrectly
+ report an infinite-recursion error. Note that if
+ <parameter>branch_delim</parameter> is not provided, a default value
+ of <literal>~</literal> is used for recursion detection purposes.
+ <!-- That pretty well sucks. FIXME -->
+ </para>
+
+ <para>
+ Here is an example:
+<programlisting>
+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)
+</programlisting>
+ </para>
+ </sect3>
+
+ </sect2>
+
+ <sect2 id="tablefunc-author">
+ <title>Author</title>
+
+ <para>
+ Joe Conway
+ </para>
+
+ </sect2>
+
+</sect1>