~ubuntu-branches/ubuntu/trusty/postgresql-9.3/trusty-updates : contents of doc/src/sgml/html/tablefunc.html at revision 27

~ubuntu-branches/ubuntu/trusty/postgresql-9.3/trusty-updates : (revision 27)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>tablefunc</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.3.13 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Additional Supplied Modules"
HREF="contrib.html"><LINK
REL="PREVIOUS"
TITLE="sslinfo"
HREF="sslinfo.html"><LINK
REL="NEXT"
TITLE="tcn"
HREF="tcn.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2016-05-09T21:13:26"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.3.13 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="sslinfo"
HREF="sslinfo.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="contrib.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Appendix F. Additional Supplied Modules</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="tcn"
HREF="tcn.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="TABLEFUNC"
>F.36. tablefunc</A
></H1
><P
>  The <TT
CLASS="FILENAME"
>tablefunc</TT
> 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.
 </P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN158772"
>F.36.1. Functions Provided</A
></H2
><P
>   <A
HREF="tablefunc.html#TABLEFUNC-FUNCTIONS"
>Table F-28</A
> shows the functions provided
   by the <TT
CLASS="FILENAME"
>tablefunc</TT
> module.
  </P
><DIV
CLASS="TABLE"
><A
NAME="TABLEFUNC-FUNCTIONS"
></A
><P
><B
>Table F-28. <TT
CLASS="FILENAME"
>tablefunc</TT
> Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><THEAD
><TR
><TH
>Function</TH
><TH
>Returns</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><CODE
CLASS="FUNCTION"
>normal_rand(int numvals, float8 mean, float8 stddev)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>setof float8</TT
></TD
><TD
>       Produces a set of normally distributed random values
      </TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>crosstab(text sql)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>setof record</TT
></TD
><TD
>       Produces a <SPAN
CLASS="QUOTE"
>"pivot table"</SPAN
> containing
       row names plus <TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
> value columns, where
       <TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
> is determined by the row type specified in the calling
       query
      </TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>crosstab<TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
>(text sql)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>setof table_crosstab_<TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
></TT
></TD
><TD
>       Produces a <SPAN
CLASS="QUOTE"
>"pivot table"</SPAN
> containing
       row names plus <TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
> value columns.
       <CODE
CLASS="FUNCTION"
>crosstab2</CODE
>, <CODE
CLASS="FUNCTION"
>crosstab3</CODE
>, and
       <CODE
CLASS="FUNCTION"
>crosstab4</CODE
> are predefined, but you can create additional
       <CODE
CLASS="FUNCTION"
>crosstab<TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
></CODE
> functions as described below
      </TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>crosstab(text source_sql, text category_sql)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>setof record</TT
></TD
><TD
>       Produces a <SPAN
CLASS="QUOTE"
>"pivot table"</SPAN
>
       with the value columns specified by a second query
      </TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>crosstab(text sql, int N)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>setof record</TT
></TD
><TD
>       <P
>Obsolete version of <CODE
CLASS="FUNCTION"
>crosstab(text)</CODE
>.
        The parameter <TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
> is now ignored, since the number of
        value columns is always determined by the calling query
       </P
>
      </TD
></TR
><TR
><TD
>       <CODE
CLASS="FUNCTION"
>        connectby(text relname, text keyid_fld, text parent_keyid_fld
        [, text orderby_fld ], text start_with, int max_depth
        [, text branch_delim ])
       </CODE
>
       
      </TD
><TD
><TT
CLASS="TYPE"
>setof record</TT
></TD
><TD
>       Produces a representation of a hierarchical tree structure
      </TD
></TR
></TBODY
></TABLE
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN158841"
>F.36.1.1. <CODE
CLASS="FUNCTION"
>normal_rand</CODE
></A
></H3
><PRE
CLASS="SYNOPSIS"
>normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8</PRE
><P
>     <CODE
CLASS="FUNCTION"
>normal_rand</CODE
> produces a set of normally distributed random
     values (Gaussian distribution).
    </P
><P
>     <TT
CLASS="PARAMETER"
>numvals</TT
> is the number of values to be returned
     from the function. <TT
CLASS="PARAMETER"
>mean</TT
> is the mean of the normal
     distribution of values and <TT
CLASS="PARAMETER"
>stddev</TT
> is the standard
     deviation of the normal distribution of values.
    </P
><P
>     For example, this call requests 1000 values with a mean of 5 and a
     standard deviation of 3:
    </P
