diff options
Diffstat (limited to '')
| -rw-r--r-- | doc/src/sgml/tablefunc.sgml | 865 |
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> + <== value columns ==> + 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> + <== value columns ==> +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> |