866 lines
31 KiB
Plaintext
866 lines
31 KiB
Plaintext
<!-- doc/src/sgml/tablefunc.sgml -->
|
|
|
|
<sect1 id="tablefunc" xreflabel="tablefunc">
|
|
<title>tablefunc — functions that return tables (<function>crosstab</function> and others)</title>
|
|
|
|
<indexterm zone="tablefunc">
|
|
<primary>tablefunc</primary>
|
|
</indexterm>
|
|
|
|
<para>
|
|
The <filename>tablefunc</filename> module includes various functions that return
|
|
tables (that is, multiple rows). These functions are useful both in their
|
|
own right and as examples of how to write C functions that return
|
|
multiple rows.
|
|
</para>
|
|
|
|
<para>
|
|
This module is considered <quote>trusted</quote>, that is, it can be
|
|
installed by non-superusers who have <literal>CREATE</literal> privilege
|
|
on the current database.
|
|
</para>
|
|
|
|
<sect2 id="tablefunc-functions-sect">
|
|
<title>Functions Provided</title>
|
|
|
|
<para>
|
|
<xref linkend="tablefunc-functions"/> summarizes the functions provided
|
|
by the <filename>tablefunc</filename> module.
|
|
</para>
|
|
|
|
<table id="tablefunc-functions">
|
|
<title><filename>tablefunc</filename> Functions</title>
|
|
<tgroup cols="1">
|
|
<thead>
|
|
<row>
|
|
<entry role="func_table_entry"><para role="func_signature">
|
|
Function
|
|
</para>
|
|
<para>
|
|
Description
|
|
</para></entry>
|
|
</row>
|
|
</thead>
|
|
|
|
<tbody>
|
|
<row>
|
|
<entry role="func_table_entry"><para role="func_signature">
|
|
<function>normal_rand</function> ( <parameter>numvals</parameter> <type>integer</type>, <parameter>mean</parameter> <type>float8</type>, <parameter>stddev</parameter> <type>float8</type> )
|
|
<returnvalue>setof float8</returnvalue>
|
|
</para>
|
|
<para>
|
|
Produces a set of normally distributed random values.
|
|
</para></entry>
|
|
</row>
|
|
|
|
<row>
|
|
<entry role="func_table_entry"><para role="func_signature">
|
|
<function>crosstab</function> ( <parameter>sql</parameter> <type>text</type> )
|
|
<returnvalue>setof record</returnvalue>
|
|
</para>
|
|
<para>
|
|
Produces a <quote>pivot table</quote> containing
|
|
row names plus <replaceable>N</replaceable> value columns, where
|
|
<replaceable>N</replaceable> is determined by the row type specified
|
|
in the calling query.
|
|
</para></entry>
|
|
</row>
|
|
|
|
<row>
|
|
<entry role="func_table_entry"><para role="func_signature">
|
|
<function>crosstab<replaceable>N</replaceable></function> ( <parameter>sql</parameter> <type>text</type> )
|
|
<returnvalue>setof table_crosstab_<replaceable>N</replaceable></returnvalue>
|
|
</para>
|
|
<para>
|
|
Produces a <quote>pivot table</quote> containing
|
|
row names plus <replaceable>N</replaceable> value columns.
|
|
<function>crosstab2</function>, <function>crosstab3</function>, and
|
|
<function>crosstab4</function> are predefined, but you can create additional
|
|
<function>crosstab<replaceable>N</replaceable></function> functions as described below.
|
|
</para></entry>
|
|
</row>
|
|
|
|
<row>
|
|
<entry role="func_table_entry"><para role="func_signature">
|
|
<function>crosstab</function> ( <parameter>source_sql</parameter> <type>text</type>, <parameter>category_sql</parameter> <type>text</type> )
|
|
<returnvalue>setof record</returnvalue>
|
|
</para>
|
|
<para>
|
|
Produces a <quote>pivot table</quote>
|
|
with the value columns specified by a second query.
|
|
</para></entry>
|
|
</row>
|
|
|
|
<row>
|
|
<entry role="func_table_entry"><para role="func_signature">
|
|
<function>crosstab</function> ( <parameter>sql</parameter> <type>text</type>, <parameter>N</parameter> <type>integer</type> )
|
|
<returnvalue>setof record</returnvalue>
|
|
</para>
|
|
<para>
|
|
Obsolete version of <function>crosstab(text)</function>.
|
|
The parameter <parameter>N</parameter> is now ignored, since the
|
|
number of value columns is always determined by the calling query.
|
|
</para></entry>
|
|
</row>
|
|
|
|
<row>
|
|
<entry role="func_table_entry"><para role="func_signature">
|
|
<indexterm><primary>connectby</primary></indexterm>
|
|
<function>connectby</function> ( <parameter>relname</parameter> <type>text</type>, <parameter>keyid_fld</parameter> <type>text</type>, <parameter>parent_keyid_fld</parameter> <type>text</type>
|
|
<optional>, <parameter>orderby_fld</parameter> <type>text</type> </optional>, <parameter>start_with</parameter> <type>text</type>, <parameter>max_depth</parameter> <type>integer</type>
|
|
<optional>, <parameter>branch_delim</parameter> <type>text</type> </optional> )
|
|
<returnvalue>setof record</returnvalue>
|
|
</para>
|
|
<para>
|
|
Produces a representation of a hierarchical tree structure.
|
|
</para></entry>
|
|
</row>
|
|
</tbody>
|
|
</tgroup>
|
|
</table>
|
|
|
|
<sect3 id="tablefunc-functions-normal-rand">
|
|
<title><function>normal_rand</function></title>
|
|
|
|
<indexterm>
|
|
<primary>normal_rand</primary>
|
|
</indexterm>
|
|
|
|
<synopsis>
|
|
normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8
|
|
</synopsis>
|
|
|
|
<para>
|
|
<function>normal_rand</function> produces a set of normally distributed random
|
|
values (Gaussian distribution).
|
|
</para>
|
|
|
|
<para>
|
|
<parameter>numvals</parameter> is the number of values to be returned
|
|
from the function. <parameter>mean</parameter> is the mean of the normal
|
|
distribution of values and <parameter>stddev</parameter> is the standard
|
|
deviation of the normal distribution of values.
|
|
</para>
|
|
|
|
<para>
|
|
For example, this call requests 1000 values with a mean of 5 and a
|
|
standard deviation of 3:
|
|
</para>
|
|
|
|
<screen>
|
|
test=# SELECT * FROM normal_rand(1000, 5, 3);
|
|
normal_rand
|
|
----------------------
|
|
1.56556322244898
|
|
9.10040991424657
|
|
5.36957140345079
|
|
-0.369151492880995
|
|
0.283600703686639
|
|
.
|
|
.
|
|
.
|
|
4.82992125404908
|
|
9.71308014517282
|
|
2.49639286969028
|
|
(1000 rows)
|
|
</screen>
|
|
</sect3>
|
|
|
|
<sect3 id="tablefunc-functions-crosstab-text">
|
|
<title><function>crosstab(text)</function></title>
|
|
|
|
<indexterm>
|
|
<primary>crosstab</primary>
|
|
</indexterm>
|
|
|
|
<synopsis>
|
|
crosstab(text sql)
|
|
crosstab(text sql, int N)
|
|
</synopsis>
|
|
|
|
<para>
|
|
The <function>crosstab</function> function is used to produce <quote>pivot</quote>
|
|
displays, wherein data is listed across the page rather than down.
|
|
For example, we might have data like
|
|
<programlisting>
|
|
row1 val11
|
|
row1 val12
|
|
row1 val13
|
|
...
|
|
row2 val21
|
|
row2 val22
|
|
row2 val23
|
|
...
|
|
</programlisting>
|
|
which we wish to display like
|
|
<programlisting>
|
|
row1 val11 val12 val13 ...
|
|
row2 val21 val22 val23 ...
|
|
...
|
|
</programlisting>
|
|
The <function>crosstab</function> function takes a text parameter that is an SQL
|
|
query producing raw data formatted in the first way, and produces a table
|
|
formatted in the second way.
|
|
</para>
|
|
|
|
<para>
|
|
The <parameter>sql</parameter> parameter is an SQL statement that produces
|
|
the source set of data. This statement must return one
|
|
<structfield>row_name</structfield> column, one
|
|
<structfield>category</structfield> column, and one
|
|
<structfield>value</structfield> column. <parameter>N</parameter> is an
|
|
obsolete parameter, ignored if supplied (formerly this had to match the
|
|
number of output value columns, but now that is determined by the
|
|
calling query).
|
|
</para>
|
|
|
|
<para>
|
|
For example, the provided query might produce a set something like:
|
|
<programlisting>
|
|
row_name cat value
|
|
----------+-------+-------
|
|
row1 cat1 val1
|
|
row1 cat2 val2
|
|
row1 cat3 val3
|
|
row1 cat4 val4
|
|
row2 cat1 val5
|
|
row2 cat2 val6
|
|
row2 cat3 val7
|
|
row2 cat4 val8
|
|
</programlisting>
|
|
</para>
|
|
|
|
<para>
|
|
The <function>crosstab</function> function is declared to return <type>setof
|
|
record</type>, so the actual names and types of the output columns must be
|
|
defined in the <literal>FROM</literal> clause of the calling <command>SELECT</command>
|
|
statement, for example:
|
|
<programlisting>
|
|
SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);
|
|
</programlisting>
|
|
This example produces a set something like:
|
|
<programlisting>
|
|
<== 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 id="tablefunc-functions-crosstab-n-text">
|
|
<title><function>crosstab<replaceable>N</replaceable>(text)</function></title>
|
|
|
|
<indexterm>
|
|
<primary>crosstab</primary>
|
|
</indexterm>
|
|
|
|
<synopsis>
|
|
crosstab<replaceable>N</replaceable>(text sql)
|
|
</synopsis>
|
|
|
|
<para>
|
|
The <function>crosstab<replaceable>N</replaceable></function> functions are examples of how
|
|
to set up custom wrappers for the general <function>crosstab</function> function,
|
|
so that you need not write out column names and types in the calling
|
|
<command>SELECT</command> query. The <filename>tablefunc</filename> module includes
|
|
<function>crosstab2</function>, <function>crosstab3</function>, and
|
|
<function>crosstab4</function>, whose output row types are defined as
|
|
</para>
|
|
|
|
<programlisting>
|
|
CREATE TYPE tablefunc_crosstab_N AS (
|
|
row_name TEXT,
|
|
category_1 TEXT,
|
|
category_2 TEXT,
|
|
.
|
|
.
|
|
.
|
|
category_N TEXT
|
|
);
|
|
</programlisting>
|
|
|
|
<para>
|
|
Thus, these functions can be used directly when the input query produces
|
|
<structfield>row_name</structfield> and <structfield>value</structfield> columns of type
|
|
<type>text</type>, and you want 2, 3, or 4 output values columns.
|
|
In all other ways they behave exactly as described above for the
|
|
general <function>crosstab</function> function.
|
|
</para>
|
|
|
|
<para>
|
|
For instance, the example given in the previous section would also
|
|
work as
|
|
<programlisting>
|
|
SELECT *
|
|
FROM crosstab3(
|
|
'select rowid, attribute, value
|
|
from ct
|
|
where attribute = ''att2'' or attribute = ''att3''
|
|
order by 1,2');
|
|
</programlisting>
|
|
</para>
|
|
|
|
<para>
|
|
These functions are provided mostly for illustration purposes. You
|
|
can create your own return types and functions based on the
|
|
underlying <function>crosstab()</function> function. There are two ways
|
|
to do it:
|
|
|
|
<itemizedlist>
|
|
<listitem>
|
|
<para>
|
|
Create a composite type describing the desired output columns,
|
|
similar to the examples in
|
|
<filename>contrib/tablefunc/tablefunc--1.0.sql</filename>.
|
|
Then define a
|
|
unique function name accepting one <type>text</type> parameter and returning
|
|
<type>setof your_type_name</type>, but linking to the same underlying
|
|
<function>crosstab</function> C function. For example, if your source data
|
|
produces row names that are <type>text</type>, and values that are
|
|
<type>float8</type>, and you want 5 value columns:
|
|
<programlisting>
|
|
CREATE TYPE my_crosstab_float8_5_cols AS (
|
|
my_row_name text,
|
|
my_category_1 float8,
|
|
my_category_2 float8,
|
|
my_category_3 float8,
|
|
my_category_4 float8,
|
|
my_category_5 float8
|
|
);
|
|
|
|
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
|
|
RETURNS setof my_crosstab_float8_5_cols
|
|
AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
|
|
</programlisting>
|
|
</para>
|
|
</listitem>
|
|
|
|
<listitem>
|
|
<para>
|
|
Use <literal>OUT</literal> parameters to define the return type implicitly.
|
|
The same example could also be done this way:
|
|
<programlisting>
|
|
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(
|
|
IN text,
|
|
OUT my_row_name text,
|
|
OUT my_category_1 float8,
|
|
OUT my_category_2 float8,
|
|
OUT my_category_3 float8,
|
|
OUT my_category_4 float8,
|
|
OUT my_category_5 float8)
|
|
RETURNS setof record
|
|
AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
|
|
</programlisting>
|
|
</para>
|
|
</listitem>
|
|
</itemizedlist>
|
|
</para>
|
|
|
|
</sect3>
|
|
|
|
<sect3 id="tablefunc-functions-crosstab-text-2">
|
|
<title><function>crosstab(text, text)</function></title>
|
|
|
|
<indexterm>
|
|
<primary>crosstab</primary>
|
|
</indexterm>
|
|
|
|
<synopsis>
|
|
crosstab(text source_sql, text category_sql)
|
|
</synopsis>
|
|
|
|
<para>
|
|
The main limitation of the single-parameter form of <function>crosstab</function>
|
|
is that it treats all values in a group alike, inserting each value into
|
|
the first available column. If you want the value
|
|
columns to correspond to specific categories of data, and some groups
|
|
might not have data for some of the categories, that doesn't work well.
|
|
The two-parameter form of <function>crosstab</function> handles this case by
|
|
providing an explicit list of the categories corresponding to the
|
|
output columns.
|
|
</para>
|
|
|
|
<para>
|
|
<parameter>source_sql</parameter> is an SQL statement that produces the
|
|
source set of data. This statement must return one
|
|
<structfield>row_name</structfield> column, one
|
|
<structfield>category</structfield> column, and one
|
|
<structfield>value</structfield> column. It may also have one or more
|
|
<quote>extra</quote> columns.
|
|
The <structfield>row_name</structfield> column must be first. The
|
|
<structfield>category</structfield> and <structfield>value</structfield>
|
|
columns must be the last two columns, in that order. Any columns between
|
|
<structfield>row_name</structfield> and
|
|
<structfield>category</structfield> are treated as <quote>extra</quote>.
|
|
The <quote>extra</quote> columns are expected to be the same for all rows
|
|
with the same <structfield>row_name</structfield> value.
|
|
</para>
|
|
|
|
<para>
|
|
For example, <parameter>source_sql</parameter> might produce a set
|
|
something like:
|
|
<programlisting>
|
|
SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;
|
|
|
|
row_name extra_col cat value
|
|
----------+------------+-----+---------
|
|
row1 extra1 cat1 val1
|
|
row1 extra1 cat2 val2
|
|
row1 extra1 cat4 val4
|
|
row2 extra2 cat1 val5
|
|
row2 extra2 cat2 val6
|
|
row2 extra2 cat3 val7
|
|
row2 extra2 cat4 val8
|
|
</programlisting>
|
|
</para>
|
|
|
|
<para>
|
|
<parameter>category_sql</parameter> is an SQL statement that produces
|
|
the set of categories. This statement must return only one column.
|
|
It must produce at least one row, or an error will be generated.
|
|
Also, it must not produce duplicate values, or an error will be
|
|
generated. <parameter>category_sql</parameter> might be something like:
|
|
|
|
<programlisting>
|
|
SELECT DISTINCT cat FROM foo ORDER BY 1;
|
|
cat
|
|
-------
|
|
cat1
|
|
cat2
|
|
cat3
|
|
cat4
|
|
</programlisting>
|
|
</para>
|
|
|
|
<para>
|
|
The <function>crosstab</function> function is declared to return <type>setof
|
|
record</type>, so the actual names and types of the output columns must be
|
|
defined in the <literal>FROM</literal> clause of the calling <command>SELECT</command>
|
|
statement, for example:
|
|
|
|
<programlisting>
|
|
SELECT * FROM crosstab('...', '...')
|
|
AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);
|
|
</programlisting>
|
|
</para>
|
|
|
|
<para>
|
|
This will produce a result something like:
|
|
<programlisting>
|
|
<== 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 id="tablefunc-functions-connectby">
|
|
<title><function>connectby</function></title>
|
|
|
|
<indexterm>
|
|
<primary>connectby</primary>
|
|
</indexterm>
|
|
|
|
<synopsis>
|
|
connectby(text relname, text keyid_fld, text parent_keyid_fld
|
|
[, text orderby_fld ], text start_with, int max_depth
|
|
[, text branch_delim ])
|
|
</synopsis>
|
|
|
|
<para>
|
|
The <function>connectby</function> function produces a display of hierarchical
|
|
data that is stored in a table. The table must have a key field that
|
|
uniquely identifies rows, and a parent-key field that references the
|
|
parent (if any) of each row. <function>connectby</function> can display the
|
|
sub-tree descending from any row.
|
|
</para>
|
|
|
|
<para>
|
|
<xref linkend="tablefunc-connectby-parameters"/> explains the
|
|
parameters.
|
|
</para>
|
|
|
|
<table id="tablefunc-connectby-parameters">
|
|
<title><function>connectby</function> Parameters</title>
|
|
<tgroup cols="2">
|
|
<thead>
|
|
<row>
|
|
<entry>Parameter</entry>
|
|
<entry>Description</entry>
|
|
</row>
|
|
</thead>
|
|
<tbody>
|
|
<row>
|
|
<entry><parameter>relname</parameter></entry>
|
|
<entry>Name of the source relation</entry>
|
|
</row>
|
|
<row>
|
|
<entry><parameter>keyid_fld</parameter></entry>
|
|
<entry>Name of the key field</entry>
|
|
</row>
|
|
<row>
|
|
<entry><parameter>parent_keyid_fld</parameter></entry>
|
|
<entry>Name of the parent-key field</entry>
|
|
</row>
|
|
<row>
|
|
<entry><parameter>orderby_fld</parameter></entry>
|
|
<entry>Name of the field to order siblings by (optional)</entry>
|
|
</row>
|
|
<row>
|
|
<entry><parameter>start_with</parameter></entry>
|
|
<entry>Key value of the row to start at</entry>
|
|
</row>
|
|
<row>
|
|
<entry><parameter>max_depth</parameter></entry>
|
|
<entry>Maximum depth to descend to, or zero for unlimited depth</entry>
|
|
</row>
|
|
<row>
|
|
<entry><parameter>branch_delim</parameter></entry>
|
|
<entry>String to separate keys with in branch output (optional)</entry>
|
|
</row>
|
|
</tbody>
|
|
</tgroup>
|
|
</table>
|
|
|
|
<para>
|
|
The key and parent-key fields can be any data type, but they must be
|
|
the same type. Note that the <parameter>start_with</parameter> value must be
|
|
entered as a text string, regardless of the type of the key field.
|
|
</para>
|
|
|
|
<para>
|
|
The <function>connectby</function> function is declared to return <type>setof
|
|
record</type>, so the actual names and types of the output columns must be
|
|
defined in the <literal>FROM</literal> clause of the calling <command>SELECT</command>
|
|
statement, for example:
|
|
</para>
|
|
|
|
<programlisting>
|
|
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
|
|
AS t(keyid text, parent_keyid text, level int, branch text, pos int);
|
|
</programlisting>
|
|
|
|
<para>
|
|
The first two output columns are used for the current row's key and
|
|
its parent row's key; they must match the type of the table's key field.
|
|
The third output column is the depth in the tree and must be of type
|
|
<type>integer</type>. If a <parameter>branch_delim</parameter> parameter was
|
|
given, the next output column is the branch display and must be of type
|
|
<type>text</type>. Finally, if an <parameter>orderby_fld</parameter>
|
|
parameter was given, the last output column is a serial number, and must
|
|
be of type <type>integer</type>.
|
|
</para>
|
|
|
|
<para>
|
|
The <quote>branch</quote> output column shows the path of keys taken to
|
|
reach the current row. The keys are separated by the specified
|
|
<parameter>branch_delim</parameter> string. If no branch display is
|
|
wanted, omit both the <parameter>branch_delim</parameter> parameter
|
|
and the branch column in the output column list.
|
|
</para>
|
|
|
|
<para>
|
|
If the ordering of siblings of the same parent is important,
|
|
include the <parameter>orderby_fld</parameter> parameter to
|
|
specify which field to order siblings by. This field can be of any
|
|
sortable data type. The output column list must include a final
|
|
integer serial-number column, if and only if
|
|
<parameter>orderby_fld</parameter> is specified.
|
|
</para>
|
|
|
|
<para>
|
|
The parameters representing table and field names are copied as-is
|
|
into the SQL queries that <function>connectby</function> generates internally.
|
|
Therefore, include double quotes if the names are mixed-case or contain
|
|
special characters. You may also need to schema-qualify the table name.
|
|
</para>
|
|
|
|
<para>
|
|
In large tables, performance will be poor unless there is an index on
|
|
the parent-key field.
|
|
</para>
|
|
|
|
<para>
|
|
It is important that the <parameter>branch_delim</parameter> string
|
|
not appear in any key values, else <function>connectby</function> may incorrectly
|
|
report an infinite-recursion error. Note that if
|
|
<parameter>branch_delim</parameter> is not provided, a default value
|
|
of <literal>~</literal> is used for recursion detection purposes.
|
|
<!-- That pretty well sucks. FIXME -->
|
|
</para>
|
|
|
|
<para>
|
|
Here is an example:
|
|
<programlisting>
|
|
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
|
|
|
|
INSERT INTO connectby_tree VALUES('row1',NULL, 0);
|
|
INSERT INTO connectby_tree VALUES('row2','row1', 0);
|
|
INSERT INTO connectby_tree VALUES('row3','row1', 0);
|
|
INSERT INTO connectby_tree VALUES('row4','row2', 1);
|
|
INSERT INTO connectby_tree VALUES('row5','row2', 0);
|
|
INSERT INTO connectby_tree VALUES('row6','row4', 0);
|
|
INSERT INTO connectby_tree VALUES('row7','row3', 0);
|
|
INSERT INTO connectby_tree VALUES('row8','row6', 0);
|
|
INSERT INTO connectby_tree VALUES('row9','row5', 0);
|
|
|
|
-- with branch, without orderby_fld (order of results is not guaranteed)
|
|
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
|
|
AS t(keyid text, parent_keyid text, level int, branch text);
|
|
keyid | parent_keyid | level | branch
|
|
-------+--------------+-------+---------------------
|
|
row2 | | 0 | row2
|
|
row4 | row2 | 1 | row2~row4
|
|
row6 | row4 | 2 | row2~row4~row6
|
|
row8 | row6 | 3 | row2~row4~row6~row8
|
|
row5 | row2 | 1 | row2~row5
|
|
row9 | row5 | 2 | row2~row5~row9
|
|
(6 rows)
|
|
|
|
-- without branch, without orderby_fld (order of results is not guaranteed)
|
|
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
|
|
AS t(keyid text, parent_keyid text, level int);
|
|
keyid | parent_keyid | level
|
|
-------+--------------+-------
|
|
row2 | | 0
|
|
row4 | row2 | 1
|
|
row6 | row4 | 2
|
|
row8 | row6 | 3
|
|
row5 | row2 | 1
|
|
row9 | row5 | 2
|
|
(6 rows)
|
|
|
|
-- with branch, with orderby_fld (notice that row5 comes before row4)
|
|
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
|
|
AS t(keyid text, parent_keyid text, level int, branch text, pos int);
|
|
keyid | parent_keyid | level | branch | pos
|
|
-------+--------------+-------+---------------------+-----
|
|
row2 | | 0 | row2 | 1
|
|
row5 | row2 | 1 | row2~row5 | 2
|
|
row9 | row5 | 2 | row2~row5~row9 | 3
|
|
row4 | row2 | 1 | row2~row4 | 4
|
|
row6 | row4 | 2 | row2~row4~row6 | 5
|
|
row8 | row6 | 3 | row2~row4~row6~row8 | 6
|
|
(6 rows)
|
|
|
|
-- without branch, with orderby_fld (notice that row5 comes before row4)
|
|
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
|
|
AS t(keyid text, parent_keyid text, level int, pos int);
|
|
keyid | parent_keyid | level | pos
|
|
-------+--------------+-------+-----
|
|
row2 | | 0 | 1
|
|
row5 | row2 | 1 | 2
|
|
row9 | row5 | 2 | 3
|
|
row4 | row2 | 1 | 4
|
|
row6 | row4 | 2 | 5
|
|
row8 | row6 | 3 | 6
|
|
(6 rows)
|
|
</programlisting>
|
|
</para>
|
|
</sect3>
|
|
|
|
</sect2>
|
|
|
|
<sect2 id="tablefunc-author">
|
|
<title>Author</title>
|
|
|
|
<para>
|
|
Joe Conway
|
|
</para>
|
|
|
|
</sect2>
|
|
|
|
</sect1>
|