><PRE
CLASS="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)</PRE
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN158855"
>F.36.1.2. <CODE
CLASS="FUNCTION"
>crosstab(text)</CODE
></A
></H3
><PRE
CLASS="SYNOPSIS"
>crosstab(text sql)
crosstab(text sql, int N)</PRE
><P
>    The <CODE
CLASS="FUNCTION"
>crosstab</CODE
> function is used to produce <SPAN
CLASS="QUOTE"
>"pivot"</SPAN
>
    displays, wherein data is listed across the page rather than down.
    For example, we might have data like
</P><PRE
CLASS="PROGRAMLISTING"
>row1    val11
row1    val12
row1    val13
...
row2    val21
row2    val22
row2    val23
...</PRE
><P>
    which we wish to display like
</P><PRE
CLASS="PROGRAMLISTING"
>row1    val11   val12   val13   ...
row2    val21   val22   val23   ...
...</PRE
><P>
    The <CODE
CLASS="FUNCTION"
>crosstab</CODE
> function takes a text parameter that is a SQL
    query producing raw data formatted in the first way, and produces a table
    formatted in the second way.
   </P
><P
>    The <TT
CLASS="PARAMETER"
>sql</TT
> parameter is a SQL statement that produces
    the source set of data. This statement must return one
    <TT
CLASS="STRUCTFIELD"
>row_name</TT
> column, one
    <TT
CLASS="STRUCTFIELD"
>category</TT
> column, and one
    <TT
CLASS="STRUCTFIELD"
>value</TT
> column.  <TT
CLASS="PARAMETER"
>N</TT
> 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).
   </P
><P
>    For example, the provided query might produce a set something like:
</P><PRE
CLASS="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</PRE
><P>
   </P
><P
>    The <CODE
CLASS="FUNCTION"
>crosstab</CODE
> function is declared to return <TT
CLASS="TYPE"
>setof
    record</TT
>, so the actual names and types of the output columns must be
    defined in the <TT
CLASS="LITERAL"
>FROM</TT
> clause of the calling <TT
CLASS="COMMAND"
>SELECT</TT
>
    statement, for example:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);</PRE
><P>
    This example produces a set something like:
</P><PRE
CLASS="PROGRAMLISTING"
>           &lt;== value  columns  ==&gt;
 row_name   category_1   category_2
----------+------------+------------
  row1        val1         val2
  row2        val5         val6</PRE
><P>
   </P
><P
>    The <TT
CLASS="LITERAL"
>FROM</TT
> clause must define the output as one
    <TT
CLASS="STRUCTFIELD"
>row_name</TT
> column (of the same data type as the first result
    column of the SQL query) followed by N <TT
CLASS="STRUCTFIELD"
>value</TT
> 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.
   </P
><P
>    The <CODE
CLASS="FUNCTION"
>crosstab</CODE
> function produces one output row for each
    consecutive group of input rows with the same
    <TT
CLASS="STRUCTFIELD"
>row_name</TT
> value.  It fills the output
    <TT
CLASS="STRUCTFIELD"
>value</TT
> columns, left to right, with the
    <TT
CLASS="STRUCTFIELD"
>value</TT
> fields from these rows.  If there
    are fewer rows in a group than there are output <TT
CLASS="STRUCTFIELD"
>value</TT
>
    columns, the extra output columns are filled with nulls; if there are
    more rows, the extra input rows are skipped.
   </P
><P
>    In practice the SQL query should always specify <TT
CLASS="LITERAL"
>ORDER BY 1,2</TT
>
    to ensure that the input rows are properly ordered, that is, values with
    the same <TT
CLASS="STRUCTFIELD"
>row_name</TT
> are brought together and
    correctly ordered within the row.  Notice that <CODE
CLASS="FUNCTION"
>crosstab</CODE
>
    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.
   </P
><P
>    Here is a complete example:
</P><PRE
CLASS="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)</PRE
><P>
   </P
><P
>    You can avoid always having to write out a <TT
CLASS="LITERAL"
>FROM</TT
> 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 <TT
CLASS="LITERAL"
>FROM</TT
> clause in a view definition.
   </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN158901"
>F.36.1.3. <CODE
CLASS="FUNCTION"
>crosstab<TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
>(text)</CODE
></A
></H3
><PRE
CLASS="SYNOPSIS"
>crosstab<TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
>(text sql)</PRE
><P
>     The <CODE
CLASS="FUNCTION"
>crosstab<TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
></CODE
> functions are examples of how
     to set up custom wrappers for the general <CODE
