Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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..808162b
--- /dev/null
+++ b/doc/src/sgml/tablefunc.sgml
@@ -0,0 +1,865 @@
+<!-- doc/src/sgml/tablefunc.sgml -->
+
+<sect1 id="tablefunc" xreflabel="tablefunc">
+ <title>tablefunc</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>
+  <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>
+   <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>
+   <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>
+   <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>
+   <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>
+   <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>
+  <title>Author</title>
+
+  <para>
+   Joe Conway
+  </para>
+
+ </sect2>
+
+</sect1>