| <!-- 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> |