CLASS="FUNCTION"
>crosstab</CODE
> function,
     so that you need not write out column names and types in the calling
     <TT
CLASS="COMMAND"
>SELECT</TT
> query.  The <TT
CLASS="FILENAME"
>tablefunc</TT
> module includes
     <CODE
CLASS="FUNCTION"
>crosstab2</CODE
>, <CODE
CLASS="FUNCTION"
>crosstab3</CODE
>, and
     <CODE
CLASS="FUNCTION"
>crosstab4</CODE
>, whose output row types are defined as
    </P
><PRE
CLASS="PROGRAMLISTING"
>CREATE TYPE tablefunc_crosstab_N AS (
    row_name TEXT,
    category_1 TEXT,
    category_2 TEXT,
        .
        .
        .
    category_N TEXT
);</PRE
><P
>     Thus, these functions can be used directly when the input query produces
     <TT
CLASS="STRUCTFIELD"
>row_name</TT
> and <TT
CLASS="STRUCTFIELD"
>value</TT
> columns of type
     <TT
CLASS="TYPE"
>text</TT
>, and you want 2, 3, or 4 output values columns.
     In all other ways they behave exactly as described above for the
     general <CODE
CLASS="FUNCTION"
>crosstab</CODE
> function.
    </P
><P
>     For instance, the example given in the previous section would also
     work as
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT *
FROM crosstab3(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2');</PRE
><P>
    </P
><P
>     These functions are provided mostly for illustration purposes. You
     can create your own return types and functions based on the
     underlying <CODE
CLASS="FUNCTION"
>crosstab()</CODE
> function.  There are two ways
     to do it:

    <P
></P
></P><UL
><LI
><P
>       Create a composite type describing the desired output columns,
       similar to the examples in
       <TT
CLASS="FILENAME"
>contrib/tablefunc/tablefunc--1.0.sql</TT
>.
       Then define a
       unique function name accepting one <TT
CLASS="TYPE"
>text</TT
> parameter and returning
       <TT
CLASS="TYPE"
>setof your_type_name</TT
>, but linking to the same underlying
       <CODE
CLASS="FUNCTION"
>crosstab</CODE
> C function.  For example, if your source data
       produces row names that are <TT
CLASS="TYPE"
>text</TT
>, and values that are
       <TT
CLASS="TYPE"
>float8</TT
>, and you want 5 value columns:
</P><PRE
CLASS="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;</PRE
><P>
      </P
></LI
><LI
><P
>       Use <TT
CLASS="LITERAL"
>OUT</TT
> parameters to define the return type implicitly.
       The same example could also be done this way:
</P><PRE
CLASS="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;</PRE
><P>
      </P
></LI
></UL
><P>
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN158942"
>F.36.1.4. <CODE
CLASS="FUNCTION"
>crosstab(text, text)</CODE
></A
></H3
><PRE
CLASS="SYNOPSIS"
>crosstab(text source_sql, text category_sql)</PRE
><P
>    The main limitation of the single-parameter form of <CODE
CLASS="FUNCTION"
>crosstab</CODE
>
    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 <CODE
CLASS="FUNCTION"
>crosstab</CODE
> handles this case by
    providing an explicit list of the categories corresponding to the
    output columns.
   </P
><P
>    <TT
CLASS="PARAMETER"
>source_sql</TT
> is a SQL statement that produces the
    source set of data.  This statement must return one
    <TT
CLASS="STRUCTFIELD"
>row_name</TT
> column, one
    <TT
CLASS="STRUCTFIELD"
>category</TT
> column, and one
    <TT
CLASS="STRUCTFIELD"
>value</TT
> column. It may also have one or more
    <SPAN
CLASS="QUOTE"
>"extra"</SPAN
> columns.
    The <TT
CLASS="STRUCTFIELD"
>row_name</TT
> column must be first. The
    <TT
CLASS="STRUCTFIELD"
>category</TT
> and <TT
CLASS="STRUCTFIELD"
>value</TT
>
    columns must be the last two columns, in that order.  Any columns between
    <TT
CLASS="STRUCTFIELD"
>row_name</TT
> and
    <TT
CLASS="STRUCTFIELD"
>category</TT
> are treated as <SPAN
CLASS="QUOTE"
>"extra"</SPAN
>.
    The <SPAN
CLASS="QUOTE"
>"extra"</SPAN
> columns are expected to be the same for all rows
    with the same <TT
CLASS="STRUCTFIELD"
>row_name</TT
> value.
   </P
><P
>    For example, <TT
CLASS="PARAMETER"
>source_sql</TT
> might produce a set
    something like:
</P><PRE
CLASS="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</PRE
><P>
   </P
><P
>    <TT
CLASS="PARAMETER"
>category_sql</TT
> is a 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.  <TT
CLASS="PARAMETER"
>category_sql</TT
> might be something like:

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT DISTINCT cat FROM foo ORDER BY 1;
    cat
  -------
    cat1
    cat2
    cat3
    cat4</PRE
><P>
   </P
><P
>    The <CODE
CLASS="FUNCTION"
>crosstab</CODE
> function is declared to return <TT
CLASS="TYPE"
>setof
    record</TT
>, so the actual names and types of the output columns must be
    defined in the <TT
CLASS="LITERAL"
>FROM</TT
> clause of the calling <TT
CLASS="COMMAND"
>SELECT</TT
>
    statement, for example:

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM crosstab('...', '...')
    AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);</PRE
