diff options
Diffstat (limited to 'doc/src/sgml/html/tablefunc.html')
| -rw-r--r-- | doc/src/sgml/html/tablefunc.html | 613 |
1 files changed, 613 insertions, 0 deletions
diff --git a/doc/src/sgml/html/tablefunc.html b/doc/src/sgml/html/tablefunc.html new file mode 100644 index 0000000..2ebed85 --- /dev/null +++ b/doc/src/sgml/html/tablefunc.html @@ -0,0 +1,613 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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 16.2 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> + The <code class="filename">tablefunc</code> 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><p> + This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be + installed by non-superusers who have <code class="literal">CREATE</code> privilege + on the current database. + </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> + <a class="xref" href="tablefunc.html#TABLEFUNC-FUNCTIONS" title="Table F.32. tablefunc Functions">Table F.32</a> summarizes the functions provided + by the <code class="filename">tablefunc</code> module. + </p><div class="table" id="TABLEFUNC-FUNCTIONS"><p class="title"><strong>Table F.32. <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"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <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> ) + → <code class="returnvalue">setof float8</code> + </p> + <p> + Produces a set of normally distributed random values. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">crosstab</code> ( <em class="parameter"><code>sql</code></em> <code class="type">text</code> ) + → <code class="returnvalue">setof record</code> + </p> + <p> + Produces a <span class="quote">“<span class="quote">pivot table</span>”</span> containing + row names plus <em class="replaceable"><code>N</code></em> value columns, where + <em class="replaceable"><code>N</code></em> is determined by the row type specified + in the calling query. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">crosstab<em class="replaceable"><code>N</code></em></code> ( <em class="parameter"><code>sql</code></em> <code class="type">text</code> ) + → <code class="returnvalue">setof table_crosstab_<em class="replaceable"><code>N</code></em></code> + </p> + <p> + Produces a <span class="quote">“<span class="quote">pivot table</span>”</span> containing + row names plus <em class="replaceable"><code>N</code></em> 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<em class="replaceable"><code>N</code></em></code> functions as described below. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <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> ) + → <code class="returnvalue">setof record</code> + </p> + <p> + Produces a <span class="quote">“<span class="quote">pivot table</span>”</span> + with the value columns specified by a second query. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <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> ) + → <code class="returnvalue">setof record</code> + </p> + <p> + Obsolete version of <code class="function">crosstab(text)</code>. + The parameter <em class="parameter"><code>N</code></em> is now ignored, since the + number of value columns is always determined by the calling query. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.53.5.3.2.2.6.1.1.1" class="indexterm"></a> + <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> + [<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> + [<span class="optional">, <em class="parameter"><code>branch_delim</code></em> <code class="type">text</code> </span>] ) + → <code class="returnvalue">setof record</code> + </p> + <p> + Produces a representation of a hierarchical tree structure. + </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"> +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> + <em class="parameter"><code>numvals</code></em> is the number of values to be returned + from the function. <em class="parameter"><code>mean</code></em> is the mean of the normal + distribution of values and <em class="parameter"><code>stddev</code></em> 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" 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"> +crosstab(text sql) +crosstab(text sql, int N) +</pre><p> + The <code class="function">crosstab</code> function is used to produce <span class="quote">“<span class="quote">pivot</span>”</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 an SQL + query producing raw data formatted in the first way, and produces a table + formatted in the second way. + </p><p> + The <em class="parameter"><code>sql</code></em> parameter is an SQL statement that produces + the source set of data. This statement must return one + <code class="structfield">row_name</code> column, one + <code class="structfield">category</code> column, and one + <code class="structfield">value</code> column. <em class="parameter"><code>N</code></em> 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 <code class="type">setof + record</code>, so the actual names and types of the output columns must be + defined in the <code class="literal">FROM</code> clause of the calling <code class="command">SELECT</code> + 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"> + <== value columns ==> + row_name category_1 category_2 +----------+------------+------------ + row1 val1 val2 + row2 val5 val6 +</pre><p> + </p><p> + The <code class="literal">FROM</code> clause must define the output as one + <code class="structfield">row_name</code> column (of the same data type as the first result + column of the SQL query) followed by N <code class="structfield">value</code> 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 + <code class="structfield">row_name</code> value. It fills the output + <code class="structfield">value</code> columns, left to right, with the + <code class="structfield">value</code> fields from these rows. If there + are fewer rows in a group than there are output <code class="structfield">value</code> + 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 <code class="literal">ORDER BY 1,2</code> + to ensure that the input rows are properly ordered, that is, values with + the same <code class="structfield">row_name</code> 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 <code class="literal">FROM</code> 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 <code class="literal">FROM</code> clause in a view definition. + </p><div class="note"><h3 class="title">Note</h3><p> + See also the <code class="command"><a class="link" href="app-psql.html#APP-PSQL-META-COMMANDS-CROSSTABVIEW">\crosstabview</a></code> + command in <span class="application">psql</span>, which provides functionality similar + to <code class="function">crosstab()</code>. + </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"> +crosstab<em class="replaceable"><code>N</code></em>(text sql) +</pre><p> + The <code class="function">crosstab<em class="replaceable"><code>N</code></em></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 + <code class="command">SELECT</code> query. The <code class="filename">tablefunc</code> 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 + <code class="structfield">row_name</code> and <code class="structfield">value</code> columns of type + <code class="type">text</code>, 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><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + Create a composite type describing the desired output columns, + similar to the examples in + <code class="filename">contrib/tablefunc/tablefunc--1.0.sql</code>. + Then define a + unique function name accepting one <code class="type">text</code> parameter and returning + <code class="type">setof your_type_name</code>, but linking to the same underlying + <code class="function">crosstab</code> C function. For example, if your source data + produces row names that are <code class="type">text</code>, and values that are + <code class="type">float8</code>, 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 class="listitem"><p> + Use <code class="literal">OUT</code> 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></div><p> + </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"> +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> + <em class="parameter"><code>source_sql</code></em> is an SQL statement that produces the + source set of data. This statement must return one + <code class="structfield">row_name</code> column, one + <code class="structfield">category</code> column, and one + <code class="structfield">value</code> column. It may also have one or more + <span class="quote">“<span class="quote">extra</span>”</span> columns. + The <code class="structfield">row_name</code> column must be first. The + <code class="structfield">category</code> and <code class="structfield">value</code> + columns must be the last two columns, in that order. Any columns between + <code class="structfield">row_name</code> and + <code class="structfield">category</code> are treated as <span class="quote">“<span class="quote">extra</span>”</span>. + The <span class="quote">“<span class="quote">extra</span>”</span> columns are expected to be the same for all rows + with the same <code class="structfield">row_name</code> value. + </p><p> + For example, <em class="parameter"><code>source_sql</code></em> 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> + <em class="parameter"><code>category_sql</code></em> 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. <em class="parameter"><code>category_sql</code></em> 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 <code class="type">setof + record</code>, so the actual names and types of the output columns must be + defined in the <code class="literal">FROM</code> clause of the calling <code class="command">SELECT</code> + 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"> + <== value columns ==> +row_name extra cat1 cat2 cat3 cat4 +---------+-------+------+------+------+------ + row1 extra1 val1 val2 val4 + row2 extra2 val5 val6 val7 val8 +</pre><p> + </p><p> + The <code class="literal">FROM</code> clause must define the proper number of output + columns of the proper data types. If there are <em class="replaceable"><code>N</code></em> + columns in the <em class="parameter"><code>source_sql</code></em> query's result, the first + <em class="replaceable"><code>N</code></em>-2 of them must match up with the first + <em class="replaceable"><code>N</code></em>-2 output columns. The remaining output columns + must have the type of the last column of the <em class="parameter"><code>source_sql</code></em> + query's result, and there must be exactly as many of them as there + are rows in the <em class="parameter"><code>category_sql</code></em> 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 + <code class="structfield">row_name</code> value. The output + <code class="structfield">row_name</code> column, plus any <span class="quote">“<span class="quote">extra</span>”</span> + columns, are copied from the first row of the group. The output + <code class="structfield">value</code> columns are filled with the + <code class="structfield">value</code> fields from rows having matching + <code class="structfield">category</code> values. If a row's <code class="structfield">category</code> + does not match any output of the <em class="parameter"><code>category_sql</code></em> + query, its <code class="structfield">value</code> 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 <em class="parameter"><code>source_sql</code></em> query should always + specify <code class="literal">ORDER BY 1</code> to ensure that values with the same + <code class="structfield">row_name</code> 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 + <em class="parameter"><code>category_sql</code></em> 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 <code class="literal">crosstab_hash</code>. + </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"> +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 class="xref" href="tablefunc.html#TABLEFUNC-CONNECTBY-PARAMETERS" title="Table F.33. connectby Parameters">Table F.33</a> explains the + parameters. + </p><div class="table" id="TABLEFUNC-CONNECTBY-PARAMETERS"><p class="title"><strong>Table F.33. <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> + The key and parent-key fields can be any data type, but they must be + the same type. Note that the <em class="parameter"><code>start_with</code></em> 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 <code class="type">setof + record</code>, so the actual names and types of the output columns must be + defined in the <code class="literal">FROM</code> clause of the calling <code class="command">SELECT</code> + 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 + <code class="type">integer</code>. If a <em class="parameter"><code>branch_delim</code></em> parameter was + given, the next output column is the branch display and must be of type + <code class="type">text</code>. Finally, if an <em class="parameter"><code>orderby_fld</code></em> + parameter was given, the last output column is a serial number, and must + be of type <code class="type">integer</code>. + </p><p> + The <span class="quote">“<span class="quote">branch</span>”</span> output column shows the path of keys taken to + reach the current row. The keys are separated by the specified + <em class="parameter"><code>branch_delim</code></em> string. If no branch display is + wanted, omit both the <em class="parameter"><code>branch_delim</code></em> 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 <em class="parameter"><code>orderby_fld</code></em> 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 + <em class="parameter"><code>orderby_fld</code></em> 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 <em class="parameter"><code>branch_delim</code></em> string + not appear in any key values, else <code class="function">connectby</code> may incorrectly + report an infinite-recursion error. Note that if + <em class="parameter"><code>branch_delim</code></em> is not provided, a default value + of <code class="literal">~</code> 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" 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> + Joe Conway + </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 16.2 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>
\ No newline at end of file |