begriffs open source - ai-pg/blob - full-docs/src/sgml/html/tablefunc.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.43. tablefunc — functions that return tables (crosstab and others)</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sslinfo.html" title="F.42. sslinfo — obtain client SSL information" /><link rel="next" href="tcn.html" title="F.44. tcn — a trigger function to notify listeners of changes to table content" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.43. tablefunc — functions that return tables (<code class="function">crosstab</code> and others)</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sslinfo.html" title="F.42. sslinfo — obtain client SSL information">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="tcn.html" title="F.44. tcn — a trigger function to notify listeners of changes to table content">Next</a></td></tr></table><hr /></div><div class="sect1" id="TABLEFUNC"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.43. tablefunc — functions that return tables (<code class="function">crosstab</code> and others) <a href="#TABLEFUNC" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="tablefunc.html#TABLEFUNC-FUNCTIONS-SECT">F.43.1. Functions Provided</a></span></dt><dt><span class="sect2"><a href="tablefunc.html#TABLEFUNC-AUTHOR">F.43.2. Author</a></span></dt></dl></div><a id="id-1.11.7.53.2" class="indexterm"></a><p>
   3   The <code class="filename">tablefunc</code> module includes various functions that return
   4   tables (that is, multiple rows).  These functions are useful both in their
   5   own right and as examples of how to write C functions that return
   6   multiple rows.
   7  </p><p>
   8   This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be
   9   installed by non-superusers who have <code class="literal">CREATE</code> privilege
  10   on the current database.
  11  </p><div class="sect2" id="TABLEFUNC-FUNCTIONS-SECT"><div class="titlepage"><div><div><h3 class="title">F.43.1. Functions Provided <a href="#TABLEFUNC-FUNCTIONS-SECT" class="id_link">#</a></h3></div></div></div><p>
  12    <a class="xref" href="tablefunc.html#TABLEFUNC-FUNCTIONS" title="Table F.33. tablefunc Functions">Table F.33</a> summarizes the functions provided
  13    by the <code class="filename">tablefunc</code> module.
  14   </p><div class="table" id="TABLEFUNC-FUNCTIONS"><p class="title"><strong>Table F.33. <code class="filename">tablefunc</code> Functions</strong></p><div class="table-contents"><table class="table" summary="tablefunc Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
  15         Function
  16        </p>
  17        <p>
  18         Description
  19        </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
  20         <code class="function">normal_rand</code> ( <em class="parameter"><code>numvals</code></em> <code class="type">integer</code>, <em class="parameter"><code>mean</code></em> <code class="type">float8</code>, <em class="parameter"><code>stddev</code></em> <code class="type">float8</code> )
  21         → <code class="returnvalue">setof float8</code>
  22        </p>
  23        <p>
  24         Produces a set of normally distributed random values.
  25        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
  26         <code class="function">crosstab</code> ( <em class="parameter"><code>sql</code></em> <code class="type">text</code> )
  27         → <code class="returnvalue">setof record</code>
  28        </p>
  29        <p>
  30         Produces a <span class="quote">“<span class="quote">pivot table</span>”</span> containing
  31         row names plus <em class="replaceable"><code>N</code></em> value columns, where
  32         <em class="replaceable"><code>N</code></em> is determined by the row type specified
  33         in the calling query.
  34        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
  35         <code class="function">crosstab<em class="replaceable"><code>N</code></em></code> ( <em class="parameter"><code>sql</code></em> <code class="type">text</code> )
  36         → <code class="returnvalue">setof table_crosstab_<em class="replaceable"><code>N</code></em></code>
  37        </p>
  38        <p>
  39         Produces a <span class="quote">“<span class="quote">pivot table</span>”</span> containing
  40         row names plus <em class="replaceable"><code>N</code></em> value columns.
  41         <code class="function">crosstab2</code>, <code class="function">crosstab3</code>, and
  42         <code class="function">crosstab4</code> are predefined, but you can create additional
  43         <code class="function">crosstab<em class="replaceable"><code>N</code></em></code> functions as described below.
  44        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
  45         <code class="function">crosstab</code> ( <em class="parameter"><code>source_sql</code></em> <code class="type">text</code>, <em class="parameter"><code>category_sql</code></em> <code class="type">text</code> )
  46         → <code class="returnvalue">setof record</code>
  47        </p>
  48        <p>
  49         Produces a <span class="quote">“<span class="quote">pivot table</span>”</span>
  50         with the value columns specified by a second query.
  51        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
  52         <code class="function">crosstab</code> ( <em class="parameter"><code>sql</code></em> <code class="type">text</code>, <em class="parameter"><code>N</code></em> <code class="type">integer</code> )
  53         → <code class="returnvalue">setof record</code>
  54        </p>
  55        <p>
  56         Obsolete version of <code class="function">crosstab(text)</code>.
  57         The parameter <em class="parameter"><code>N</code></em> is now ignored, since the
  58         number of value columns is always determined by the calling query.
  59        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
  60         <a id="id-1.11.7.53.5.3.2.2.6.1.1.1" class="indexterm"></a>
  61         <code class="function">connectby</code> ( <em class="parameter"><code>relname</code></em> <code class="type">text</code>, <em class="parameter"><code>keyid_fld</code></em> <code class="type">text</code>, <em class="parameter"><code>parent_keyid_fld</code></em> <code class="type">text</code>
  62         [<span class="optional">, <em class="parameter"><code>orderby_fld</code></em> <code class="type">text</code> </span>], <em class="parameter"><code>start_with</code></em> <code class="type">text</code>, <em class="parameter"><code>max_depth</code></em> <code class="type">integer</code>
  63         [<span class="optional">, <em class="parameter"><code>branch_delim</code></em> <code class="type">text</code> </span>] )
  64         → <code class="returnvalue">setof record</code>
  65        </p>
  66        <p>
  67         Produces a representation of a hierarchical tree structure.
  68         </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="sect3" id="TABLEFUNC-FUNCTIONS-NORMAL-RAND"><div class="titlepage"><div><div><h4 class="title">F.43.1.1. <code class="function">normal_rand</code> <a href="#TABLEFUNC-FUNCTIONS-NORMAL-RAND" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.53.5.4.2" class="indexterm"></a><pre class="synopsis">
  69 normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8
  70 </pre><p>
  71      <code class="function">normal_rand</code> produces a set of normally distributed random
  72      values (Gaussian distribution).
  73     </p><p>
  74      <em class="parameter"><code>numvals</code></em> is the number of values to be returned
  75      from the function. <em class="parameter"><code>mean</code></em> is the mean of the normal
  76      distribution of values and <em class="parameter"><code>stddev</code></em> is the standard
  77      deviation of the normal distribution of values.
  78     </p><p>
  79      For example, this call requests 1000 values with a mean of 5 and a
  80      standard deviation of 3:
  81     </p><pre class="screen">
  82 test=# SELECT * FROM normal_rand(1000, 5, 3);
  83      normal_rand
  84 ----------------------
  85      1.56556322244898
  86      9.10040991424657
  87      5.36957140345079
  88    -0.369151492880995
  89     0.283600703686639
  90        .
  91        .
  92        .
  93      4.82992125404908
  94      9.71308014517282
  95      2.49639286969028
  96 (1000 rows)
  97 </pre></div><div class="sect3" id="TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT"><div class="titlepage"><div><div><h4 class="title">F.43.1.2. <code class="function">crosstab(text)</code> <a href="#TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.53.5.5.2" class="indexterm"></a><pre class="synopsis">
  98 crosstab(text sql)
  99 crosstab(text sql, int N)
 100 </pre><p>
 101     The <code class="function">crosstab</code> function is used to produce <span class="quote">“<span class="quote">pivot</span>”</span>
 102     displays, wherein data is listed across the page rather than down.
 103     For example, we might have data like
 104 </p><pre class="programlisting">
 105 row1    val11
 106 row1    val12
 107 row1    val13
 108 ...
 109 row2    val21
 110 row2    val22
 111 row2    val23
 112 ...
 113 </pre><p>
 114     which we wish to display like
 115 </p><pre class="programlisting">
 116 row1    val11   val12   val13   ...
 117 row2    val21   val22   val23   ...
 118 ...
 119 </pre><p>
 120     The <code class="function">crosstab</code> function takes a text parameter that is an SQL
 121     query producing raw data formatted in the first way, and produces a table
 122     formatted in the second way.
 123    </p><p>
 124     The <em class="parameter"><code>sql</code></em> parameter is an SQL statement that produces
 125     the source set of data. This statement must return one
 126     <code class="structfield">row_name</code> column, one
 127     <code class="structfield">category</code> column, and one
 128     <code class="structfield">value</code> column.  <em class="parameter"><code>N</code></em> is an
 129     obsolete parameter, ignored if supplied (formerly this had to match the
 130     number of output value columns, but now that is determined by the
 131     calling query).
 132    </p><p>
 133     For example, the provided query might produce a set something like:
 134 </p><pre class="programlisting">
 135  row_name    cat    value
 136 ----------+-------+-------
 137   row1      cat1    val1
 138   row1      cat2    val2
 139   row1      cat3    val3
 140   row1      cat4    val4
 141   row2      cat1    val5
 142   row2      cat2    val6
 143   row2      cat3    val7
 144   row2      cat4    val8
 145 </pre><p>
 146    </p><p>
 147     The <code class="function">crosstab</code> function is declared to return <code class="type">setof
 148     record</code>, so the actual names and types of the output columns must be
 149     defined in the <code class="literal">FROM</code> clause of the calling <code class="command">SELECT</code>
 150     statement, for example:
 151 </p><pre class="programlisting">
 152 SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);
 153 </pre><p>
 154     This example produces a set something like:
 155 </p><pre class="programlisting">
 156            &lt;== value  columns  ==&gt;
 157  row_name   category_1   category_2
 158 ----------+------------+------------
 159   row1        val1         val2
 160   row2        val5         val6
 161 </pre><p>
 162    </p><p>
 163     The <code class="literal">FROM</code> clause must define the output as one
 164     <code class="structfield">row_name</code> column (of the same data type as the first result
 165     column of the SQL query) followed by N <code class="structfield">value</code> columns
 166     (all of the same data type as the third result column of the SQL query).
 167     You can set up as many output value columns as you wish.  The names of the
 168     output columns are up to you.
 169    </p><p>
 170     The <code class="function">crosstab</code> function produces one output row for each
 171     consecutive group of input rows with the same
 172     <code class="structfield">row_name</code> value.  It fills the output
 173     <code class="structfield">value</code> columns, left to right, with the
 174     <code class="structfield">value</code> fields from these rows.  If there
 175     are fewer rows in a group than there are output <code class="structfield">value</code>
 176     columns, the extra output columns are filled with nulls; if there are
 177     more rows, the extra input rows are skipped.
 178    </p><p>
 179     In practice the SQL query should always specify <code class="literal">ORDER BY 1,2</code>
 180     to ensure that the input rows are properly ordered, that is, values with
 181     the same <code class="structfield">row_name</code> are brought together and
 182     correctly ordered within the row.  Notice that <code class="function">crosstab</code>
 183     itself does not pay any attention to the second column of the query
 184     result; it's just there to be ordered by, to control the order in which
 185     the third-column values appear across the page.
 186    </p><p>
 187     Here is a complete example:
 188 </p><pre class="programlisting">
 189 CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
 190 INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
 191 INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
 192 INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
 193 INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
 194 INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
 195 INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
 196 INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
 197 INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');
 198
 199 SELECT *
 200 FROM crosstab(
 201   'select rowid, attribute, value
 202    from ct
 203    where attribute = ''att2'' or attribute = ''att3''
 204    order by 1,2')
 205 AS ct(row_name text, category_1 text, category_2 text, category_3 text);
 206
 207  row_name | category_1 | category_2 | category_3
 208 ----------+------------+------------+------------
 209  test1    | val2       | val3       |
 210  test2    | val6       | val7       |
 211 (2 rows)
 212 </pre><p>
 213    </p><p>
 214     You can avoid always having to write out a <code class="literal">FROM</code> clause to
 215     define the output columns, by setting up a custom crosstab function that
 216     has the desired output row type wired into its definition.  This is
 217     described in the next section.  Another possibility is to embed the
 218     required <code class="literal">FROM</code> clause in a view definition.
 219    </p><div class="note"><h3 class="title">Note</h3><p>
 220      See also the <code class="command"><a class="link" href="app-psql.html#APP-PSQL-META-COMMANDS-CROSSTABVIEW">\crosstabview</a></code>
 221      command in <span class="application">psql</span>, which provides functionality similar
 222      to <code class="function">crosstab()</code>.
 223     </p></div></div><div class="sect3" id="TABLEFUNC-FUNCTIONS-CROSSTAB-N-TEXT"><div class="titlepage"><div><div><h4 class="title">F.43.1.3. <code class="function">crosstab<em class="replaceable"><code>N</code></em>(text)</code> <a href="#TABLEFUNC-FUNCTIONS-CROSSTAB-N-TEXT" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.53.5.6.2" class="indexterm"></a><pre class="synopsis">
 224 crosstab<em class="replaceable"><code>N</code></em>(text sql)
 225 </pre><p>
 226      The <code class="function">crosstab<em class="replaceable"><code>N</code></em></code> functions are examples of how
 227      to set up custom wrappers for the general <code class="function">crosstab</code> function,
 228      so that you need not write out column names and types in the calling
 229      <code class="command">SELECT</code> query.  The <code class="filename">tablefunc</code> module includes
 230      <code class="function">crosstab2</code>, <code class="function">crosstab3</code>, and
 231      <code class="function">crosstab4</code>, whose output row types are defined as
 232     </p><pre class="programlisting">
 233 CREATE TYPE tablefunc_crosstab_N AS (
 234     row_name TEXT,
 235     category_1 TEXT,
 236     category_2 TEXT,
 237         .
 238         .
 239         .
 240     category_N TEXT
 241 );
 242 </pre><p>
 243      Thus, these functions can be used directly when the input query produces
 244      <code class="structfield">row_name</code> and <code class="structfield">value</code> columns of type
 245      <code class="type">text</code>, and you want 2, 3, or 4 output values columns.
 246      In all other ways they behave exactly as described above for the
 247      general <code class="function">crosstab</code> function.
 248     </p><p>
 249      For instance, the example given in the previous section would also
 250      work as
 251 </p><pre class="programlisting">
 252 SELECT *
 253 FROM crosstab3(
 254   'select rowid, attribute, value
 255    from ct
 256    where attribute = ''att2'' or attribute = ''att3''
 257    order by 1,2');
 258 </pre><p>
 259     </p><p>
 260      These functions are provided mostly for illustration purposes. You
 261      can create your own return types and functions based on the
 262      underlying <code class="function">crosstab()</code> function.  There are two ways
 263      to do it:
 264
 265     </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
 266        Create a composite type describing the desired output columns,
 267        similar to the examples in
 268        <code class="filename">contrib/tablefunc/tablefunc--1.0.sql</code>.
 269        Then define a
 270        unique function name accepting one <code class="type">text</code> parameter and returning
 271        <code class="type">setof your_type_name</code>, but linking to the same underlying
 272        <code class="function">crosstab</code> C function.  For example, if your source data
 273        produces row names that are <code class="type">text</code>, and values that are
 274        <code class="type">float8</code>, and you want 5 value columns:
 275 </p><pre class="programlisting">
 276 CREATE TYPE my_crosstab_float8_5_cols AS (
 277     my_row_name text,
 278     my_category_1 float8,
 279     my_category_2 float8,
 280     my_category_3 float8,
 281     my_category_4 float8,
 282     my_category_5 float8
 283 );
 284
 285 CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
 286     RETURNS setof my_crosstab_float8_5_cols
 287     AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
 288 </pre><p>
 289       </p></li><li class="listitem"><p>
 290        Use <code class="literal">OUT</code> parameters to define the return type implicitly.
 291        The same example could also be done this way:
 292 </p><pre class="programlisting">
 293 CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(
 294     IN text,
 295     OUT my_row_name text,
 296     OUT my_category_1 float8,
 297     OUT my_category_2 float8,
 298     OUT my_category_3 float8,
 299     OUT my_category_4 float8,
 300     OUT my_category_5 float8)
 301   RETURNS setof record
 302   AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
 303 </pre><p>
 304       </p></li></ul></div><p>
 305     </p></div><div class="sect3" id="TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT-2"><div class="titlepage"><div><div><h4 class="title">F.43.1.4. <code class="function">crosstab(text, text)</code> <a href="#TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT-2" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.53.5.7.2" class="indexterm"></a><pre class="synopsis">
 306 crosstab(text source_sql, text category_sql)
 307 </pre><p>
 308     The main limitation of the single-parameter form of <code class="function">crosstab</code>
 309     is that it treats all values in a group alike, inserting each value into
 310     the first available column.  If you want the value
 311     columns to correspond to specific categories of data, and some groups
 312     might not have data for some of the categories, that doesn't work well.
 313     The two-parameter form of <code class="function">crosstab</code> handles this case by
 314     providing an explicit list of the categories corresponding to the
 315     output columns.
 316    </p><p>
 317     <em class="parameter"><code>source_sql</code></em> is an SQL statement that produces the
 318     source set of data.  This statement must return one
 319     <code class="structfield">row_name</code> column, one
 320     <code class="structfield">category</code> column, and one
 321     <code class="structfield">value</code> column. It may also have one or more
 322     <span class="quote">“<span class="quote">extra</span>”</span> columns.
 323     The <code class="structfield">row_name</code> column must be first. The
 324     <code class="structfield">category</code> and <code class="structfield">value</code>
 325     columns must be the last two columns, in that order.  Any columns between
 326     <code class="structfield">row_name</code> and
 327     <code class="structfield">category</code> are treated as <span class="quote">“<span class="quote">extra</span>”</span>.
 328     The <span class="quote">“<span class="quote">extra</span>”</span> columns are expected to be the same for all rows
 329     with the same <code class="structfield">row_name</code> value.
 330    </p><p>
 331     For example, <em class="parameter"><code>source_sql</code></em> might produce a set
 332     something like:
 333 </p><pre class="programlisting">
 334 SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;
 335
 336  row_name    extra_col   cat    value
 337 ----------+------------+-----+---------
 338   row1         extra1    cat1    val1
 339   row1         extra1    cat2    val2
 340   row1         extra1    cat4    val4
 341   row2         extra2    cat1    val5
 342   row2         extra2    cat2    val6
 343   row2         extra2    cat3    val7
 344   row2         extra2    cat4    val8
 345 </pre><p>
 346    </p><p>
 347     <em class="parameter"><code>category_sql</code></em> is an SQL statement that produces
 348     the set of categories. This statement must return only one column.
 349     It must produce at least one row, or an error will be generated.
 350     Also, it must not produce duplicate values, or an error will be
 351     generated.  <em class="parameter"><code>category_sql</code></em> might be something like:
 352
 353 </p><pre class="programlisting">
 354 SELECT DISTINCT cat FROM foo ORDER BY 1;
 355     cat
 356   -------
 357     cat1
 358     cat2
 359     cat3
 360     cat4
 361 </pre><p>
 362    </p><p>
 363     The <code class="function">crosstab</code> function is declared to return <code class="type">setof
 364     record</code>, so the actual names and types of the output columns must be
 365     defined in the <code class="literal">FROM</code> clause of the calling <code class="command">SELECT</code>
 366     statement, for example:
 367
 368 </p><pre class="programlisting">
 369 SELECT * FROM crosstab('...', '...')
 370     AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);
 371 </pre><p>
 372    </p><p>
 373     This will produce a result something like:
 374 </p><pre class="programlisting">
 375                   &lt;==  value  columns   ==&gt;
 376 row_name   extra   cat1   cat2   cat3   cat4
 377 ---------+-------+------+------+------+------
 378   row1     extra1  val1   val2          val4
 379   row2     extra2  val5   val6   val7   val8
 380 </pre><p>
 381    </p><p>
 382     The <code class="literal">FROM</code> clause must define the proper number of output
 383     columns of the proper data types.  If there are <em class="replaceable"><code>N</code></em>
 384     columns in the <em class="parameter"><code>source_sql</code></em> query's result, the first
 385     <em class="replaceable"><code>N</code></em>-2 of them must match up with the first
 386     <em class="replaceable"><code>N</code></em>-2 output columns.  The remaining output columns
 387     must have the type of the last column of the <em class="parameter"><code>source_sql</code></em>
 388     query's result, and there must be exactly as many of them as there
 389     are rows in the <em class="parameter"><code>category_sql</code></em> query's result.
 390    </p><p>
 391     The <code class="function">crosstab</code> function produces one output row for each
 392     consecutive group of input rows with the same
 393     <code class="structfield">row_name</code> value.  The output
 394     <code class="structfield">row_name</code> column, plus any <span class="quote">“<span class="quote">extra</span>”</span>
 395     columns, are copied from the first row of the group.  The output
 396     <code class="structfield">value</code> columns are filled with the
 397     <code class="structfield">value</code> fields from rows having matching
 398     <code class="structfield">category</code> values.  If a row's <code class="structfield">category</code>
 399     does not match any output of the <em class="parameter"><code>category_sql</code></em>
 400     query, its <code class="structfield">value</code> is ignored.  Output
 401     columns whose matching category is not present in any input row
 402     of the group are filled with nulls.
 403    </p><p>
 404     In practice the <em class="parameter"><code>source_sql</code></em> query should always
 405     specify <code class="literal">ORDER BY 1</code> to ensure that values with the same
 406     <code class="structfield">row_name</code> are brought together.  However,
 407     ordering of the categories within a group is not important.
 408     Also, it is essential to be sure that the order of the
 409     <em class="parameter"><code>category_sql</code></em> query's output matches the specified
 410     output column order.
 411    </p><p>
 412     Here are two complete examples:
 413 </p><pre class="programlisting">
 414 create table sales(year int, month int, qty int);
 415 insert into sales values(2007, 1, 1000);
 416 insert into sales values(2007, 2, 1500);
 417 insert into sales values(2007, 7, 500);
 418 insert into sales values(2007, 11, 1500);
 419 insert into sales values(2007, 12, 2000);
 420 insert into sales values(2008, 1, 1000);
 421
 422 select * from crosstab(
 423   'select year, month, qty from sales order by 1',
 424   'select m from generate_series(1,12) m'
 425 ) as (
 426   year int,
 427   "Jan" int,
 428   "Feb" int,
 429   "Mar" int,
 430   "Apr" int,
 431   "May" int,
 432   "Jun" int,
 433   "Jul" int,
 434   "Aug" int,
 435   "Sep" int,
 436   "Oct" int,
 437   "Nov" int,
 438   "Dec" int
 439 );
 440  year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
 441 ------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 442  2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 443  2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
 444 (2 rows)
 445 </pre><p>
 446
 447 </p><pre class="programlisting">
 448 CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
 449 INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
 450 INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
 451 INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
 452 INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
 453 INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
 454 INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
 455 INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');
 456
 457 SELECT * FROM crosstab
 458 (
 459   'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 460   'SELECT DISTINCT attribute FROM cth ORDER BY 1'
 461 )
 462 AS
 463 (
 464        rowid text,
 465        rowdt timestamp,
 466        temperature int4,
 467        test_result text,
 468        test_startdate timestamp,
 469        volts float8
 470 );
 471  rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
 472 -------+--------------------------+-------------+-------------+--------------------------+--------
 473  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
 474  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
 475 (2 rows)
 476 </pre><p>
 477    </p><p>
 478     You can create predefined functions to avoid having to write out
 479     the result column names and types in each query.  See the examples
 480     in the previous section.  The underlying C function for this form
 481     of <code class="function">crosstab</code> is named <code class="literal">crosstab_hash</code>.
 482    </p></div><div class="sect3" id="TABLEFUNC-FUNCTIONS-CONNECTBY"><div class="titlepage"><div><div><h4 class="title">F.43.1.5. <code class="function">connectby</code> <a href="#TABLEFUNC-FUNCTIONS-CONNECTBY" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.53.5.8.2" class="indexterm"></a><pre class="synopsis">
 483 connectby(text relname, text keyid_fld, text parent_keyid_fld
 484           [, text orderby_fld ], text start_with, int max_depth
 485           [, text branch_delim ])
 486 </pre><p>
 487     The <code class="function">connectby</code> function produces a display of hierarchical
 488     data that is stored in a table.  The table must have a key field that
 489     uniquely identifies rows, and a parent-key field that references the
 490     parent (if any) of each row.  <code class="function">connectby</code> can display the
 491     sub-tree descending from any row.
 492    </p><p>
 493     <a class="xref" href="tablefunc.html#TABLEFUNC-CONNECTBY-PARAMETERS" title="Table F.34. connectby Parameters">Table F.34</a> explains the
 494     parameters.
 495    </p><div class="table" id="TABLEFUNC-CONNECTBY-PARAMETERS"><p class="title"><strong>Table F.34. <code class="function">connectby</code> Parameters</strong></p><div class="table-contents"><table class="table" summary="connectby Parameters" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td><em class="parameter"><code>relname</code></em></td><td>Name of the source relation</td></tr><tr><td><em class="parameter"><code>keyid_fld</code></em></td><td>Name of the key field</td></tr><tr><td><em class="parameter"><code>parent_keyid_fld</code></em></td><td>Name of the parent-key field</td></tr><tr><td><em class="parameter"><code>orderby_fld</code></em></td><td>Name of the field to order siblings by (optional)</td></tr><tr><td><em class="parameter"><code>start_with</code></em></td><td>Key value of the row to start at</td></tr><tr><td><em class="parameter"><code>max_depth</code></em></td><td>Maximum depth to descend to, or zero for unlimited depth</td></tr><tr><td><em class="parameter"><code>branch_delim</code></em></td><td>String to separate keys with in branch output (optional)</td></tr></tbody></table></div></div><br class="table-break" /><p>
 496      The key and parent-key fields can be any data type, but they must be
 497      the same type.  Note that the <em class="parameter"><code>start_with</code></em> value must be
 498      entered as a text string, regardless of the type of the key field.
 499     </p><p>
 500      The <code class="function">connectby</code> function is declared to return <code class="type">setof
 501      record</code>, so the actual names and types of the output columns must be
 502      defined in the <code class="literal">FROM</code> clause of the calling <code class="command">SELECT</code>
 503      statement, for example:
 504     </p><pre class="programlisting">
 505 SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
 506     AS t(keyid text, parent_keyid text, level int, branch text, pos int);
 507 </pre><p>
 508      The first two output columns are used for the current row's key and
 509      its parent row's key; they must match the type of the table's key field.
 510      The third output column is the depth in the tree and must be of type
 511      <code class="type">integer</code>.  If a <em class="parameter"><code>branch_delim</code></em> parameter was
 512      given, the next output column is the branch display and must be of type
 513      <code class="type">text</code>.  Finally, if an <em class="parameter"><code>orderby_fld</code></em>
 514      parameter was given, the last output column is a serial number, and must
 515      be of type <code class="type">integer</code>.
 516     </p><p>
 517      The <span class="quote">“<span class="quote">branch</span>”</span> output column shows the path of keys taken to
 518      reach the current row.  The keys are separated by the specified
 519      <em class="parameter"><code>branch_delim</code></em> string.  If no branch display is
 520      wanted, omit both the <em class="parameter"><code>branch_delim</code></em> parameter
 521      and the branch column in the output column list.
 522     </p><p>
 523      If the ordering of siblings of the same parent is important,
 524      include the <em class="parameter"><code>orderby_fld</code></em> parameter to
 525      specify which field to order siblings by.  This field can be of any
 526      sortable data type.  The output column list must include a final
 527      integer serial-number column, if and only if
 528      <em class="parameter"><code>orderby_fld</code></em> is specified.
 529     </p><p>
 530      The parameters representing table and field names are copied as-is
 531      into the SQL queries that <code class="function">connectby</code> generates internally.
 532      Therefore, include double quotes if the names are mixed-case or contain
 533      special characters.  You may also need to schema-qualify the table name.
 534     </p><p>
 535      In large tables, performance will be poor unless there is an index on
 536      the parent-key field.
 537     </p><p>
 538      It is important that the <em class="parameter"><code>branch_delim</code></em> string
 539      not appear in any key values, else <code class="function">connectby</code> may incorrectly
 540      report an infinite-recursion error.  Note that if
 541      <em class="parameter"><code>branch_delim</code></em> is not provided, a default value
 542      of <code class="literal">~</code> is used for recursion detection purposes.
 543
 544     </p><p>
 545      Here is an example:
 546 </p><pre class="programlisting">
 547 CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
 548
 549 INSERT INTO connectby_tree VALUES('row1',NULL, 0);
 550 INSERT INTO connectby_tree VALUES('row2','row1', 0);
 551 INSERT INTO connectby_tree VALUES('row3','row1', 0);
 552 INSERT INTO connectby_tree VALUES('row4','row2', 1);
 553 INSERT INTO connectby_tree VALUES('row5','row2', 0);
 554 INSERT INTO connectby_tree VALUES('row6','row4', 0);
 555 INSERT INTO connectby_tree VALUES('row7','row3', 0);
 556 INSERT INTO connectby_tree VALUES('row8','row6', 0);
 557 INSERT INTO connectby_tree VALUES('row9','row5', 0);
 558
 559 -- with branch, without orderby_fld (order of results is not guaranteed)
 560 SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
 561  AS t(keyid text, parent_keyid text, level int, branch text);
 562  keyid | parent_keyid | level |       branch
 563 -------+--------------+-------+---------------------
 564  row2  |              |     0 | row2
 565  row4  | row2         |     1 | row2~row4
 566  row6  | row4         |     2 | row2~row4~row6
 567  row8  | row6         |     3 | row2~row4~row6~row8
 568  row5  | row2         |     1 | row2~row5
 569  row9  | row5         |     2 | row2~row5~row9
 570 (6 rows)
 571
 572 -- without branch, without orderby_fld (order of results is not guaranteed)
 573 SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
 574  AS t(keyid text, parent_keyid text, level int);
 575  keyid | parent_keyid | level
 576 -------+--------------+-------
 577  row2  |              |     0
 578  row4  | row2         |     1
 579  row6  | row4         |     2
 580  row8  | row6         |     3
 581  row5  | row2         |     1
 582  row9  | row5         |     2
 583 (6 rows)
 584
 585 -- with branch, with orderby_fld (notice that row5 comes before row4)
 586 SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
 587  AS t(keyid text, parent_keyid text, level int, branch text, pos int);
 588  keyid | parent_keyid | level |       branch        | pos
 589 -------+--------------+-------+---------------------+-----
 590  row2  |              |     0 | row2                |   1
 591  row5  | row2         |     1 | row2~row5           |   2
 592  row9  | row5         |     2 | row2~row5~row9      |   3
 593  row4  | row2         |     1 | row2~row4           |   4
 594  row6  | row4         |     2 | row2~row4~row6      |   5
 595  row8  | row6         |     3 | row2~row4~row6~row8 |   6
 596 (6 rows)
 597
 598 -- without branch, with orderby_fld (notice that row5 comes before row4)
 599 SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
 600  AS t(keyid text, parent_keyid text, level int, pos int);
 601  keyid | parent_keyid | level | pos
 602 -------+--------------+-------+-----
 603  row2  |              |     0 |   1
 604  row5  | row2         |     1 |   2
 605  row9  | row5         |     2 |   3
 606  row4  | row2         |     1 |   4
 607  row6  | row4         |     2 |   5
 608  row8  | row6         |     3 |   6
 609 (6 rows)
 610 </pre><p>
 611     </p></div></div><div class="sect2" id="TABLEFUNC-AUTHOR"><div class="titlepage"><div><div><h3 class="title">F.43.2. Author <a href="#TABLEFUNC-AUTHOR" class="id_link">#</a></h3></div></div></div><p>
 612    Joe Conway
 613   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sslinfo.html" title="F.42. sslinfo — obtain client SSL information">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="tcn.html" title="F.44. tcn — a trigger function to notify listeners of changes to table content">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.42. sslinfo — obtain client SSL information </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.44. tcn — a trigger function to notify listeners of changes to table content</td></tr></table></div></body></html>