><P>
   </P
><P
>    This will produce a result something like:
</P><PRE
CLASS="PROGRAMLISTING"
>                  &lt;==  value  columns   ==&gt;
row_name   extra   cat1   cat2   cat3   cat4
---------+-------+------+------+------+------
  row1     extra1  val1   val2          val4
  row2     extra2  val5   val6   val7   val8</PRE
><P>
   </P
><P
>    The <TT
CLASS="LITERAL"
>FROM</TT
> clause must define the proper number of output
    columns of the proper data types.  If there are <TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
>
    columns in the <TT
CLASS="PARAMETER"
>source_sql</TT
> query's result, the first
    <TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
>-2 of them must match up with the first
    <TT
CLASS="REPLACEABLE"
><I
>N</I
></TT
>-2 output columns.  The remaining output columns
    must have the type of the last column of the <TT
CLASS="PARAMETER"
>source_sql</TT
>
    query's result, and there must be exactly as many of them as there
    are rows in the <TT
CLASS="PARAMETER"
>category_sql</TT
> query's result.
   </P
><P
>    The <CODE
CLASS="FUNCTION"
>crosstab</CODE
> function produces one output row for each
    consecutive group of input rows with the same
    <TT
CLASS="STRUCTFIELD"
>row_name</TT
> value.  The output
    <TT
CLASS="STRUCTFIELD"
>row_name</TT
> column, plus any <SPAN
CLASS="QUOTE"
>"extra"</SPAN
>
    columns, are copied from the first row of the group.  The output
    <TT
CLASS="STRUCTFIELD"
>value</TT
> columns are filled with the
    <TT
CLASS="STRUCTFIELD"
>value</TT
> fields from rows having matching
    <TT
CLASS="STRUCTFIELD"
>category</TT
> values.  If a row's <TT
CLASS="STRUCTFIELD"
>category</TT
>
    does not match any output of the <TT
CLASS="PARAMETER"
>category_sql</TT
>
    query, its <TT
CLASS="STRUCTFIELD"
>value</TT
> is ignored.  Output
    columns whose matching category is not present in any input row
    of the group are filled with nulls.
   </P
><P
>    In practice the <TT
CLASS="PARAMETER"
>source_sql</TT
> query should always
    specify <TT
CLASS="LITERAL"
>ORDER BY 1</TT
> to ensure that values with the same
    <TT
CLASS="STRUCTFIELD"
>row_name</TT
> 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
    <TT
CLASS="PARAMETER"
>category_sql</TT
> query's output matches the specified
    output column order.
   </P
><P
>    Here are two complete examples:
</P><PRE
CLASS="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)</PRE
><P>

</P><PRE
CLASS="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)</PRE
><P>
   </P
><P
>    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 <CODE
CLASS="FUNCTION"
>crosstab</CODE
> is named <TT
CLASS="LITERAL"
>crosstab_hash</TT
>.
   </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN159010"
>F.36.1.5. <CODE
CLASS="FUNCTION"
>connectby</CODE
></A
></H3
><PRE
CLASS="SYNOPSIS"
>connectby(text relname, text keyid_fld, text parent_keyid_fld
          [, text orderby_fld ], text start_with, int max_depth
          [, text branch_delim ])</PRE
><P
>    The <CODE
CLASS="FUNCTION"
>connectby</CODE
> 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.  <CODE
CLASS="FUNCTION"
>connectby</CODE
> can display the
    sub-tree descending from any row.
   </P
><P
>    <A
HREF="tablefunc.html#TABLEFUNC-CONNECTBY-PARAMETERS"
>Table F-29</A
> explains the
    parameters.
   </P
><DIV
CLASS="TABLE"
><A
NAME="TABLEFUNC-CONNECTBY-PARAMETERS"
></A
><P
><B
>Table F-29. <CODE
CLASS="FUNCTION"
>connectby</CODE
> Parameters</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Parameter</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="PARAMETER"
>relname</TT
></TD
><TD
>Name of the source relation</TD
></TR
><TR
><TD
><TT
CLASS="PARAMETER"
>keyid_fld</TT
></TD
><TD
>Name of the key field</TD
></TR
><TR
><TD
><TT
CLASS="PARAMETER"
>parent_keyid_fld</TT
></TD
><TD
>Name of the parent-key field</TD
></TR
><TR
><TD
><TT
CLASS="PARAMETER"
>orderby_fld</TT
></TD
><TD
>Name of the field to order siblings by (optional)</TD
></TR
><TR
><TD
><TT
CLASS="PARAMETER"
>start_with</TT
></TD
><TD
>Key value of the row to start at</TD
></TR
><TR
><TD
><TT
CLASS="PARAMETER"
>max_depth</TT
></TD
><TD
>Maximum depth to descend to, or zero for unlimited depth</TD
></TR
><TR
><TD
><TT
CLASS="PARAMETER"
>branch_delim</TT
></TD
><TD
>String to separate keys with in branch output (optional)</TD
></TR
></TBODY
></TABLE
></DIV
><P
>     The key and parent-key fields can be any data type, but they must be
     the same type.  Note that the <TT
CLASS="PARAMETER"
>start_with</TT
> value must be
     entered as a text string, regardless of the type of the key field.
    </P
><P
>     The <CODE
CLASS="FUNCTION"
>connectby</CODE
> function is declared to return <TT
CLASS="TYPE"
>setof
     record</TT
>, so the actual names and types of the output columns must be
     defined in the <TT
CLASS="LITERAL"
>FROM</TT
> clause of the calling <TT
CLASS="COMMAND"
>SELECT</TT
>
     statement, for example:
    </P
><PRE
CLASS="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);</PRE
><P
>     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
     <TT
CLASS="TYPE"
>integer</TT
>.  If a <TT
CLASS="PARAMETER"
>branch_delim</TT
> parameter was
     given, the next output column is the branch display and must be of type
     <TT
CLASS="TYPE"
>text</TT
>.  Finally, if an <TT
CLASS="PARAMETER"
>orderby_fld</TT
>
     parameter was given, the last output column is a serial number, and must
     be of type <TT
CLASS="TYPE"
>integer</TT
>.
    </P
><P
>     The <SPAN
CLASS="QUOTE"
>"branch"</SPAN
> output column shows the path of keys taken to
     reach the current row.  The keys are separated by the specified
     <TT
CLASS="PARAMETER"
>branch_delim</TT
> string.  If no branch display is
     wanted, omit both the <TT
CLASS="PARAMETER"
>branch_delim</TT
> parameter
     and the branch column in the output column list.
    </P
><P
>     If the ordering of siblings of the same parent is important,
     include the <TT
CLASS="PARAMETER"
>orderby_fld</TT
> 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
     <TT
CLASS="PARAMETER"
>orderby_fld</TT
> is specified.
    </P
><P
>     The parameters representing table and field names are copied as-is
     into the SQL queries that <CODE
CLASS="FUNCTION"
>connectby</CODE
> 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.
    </P
><P
>     In large tables, performance will be poor unless there is an index on
     the parent-key field.
    </P
><P
>     It is important that the <TT
CLASS="PARAMETER"
>branch_delim</TT
> string
     not appear in any key values, else <CODE
CLASS="FUNCTION"
>connectby</CODE
> may incorrectly
     report an infinite-recursion error.  Note that if
     <TT
CLASS="PARAMETER"
>branch_delim</TT
> is not provided, a default value
     of <TT
CLASS="LITERAL"
>~</TT
> is used for recursion detection purposes.
     
    </P
><P
>     Here is an example:
</P><PRE
CLASS="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)</PRE
><P>
    </P
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN159089"
>F.36.2. Author</A
></H2
><P
>   Joe Conway
  </P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sslinfo.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="tcn.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>sslinfo</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="contrib.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>tcn</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>