| 1 |
<!-- doc/src/sgml/tablefunc.sgml --> |
| 2 |
|
| 3 |
<sect1 id="tablefunc" xreflabel="tablefunc"> |
| 4 |
<title>tablefunc — functions that return tables (<function>crosstab</function> and others)</title> |
| 5 |
|
| 6 |
<indexterm zone="tablefunc"> |
| 7 |
<primary>tablefunc</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<para> |
| 11 |
The <filename>tablefunc</filename> module includes various functions that return |
| 12 |
tables (that is, multiple rows). These functions are useful both in their |
| 13 |
own right and as examples of how to write C functions that return |
| 14 |
multiple rows. |
| 15 |
</para> |
| 16 |
|
| 17 |
<para> |
| 18 |
This module is considered <quote>trusted</quote>, that is, it can be |
| 19 |
installed by non-superusers who have <literal>CREATE</literal> privilege |
| 20 |
on the current database. |
| 21 |
</para> |
| 22 |
|
| 23 |
<sect2 id="tablefunc-functions-sect"> |
| 24 |
<title>Functions Provided</title> |
| 25 |
|
| 26 |
<para> |
| 27 |
<xref linkend="tablefunc-functions"/> summarizes the functions provided |
| 28 |
by the <filename>tablefunc</filename> module. |
| 29 |
</para> |
| 30 |
|
| 31 |
<table id="tablefunc-functions"> |
| 32 |
<title><filename>tablefunc</filename> Functions</title> |
| 33 |
<tgroup cols="1"> |
| 34 |
<thead> |
| 35 |
<row> |
| 36 |
<entry role="func_table_entry"><para role="func_signature"> |
| 37 |
Function |
| 38 |
</para> |
| 39 |
<para> |
| 40 |
Description |
| 41 |
</para></entry> |
| 42 |
</row> |
| 43 |
</thead> |
| 44 |
|
| 45 |
<tbody> |
| 46 |
<row> |
| 47 |
<entry role="func_table_entry"><para role="func_signature"> |
| 48 |
<function>normal_rand</function> ( <parameter>numvals</parameter> <type>integer</type>, <parameter>mean</parameter> <type>float8</type>, <parameter>stddev</parameter> <type>float8</type> ) |
| 49 |
<returnvalue>setof float8</returnvalue> |
| 50 |
</para> |
| 51 |
<para> |
| 52 |
Produces a set of normally distributed random values. |
| 53 |
</para></entry> |
| 54 |
</row> |
| 55 |
|
| 56 |
<row> |
| 57 |
<entry role="func_table_entry"><para role="func_signature"> |
| 58 |
<function>crosstab</function> ( <parameter>sql</parameter> <type>text</type> ) |
| 59 |
<returnvalue>setof record</returnvalue> |
| 60 |
</para> |
| 61 |
<para> |
| 62 |
Produces a <quote>pivot table</quote> containing |
| 63 |
row names plus <replaceable>N</replaceable> value columns, where |
| 64 |
<replaceable>N</replaceable> is determined by the row type specified |
| 65 |
in the calling query. |
| 66 |
</para></entry> |
| 67 |
</row> |
| 68 |
|
| 69 |
<row> |
| 70 |
<entry role="func_table_entry"><para role="func_signature"> |
| 71 |
<function>crosstab<replaceable>N</replaceable></function> ( <parameter>sql</parameter> <type>text</type> ) |
| 72 |
<returnvalue>setof table_crosstab_<replaceable>N</replaceable></returnvalue> |
| 73 |
</para> |
| 74 |
<para> |
| 75 |
Produces a <quote>pivot table</quote> containing |
| 76 |
row names plus <replaceable>N</replaceable> value columns. |
| 77 |
<function>crosstab2</function>, <function>crosstab3</function>, and |
| 78 |
<function>crosstab4</function> are predefined, but you can create additional |
| 79 |
<function>crosstab<replaceable>N</replaceable></function> functions as described below. |
| 80 |
</para></entry> |
| 81 |
</row> |
| 82 |
|
| 83 |
<row> |
| 84 |
<entry role="func_table_entry"><para role="func_signature"> |
| 85 |
<function>crosstab</function> ( <parameter>source_sql</parameter> <type>text</type>, <parameter>category_sql</parameter> <type>text</type> ) |
| 86 |
<returnvalue>setof record</returnvalue> |
| 87 |
</para> |
| 88 |
<para> |
| 89 |
Produces a <quote>pivot table</quote> |
| 90 |
with the value columns specified by a second query. |
| 91 |
</para></entry> |
| 92 |
</row> |
| 93 |
|
| 94 |
<row> |
| 95 |
<entry role="func_table_entry"><para role="func_signature"> |
| 96 |
<function>crosstab</function> ( <parameter>sql</parameter> <type>text</type>, <parameter>N</parameter> <type>integer</type> ) |
| 97 |
<returnvalue>setof record</returnvalue> |
| 98 |
</para> |
| 99 |
<para> |
| 100 |
Obsolete version of <function>crosstab(text)</function>. |
| 101 |
The parameter <parameter>N</parameter> is now ignored, since the |
| 102 |
number of value columns is always determined by the calling query. |
| 103 |
</para></entry> |
| 104 |
</row> |
| 105 |
|
| 106 |
<row> |
| 107 |
<entry role="func_table_entry"><para role="func_signature"> |
| 108 |
<indexterm><primary>connectby</primary></indexterm> |
| 109 |
<function>connectby</function> ( <parameter>relname</parameter> <type>text</type>, <parameter>keyid_fld</parameter> <type>text</type>, <parameter>parent_keyid_fld</parameter> <type>text</type> |
| 110 |
<optional>, <parameter>orderby_fld</parameter> <type>text</type> </optional>, <parameter>start_with</parameter> <type>text</type>, <parameter>max_depth</parameter> <type>integer</type> |
| 111 |
<optional>, <parameter>branch_delim</parameter> <type>text</type> </optional> ) |
| 112 |
<returnvalue>setof record</returnvalue> |
| 113 |
</para> |
| 114 |
<para> |
| 115 |
Produces a representation of a hierarchical tree structure. |
| 116 |
</para></entry> |
| 117 |
</row> |
| 118 |
</tbody> |
| 119 |
</tgroup> |
| 120 |
</table> |
| 121 |
|
| 122 |
<sect3 id="tablefunc-functions-normal-rand"> |
| 123 |
<title><function>normal_rand</function></title> |
| 124 |
|
| 125 |
<indexterm> |
| 126 |
<primary>normal_rand</primary> |
| 127 |
</indexterm> |
| 128 |
|
| 129 |
<synopsis> |
| 130 |
normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8 |
| 131 |
</synopsis> |
| 132 |
|
| 133 |
<para> |
| 134 |
<function>normal_rand</function> produces a set of normally distributed random |
| 135 |
values (Gaussian distribution). |
| 136 |
</para> |
| 137 |
|
| 138 |
<para> |
| 139 |
<parameter>numvals</parameter> is the number of values to be returned |
| 140 |
from the function. <parameter>mean</parameter> is the mean of the normal |
| 141 |
distribution of values and <parameter>stddev</parameter> is the standard |
| 142 |
deviation of the normal distribution of values. |
| 143 |
</para> |
| 144 |
|
| 145 |
<para> |
| 146 |
For example, this call requests 1000 values with a mean of 5 and a |
| 147 |
standard deviation of 3: |
| 148 |
</para> |
| 149 |
|
| 150 |
<screen> |
| 151 |
test=# SELECT * FROM normal_rand(1000, 5, 3); |
| 152 |
normal_rand |
| 153 |
---------------------- |
| 154 |
1.56556322244898 |
| 155 |
9.10040991424657 |
| 156 |
5.36957140345079 |
| 157 |
-0.369151492880995 |
| 158 |
0.283600703686639 |
| 159 |
. |
| 160 |
. |
| 161 |
. |
| 162 |
4.82992125404908 |
| 163 |
9.71308014517282 |
| 164 |
2.49639286969028 |
| 165 |
(1000 rows) |
| 166 |
</screen> |
| 167 |
</sect3> |
| 168 |
|
| 169 |
<sect3 id="tablefunc-functions-crosstab-text"> |
| 170 |
<title><function>crosstab(text)</function></title> |
| 171 |
|
| 172 |
<indexterm> |
| 173 |
<primary>crosstab</primary> |
| 174 |
</indexterm> |
| 175 |
|
| 176 |
<synopsis> |
| 177 |
crosstab(text sql) |
| 178 |
crosstab(text sql, int N) |
| 179 |
</synopsis> |
| 180 |
|
| 181 |
<para> |
| 182 |
The <function>crosstab</function> function is used to produce <quote>pivot</quote> |
| 183 |
displays, wherein data is listed across the page rather than down. |
| 184 |
For example, we might have data like |
| 185 |
<programlisting> |
| 186 |
row1 val11 |
| 187 |
row1 val12 |
| 188 |
row1 val13 |
| 189 |
... |
| 190 |
row2 val21 |
| 191 |
row2 val22 |
| 192 |
row2 val23 |
| 193 |
... |
| 194 |
</programlisting> |
| 195 |
which we wish to display like |
| 196 |
<programlisting> |
| 197 |
row1 val11 val12 val13 ... |
| 198 |
row2 val21 val22 val23 ... |
| 199 |
... |
| 200 |
</programlisting> |
| 201 |
The <function>crosstab</function> function takes a text parameter that is an SQL |
| 202 |
query producing raw data formatted in the first way, and produces a table |
| 203 |
formatted in the second way. |
| 204 |
</para> |
| 205 |
|
| 206 |
<para> |
| 207 |
The <parameter>sql</parameter> parameter is an SQL statement that produces |
| 208 |
the source set of data. This statement must return one |
| 209 |
<structfield>row_name</structfield> column, one |
| 210 |
<structfield>category</structfield> column, and one |
| 211 |
<structfield>value</structfield> column. <parameter>N</parameter> is an |
| 212 |
obsolete parameter, ignored if supplied (formerly this had to match the |
| 213 |
number of output value columns, but now that is determined by the |
| 214 |
calling query). |
| 215 |
</para> |
| 216 |
|
| 217 |
<para> |
| 218 |
For example, the provided query might produce a set something like: |
| 219 |
<programlisting> |
| 220 |
row_name cat value |
| 221 |
----------+-------+------- |
| 222 |
row1 cat1 val1 |
| 223 |
row1 cat2 val2 |
| 224 |
row1 cat3 val3 |
| 225 |
row1 cat4 val4 |
| 226 |
row2 cat1 val5 |
| 227 |
row2 cat2 val6 |
| 228 |
row2 cat3 val7 |
| 229 |
row2 cat4 val8 |
| 230 |
</programlisting> |
| 231 |
</para> |
| 232 |
|
| 233 |
<para> |
| 234 |
The <function>crosstab</function> function is declared to return <type>setof |
| 235 |
record</type>, so the actual names and types of the output columns must be |
| 236 |
defined in the <literal>FROM</literal> clause of the calling <command>SELECT</command> |
| 237 |
statement, for example: |
| 238 |
<programlisting> |
| 239 |
SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text); |
| 240 |
</programlisting> |
| 241 |
This example produces a set something like: |
| 242 |
<programlisting> |
| 243 |
<== value columns ==> |
| 244 |
row_name category_1 category_2 |
| 245 |
----------+------------+------------ |
| 246 |
row1 val1 val2 |
| 247 |
row2 val5 val6 |
| 248 |
</programlisting> |
| 249 |
</para> |
| 250 |
|
| 251 |
<para> |
| 252 |
The <literal>FROM</literal> clause must define the output as one |
| 253 |
<structfield>row_name</structfield> column (of the same data type as the first result |
| 254 |
column of the SQL query) followed by N <structfield>value</structfield> columns |
| 255 |
(all of the same data type as the third result column of the SQL query). |
| 256 |
You can set up as many output value columns as you wish. The names of the |
| 257 |
output columns are up to you. |
| 258 |
</para> |
| 259 |
|
| 260 |
<para> |
| 261 |
The <function>crosstab</function> function produces one output row for each |
| 262 |
consecutive group of input rows with the same |
| 263 |
<structfield>row_name</structfield> value. It fills the output |
| 264 |
<structfield>value</structfield> columns, left to right, with the |
| 265 |
<structfield>value</structfield> fields from these rows. If there |
| 266 |
are fewer rows in a group than there are output <structfield>value</structfield> |
| 267 |
columns, the extra output columns are filled with nulls; if there are |
| 268 |
more rows, the extra input rows are skipped. |
| 269 |
</para> |
| 270 |
|
| 271 |
<para> |
| 272 |
In practice the SQL query should always specify <literal>ORDER BY 1,2</literal> |
| 273 |
to ensure that the input rows are properly ordered, that is, values with |
| 274 |
the same <structfield>row_name</structfield> are brought together and |
| 275 |
correctly ordered within the row. Notice that <function>crosstab</function> |
| 276 |
itself does not pay any attention to the second column of the query |
| 277 |
result; it's just there to be ordered by, to control the order in which |
| 278 |
the third-column values appear across the page. |
| 279 |
</para> |
| 280 |
|
| 281 |
<para> |
| 282 |
Here is a complete example: |
| 283 |
<programlisting> |
| 284 |
CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT); |
| 285 |
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1'); |
| 286 |
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2'); |
| 287 |
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3'); |
| 288 |
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4'); |
| 289 |
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5'); |
| 290 |
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6'); |
| 291 |
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7'); |
| 292 |
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8'); |
| 293 |
|
| 294 |
SELECT * |
| 295 |
FROM crosstab( |
| 296 |
'select rowid, attribute, value |
| 297 |
from ct |
| 298 |
where attribute = ''att2'' or attribute = ''att3'' |
| 299 |
order by 1,2') |
| 300 |
AS ct(row_name text, category_1 text, category_2 text, category_3 text); |
| 301 |
|
| 302 |
row_name | category_1 | category_2 | category_3 |
| 303 |
----------+------------+------------+------------ |
| 304 |
test1 | val2 | val3 | |
| 305 |
test2 | val6 | val7 | |
| 306 |
(2 rows) |
| 307 |
</programlisting> |
| 308 |
</para> |
| 309 |
|
| 310 |
<para> |
| 311 |
You can avoid always having to write out a <literal>FROM</literal> clause to |
| 312 |
define the output columns, by setting up a custom crosstab function that |
| 313 |
has the desired output row type wired into its definition. This is |
| 314 |
described in the next section. Another possibility is to embed the |
| 315 |
required <literal>FROM</literal> clause in a view definition. |
| 316 |
</para> |
| 317 |
|
| 318 |
<note> |
| 319 |
<para> |
| 320 |
See also the <command><link linkend="app-psql-meta-commands-crosstabview">\crosstabview</link></command> |
| 321 |
command in <application>psql</application>, which provides functionality similar |
| 322 |
to <function>crosstab()</function>. |
| 323 |
</para> |
| 324 |
</note> |
| 325 |
|
| 326 |
</sect3> |
| 327 |
|
| 328 |
<sect3 id="tablefunc-functions-crosstab-n-text"> |
| 329 |
<title><function>crosstab<replaceable>N</replaceable>(text)</function></title> |
| 330 |
|
| 331 |
<indexterm> |
| 332 |
<primary>crosstab</primary> |
| 333 |
</indexterm> |
| 334 |
|
| 335 |
<synopsis> |
| 336 |
crosstab<replaceable>N</replaceable>(text sql) |
| 337 |
</synopsis> |
| 338 |
|
| 339 |
<para> |
| 340 |
The <function>crosstab<replaceable>N</replaceable></function> functions are examples of how |
| 341 |
to set up custom wrappers for the general <function>crosstab</function> function, |
| 342 |
so that you need not write out column names and types in the calling |
| 343 |
<command>SELECT</command> query. The <filename>tablefunc</filename> module includes |
| 344 |
<function>crosstab2</function>, <function>crosstab3</function>, and |
| 345 |
<function>crosstab4</function>, whose output row types are defined as |
| 346 |
</para> |
| 347 |
|
| 348 |
<programlisting> |
| 349 |
CREATE TYPE tablefunc_crosstab_N AS ( |
| 350 |
row_name TEXT, |
| 351 |
category_1 TEXT, |
| 352 |
category_2 TEXT, |
| 353 |
. |
| 354 |
. |
| 355 |
. |
| 356 |
category_N TEXT |
| 357 |
); |
| 358 |
</programlisting> |
| 359 |
|
| 360 |
<para> |
| 361 |
Thus, these functions can be used directly when the input query produces |
| 362 |
<structfield>row_name</structfield> and <structfield>value</structfield> columns of type |
| 363 |
<type>text</type>, and you want 2, 3, or 4 output values columns. |
| 364 |
In all other ways they behave exactly as described above for the |
| 365 |
general <function>crosstab</function> function. |
| 366 |
</para> |
| 367 |
|
| 368 |
<para> |
| 369 |
For instance, the example given in the previous section would also |
| 370 |
work as |
| 371 |
<programlisting> |
| 372 |
SELECT * |
| 373 |
FROM crosstab3( |
| 374 |
'select rowid, attribute, value |
| 375 |
from ct |
| 376 |
where attribute = ''att2'' or attribute = ''att3'' |
| 377 |
order by 1,2'); |
| 378 |
</programlisting> |
| 379 |
</para> |
| 380 |
|
| 381 |
<para> |
| 382 |
These functions are provided mostly for illustration purposes. You |
| 383 |
can create your own return types and functions based on the |
| 384 |
underlying <function>crosstab()</function> function. There are two ways |
| 385 |
to do it: |
| 386 |
|
| 387 |
<itemizedlist> |
| 388 |
<listitem> |
| 389 |
<para> |
| 390 |
Create a composite type describing the desired output columns, |
| 391 |
similar to the examples in |
| 392 |
<filename>contrib/tablefunc/tablefunc--1.0.sql</filename>. |
| 393 |
Then define a |
| 394 |
unique function name accepting one <type>text</type> parameter and returning |
| 395 |
<type>setof your_type_name</type>, but linking to the same underlying |
| 396 |
<function>crosstab</function> C function. For example, if your source data |
| 397 |
produces row names that are <type>text</type>, and values that are |
| 398 |
<type>float8</type>, and you want 5 value columns: |
| 399 |
<programlisting> |
| 400 |
CREATE TYPE my_crosstab_float8_5_cols AS ( |
| 401 |
my_row_name text, |
| 402 |
my_category_1 float8, |
| 403 |
my_category_2 float8, |
| 404 |
my_category_3 float8, |
| 405 |
my_category_4 float8, |
| 406 |
my_category_5 float8 |
| 407 |
); |
| 408 |
|
| 409 |
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text) |
| 410 |
RETURNS setof my_crosstab_float8_5_cols |
| 411 |
AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT; |
| 412 |
</programlisting> |
| 413 |
</para> |
| 414 |
</listitem> |
| 415 |
|
| 416 |
<listitem> |
| 417 |
<para> |
| 418 |
Use <literal>OUT</literal> parameters to define the return type implicitly. |
| 419 |
The same example could also be done this way: |
| 420 |
<programlisting> |
| 421 |
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols( |
| 422 |
IN text, |
| 423 |
OUT my_row_name text, |
| 424 |
OUT my_category_1 float8, |
| 425 |
OUT my_category_2 float8, |
| 426 |
OUT my_category_3 float8, |
| 427 |
OUT my_category_4 float8, |
| 428 |
OUT my_category_5 float8) |
| 429 |
RETURNS setof record |
| 430 |
AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT; |
| 431 |
</programlisting> |
| 432 |
</para> |
| 433 |
</listitem> |
| 434 |
</itemizedlist> |
| 435 |
</para> |
| 436 |
|
| 437 |
</sect3> |
| 438 |
|
| 439 |
<sect3 id="tablefunc-functions-crosstab-text-2"> |
| 440 |
<title><function>crosstab(text, text)</function></title> |
| 441 |
|
| 442 |
<indexterm> |
| 443 |
<primary>crosstab</primary> |
| 444 |
</indexterm> |
| 445 |
|
| 446 |
<synopsis> |
| 447 |
crosstab(text source_sql, text category_sql) |
| 448 |
</synopsis> |
| 449 |
|
| 450 |
<para> |
| 451 |
The main limitation of the single-parameter form of <function>crosstab</function> |
| 452 |
is that it treats all values in a group alike, inserting each value into |
| 453 |
the first available column. If you want the value |
| 454 |
columns to correspond to specific categories of data, and some groups |
| 455 |
might not have data for some of the categories, that doesn't work well. |
| 456 |
The two-parameter form of <function>crosstab</function> handles this case by |
| 457 |
providing an explicit list of the categories corresponding to the |
| 458 |
output columns. |
| 459 |
</para> |
| 460 |
|
| 461 |
<para> |
| 462 |
<parameter>source_sql</parameter> is an SQL statement that produces the |
| 463 |
source set of data. This statement must return one |
| 464 |
<structfield>row_name</structfield> column, one |
| 465 |
<structfield>category</structfield> column, and one |
| 466 |
<structfield>value</structfield> column. It may also have one or more |
| 467 |
<quote>extra</quote> columns. |
| 468 |
The <structfield>row_name</structfield> column must be first. The |
| 469 |
<structfield>category</structfield> and <structfield>value</structfield> |
| 470 |
columns must be the last two columns, in that order. Any columns between |
| 471 |
<structfield>row_name</structfield> and |
| 472 |
<structfield>category</structfield> are treated as <quote>extra</quote>. |
| 473 |
The <quote>extra</quote> columns are expected to be the same for all rows |
| 474 |
with the same <structfield>row_name</structfield> value. |
| 475 |
</para> |
| 476 |
|
| 477 |
<para> |
| 478 |
For example, <parameter>source_sql</parameter> might produce a set |
| 479 |
something like: |
| 480 |
<programlisting> |
| 481 |
SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1; |
| 482 |
|
| 483 |
row_name extra_col cat value |
| 484 |
----------+------------+-----+--------- |
| 485 |
row1 extra1 cat1 val1 |
| 486 |
row1 extra1 cat2 val2 |
| 487 |
row1 extra1 cat4 val4 |
| 488 |
row2 extra2 cat1 val5 |
| 489 |
row2 extra2 cat2 val6 |
| 490 |
row2 extra2 cat3 val7 |
| 491 |
row2 extra2 cat4 val8 |
| 492 |
</programlisting> |
| 493 |
</para> |
| 494 |
|
| 495 |
<para> |
| 496 |
<parameter>category_sql</parameter> is an SQL statement that produces |
| 497 |
the set of categories. This statement must return only one column. |
| 498 |
It must produce at least one row, or an error will be generated. |
| 499 |
Also, it must not produce duplicate values, or an error will be |
| 500 |
generated. <parameter>category_sql</parameter> might be something like: |
| 501 |
|
| 502 |
<programlisting> |
| 503 |
SELECT DISTINCT cat FROM foo ORDER BY 1; |
| 504 |
cat |
| 505 |
------- |
| 506 |
cat1 |
| 507 |
cat2 |
| 508 |
cat3 |
| 509 |
cat4 |
| 510 |
</programlisting> |
| 511 |
</para> |
| 512 |
|
| 513 |
<para> |
| 514 |
The <function>crosstab</function> function is declared to return <type>setof |
| 515 |
record</type>, so the actual names and types of the output columns must be |
| 516 |
defined in the <literal>FROM</literal> clause of the calling <command>SELECT</command> |
| 517 |
statement, for example: |
| 518 |
|
| 519 |
<programlisting> |
| 520 |
SELECT * FROM crosstab('...', '...') |
| 521 |
AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text); |
| 522 |
</programlisting> |
| 523 |
</para> |
| 524 |
|
| 525 |
<para> |
| 526 |
This will produce a result something like: |
| 527 |
<programlisting> |
| 528 |
<== value columns ==> |
| 529 |
row_name extra cat1 cat2 cat3 cat4 |
| 530 |
---------+-------+------+------+------+------ |
| 531 |
row1 extra1 val1 val2 val4 |
| 532 |
row2 extra2 val5 val6 val7 val8 |
| 533 |
</programlisting> |
| 534 |
</para> |
| 535 |
|
| 536 |
<para> |
| 537 |
The <literal>FROM</literal> clause must define the proper number of output |
| 538 |
columns of the proper data types. If there are <replaceable>N</replaceable> |
| 539 |
columns in the <parameter>source_sql</parameter> query's result, the first |
| 540 |
<replaceable>N</replaceable>-2 of them must match up with the first |
| 541 |
<replaceable>N</replaceable>-2 output columns. The remaining output columns |
| 542 |
must have the type of the last column of the <parameter>source_sql</parameter> |
| 543 |
query's result, and there must be exactly as many of them as there |
| 544 |
are rows in the <parameter>category_sql</parameter> query's result. |
| 545 |
</para> |
| 546 |
|
| 547 |
<para> |
| 548 |
The <function>crosstab</function> function produces one output row for each |
| 549 |
consecutive group of input rows with the same |
| 550 |
<structfield>row_name</structfield> value. The output |
| 551 |
<structfield>row_name</structfield> column, plus any <quote>extra</quote> |
| 552 |
columns, are copied from the first row of the group. The output |
| 553 |
<structfield>value</structfield> columns are filled with the |
| 554 |
<structfield>value</structfield> fields from rows having matching |
| 555 |
<structfield>category</structfield> values. If a row's <structfield>category</structfield> |
| 556 |
does not match any output of the <parameter>category_sql</parameter> |
| 557 |
query, its <structfield>value</structfield> is ignored. Output |
| 558 |
columns whose matching category is not present in any input row |
| 559 |
of the group are filled with nulls. |
| 560 |
</para> |
| 561 |
|
| 562 |
<para> |
| 563 |
In practice the <parameter>source_sql</parameter> query should always |
| 564 |
specify <literal>ORDER BY 1</literal> to ensure that values with the same |
| 565 |
<structfield>row_name</structfield> are brought together. However, |
| 566 |
ordering of the categories within a group is not important. |
| 567 |
Also, it is essential to be sure that the order of the |
| 568 |
<parameter>category_sql</parameter> query's output matches the specified |
| 569 |
output column order. |
| 570 |
</para> |
| 571 |
|
| 572 |
<para> |
| 573 |
Here are two complete examples: |
| 574 |
<programlisting> |
| 575 |
create table sales(year int, month int, qty int); |
| 576 |
insert into sales values(2007, 1, 1000); |
| 577 |
insert into sales values(2007, 2, 1500); |
| 578 |
insert into sales values(2007, 7, 500); |
| 579 |
insert into sales values(2007, 11, 1500); |
| 580 |
insert into sales values(2007, 12, 2000); |
| 581 |
insert into sales values(2008, 1, 1000); |
| 582 |
|
| 583 |
select * from crosstab( |
| 584 |
'select year, month, qty from sales order by 1', |
| 585 |
'select m from generate_series(1,12) m' |
| 586 |
) as ( |
| 587 |
year int, |
| 588 |
"Jan" int, |
| 589 |
"Feb" int, |
| 590 |
"Mar" int, |
| 591 |
"Apr" int, |
| 592 |
"May" int, |
| 593 |
"Jun" int, |
| 594 |
"Jul" int, |
| 595 |
"Aug" int, |
| 596 |
"Sep" int, |
| 597 |
"Oct" int, |
| 598 |
"Nov" int, |
| 599 |
"Dec" int |
| 600 |
); |
| 601 |
year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| 602 |
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------ |
| 603 |
2007 | 1000 | 1500 | | | | | 500 | | | | 1500 | 2000 |
| 604 |
2008 | 1000 | | | | | | | | | | | |
| 605 |
(2 rows) |
| 606 |
</programlisting> |
| 607 |
|
| 608 |
<programlisting> |
| 609 |
CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text); |
| 610 |
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42'); |
| 611 |
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS'); |
| 612 |
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987'); |
| 613 |
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53'); |
| 614 |
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL'); |
| 615 |
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003'); |
| 616 |
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234'); |
| 617 |
|
| 618 |
SELECT * FROM crosstab |
| 619 |
( |
| 620 |
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', |
| 621 |
'SELECT DISTINCT attribute FROM cth ORDER BY 1' |
| 622 |
) |
| 623 |
AS |
| 624 |
( |
| 625 |
rowid text, |
| 626 |
rowdt timestamp, |
| 627 |
temperature int4, |
| 628 |
test_result text, |
| 629 |
test_startdate timestamp, |
| 630 |
volts float8 |
| 631 |
); |
| 632 |
rowid | rowdt | temperature | test_result | test_startdate | volts |
| 633 |
-------+--------------------------+-------------+-------------+--------------------------+-------- |
| 634 |
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987 |
| 635 |
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234 |
| 636 |
(2 rows) |
| 637 |
</programlisting> |
| 638 |
</para> |
| 639 |
|
| 640 |
<para> |
| 641 |
You can create predefined functions to avoid having to write out |
| 642 |
the result column names and types in each query. See the examples |
| 643 |
in the previous section. The underlying C function for this form |
| 644 |
of <function>crosstab</function> is named <literal>crosstab_hash</literal>. |
| 645 |
</para> |
| 646 |
|
| 647 |
</sect3> |
| 648 |
|
| 649 |
<sect3 id="tablefunc-functions-connectby"> |
| 650 |
<title><function>connectby</function></title> |
| 651 |
|
| 652 |
<indexterm> |
| 653 |
<primary>connectby</primary> |
| 654 |
</indexterm> |
| 655 |
|
| 656 |
<synopsis> |
| 657 |
connectby(text relname, text keyid_fld, text parent_keyid_fld |
| 658 |
[, text orderby_fld ], text start_with, int max_depth |
| 659 |
[, text branch_delim ]) |
| 660 |
</synopsis> |
| 661 |
|
| 662 |
<para> |
| 663 |
The <function>connectby</function> function produces a display of hierarchical |
| 664 |
data that is stored in a table. The table must have a key field that |
| 665 |
uniquely identifies rows, and a parent-key field that references the |
| 666 |
parent (if any) of each row. <function>connectby</function> can display the |
| 667 |
sub-tree descending from any row. |
| 668 |
</para> |
| 669 |
|
| 670 |
<para> |
| 671 |
<xref linkend="tablefunc-connectby-parameters"/> explains the |
| 672 |
parameters. |
| 673 |
</para> |
| 674 |
|
| 675 |
<table id="tablefunc-connectby-parameters"> |
| 676 |
<title><function>connectby</function> Parameters</title> |
| 677 |
<tgroup cols="2"> |
| 678 |
<thead> |
| 679 |
<row> |
| 680 |
<entry>Parameter</entry> |
| 681 |
<entry>Description</entry> |
| 682 |
</row> |
| 683 |
</thead> |
| 684 |
<tbody> |
| 685 |
<row> |
| 686 |
<entry><parameter>relname</parameter></entry> |
| 687 |
<entry>Name of the source relation</entry> |
| 688 |
</row> |
| 689 |
<row> |
| 690 |
<entry><parameter>keyid_fld</parameter></entry> |
| 691 |
<entry>Name of the key field</entry> |
| 692 |
</row> |
| 693 |
<row> |
| 694 |
<entry><parameter>parent_keyid_fld</parameter></entry> |
| 695 |
<entry>Name of the parent-key field</entry> |
| 696 |
</row> |
| 697 |
<row> |
| 698 |
<entry><parameter>orderby_fld</parameter></entry> |
| 699 |
<entry>Name of the field to order siblings by (optional)</entry> |
| 700 |
</row> |
| 701 |
<row> |
| 702 |
<entry><parameter>start_with</parameter></entry> |
| 703 |
<entry>Key value of the row to start at</entry> |
| 704 |
</row> |
| 705 |
<row> |
| 706 |
<entry><parameter>max_depth</parameter></entry> |
| 707 |
<entry>Maximum depth to descend to, or zero for unlimited depth</entry> |
| 708 |
</row> |
| 709 |
<row> |
| 710 |
<entry><parameter>branch_delim</parameter></entry> |
| 711 |
<entry>String to separate keys with in branch output (optional)</entry> |
| 712 |
</row> |
| 713 |
</tbody> |
| 714 |
</tgroup> |
| 715 |
</table> |
| 716 |
|
| 717 |
<para> |
| 718 |
The key and parent-key fields can be any data type, but they must be |
| 719 |
the same type. Note that the <parameter>start_with</parameter> value must be |
| 720 |
entered as a text string, regardless of the type of the key field. |
| 721 |
</para> |
| 722 |
|
| 723 |
<para> |
| 724 |
The <function>connectby</function> function is declared to return <type>setof |
| 725 |
record</type>, so the actual names and types of the output columns must be |
| 726 |
defined in the <literal>FROM</literal> clause of the calling <command>SELECT</command> |
| 727 |
statement, for example: |
| 728 |
</para> |
| 729 |
|
| 730 |
<programlisting> |
| 731 |
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') |
| 732 |
AS t(keyid text, parent_keyid text, level int, branch text, pos int); |
| 733 |
</programlisting> |
| 734 |
|
| 735 |
<para> |
| 736 |
The first two output columns are used for the current row's key and |
| 737 |
its parent row's key; they must match the type of the table's key field. |
| 738 |
The third output column is the depth in the tree and must be of type |
| 739 |
<type>integer</type>. If a <parameter>branch_delim</parameter> parameter was |
| 740 |
given, the next output column is the branch display and must be of type |
| 741 |
<type>text</type>. Finally, if an <parameter>orderby_fld</parameter> |
| 742 |
parameter was given, the last output column is a serial number, and must |
| 743 |
be of type <type>integer</type>. |
| 744 |
</para> |
| 745 |
|
| 746 |
<para> |
| 747 |
The <quote>branch</quote> output column shows the path of keys taken to |
| 748 |
reach the current row. The keys are separated by the specified |
| 749 |
<parameter>branch_delim</parameter> string. If no branch display is |
| 750 |
wanted, omit both the <parameter>branch_delim</parameter> parameter |
| 751 |
and the branch column in the output column list. |
| 752 |
</para> |
| 753 |
|
| 754 |
<para> |
| 755 |
If the ordering of siblings of the same parent is important, |
| 756 |
include the <parameter>orderby_fld</parameter> parameter to |
| 757 |
specify which field to order siblings by. This field can be of any |
| 758 |
sortable data type. The output column list must include a final |
| 759 |
integer serial-number column, if and only if |
| 760 |
<parameter>orderby_fld</parameter> is specified. |
| 761 |
</para> |
| 762 |
|
| 763 |
<para> |
| 764 |
The parameters representing table and field names are copied as-is |
| 765 |
into the SQL queries that <function>connectby</function> generates internally. |
| 766 |
Therefore, include double quotes if the names are mixed-case or contain |
| 767 |
special characters. You may also need to schema-qualify the table name. |
| 768 |
</para> |
| 769 |
|
| 770 |
<para> |
| 771 |
In large tables, performance will be poor unless there is an index on |
| 772 |
the parent-key field. |
| 773 |
</para> |
| 774 |
|
| 775 |
<para> |
| 776 |
It is important that the <parameter>branch_delim</parameter> string |
| 777 |
not appear in any key values, else <function>connectby</function> may incorrectly |
| 778 |
report an infinite-recursion error. Note that if |
| 779 |
<parameter>branch_delim</parameter> is not provided, a default value |
| 780 |
of <literal>~</literal> is used for recursion detection purposes. |
| 781 |
<!-- That pretty well sucks. FIXME --> |
| 782 |
</para> |
| 783 |
|
| 784 |
<para> |
| 785 |
Here is an example: |
| 786 |
<programlisting> |
| 787 |
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int); |
| 788 |
|
| 789 |
INSERT INTO connectby_tree VALUES('row1',NULL, 0); |
| 790 |
INSERT INTO connectby_tree VALUES('row2','row1', 0); |
| 791 |
INSERT INTO connectby_tree VALUES('row3','row1', 0); |
| 792 |
INSERT INTO connectby_tree VALUES('row4','row2', 1); |
| 793 |
INSERT INTO connectby_tree VALUES('row5','row2', 0); |
| 794 |
INSERT INTO connectby_tree VALUES('row6','row4', 0); |
| 795 |
INSERT INTO connectby_tree VALUES('row7','row3', 0); |
| 796 |
INSERT INTO connectby_tree VALUES('row8','row6', 0); |
| 797 |
INSERT INTO connectby_tree VALUES('row9','row5', 0); |
| 798 |
|
| 799 |
-- with branch, without orderby_fld (order of results is not guaranteed) |
| 800 |
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~') |
| 801 |
AS t(keyid text, parent_keyid text, level int, branch text); |
| 802 |
keyid | parent_keyid | level | branch |
| 803 |
-------+--------------+-------+--------------------- |
| 804 |
row2 | | 0 | row2 |
| 805 |
row4 | row2 | 1 | row2~row4 |
| 806 |
row6 | row4 | 2 | row2~row4~row6 |
| 807 |
row8 | row6 | 3 | row2~row4~row6~row8 |
| 808 |
row5 | row2 | 1 | row2~row5 |
| 809 |
row9 | row5 | 2 | row2~row5~row9 |
| 810 |
(6 rows) |
| 811 |
|
| 812 |
-- without branch, without orderby_fld (order of results is not guaranteed) |
| 813 |
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0) |
| 814 |
AS t(keyid text, parent_keyid text, level int); |
| 815 |
keyid | parent_keyid | level |
| 816 |
-------+--------------+------- |
| 817 |
row2 | | 0 |
| 818 |
row4 | row2 | 1 |
| 819 |
row6 | row4 | 2 |
| 820 |
row8 | row6 | 3 |
| 821 |
row5 | row2 | 1 |
| 822 |
row9 | row5 | 2 |
| 823 |
(6 rows) |
| 824 |
|
| 825 |
-- with branch, with orderby_fld (notice that row5 comes before row4) |
| 826 |
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') |
| 827 |
AS t(keyid text, parent_keyid text, level int, branch text, pos int); |
| 828 |
keyid | parent_keyid | level | branch | pos |
| 829 |
-------+--------------+-------+---------------------+----- |
| 830 |
row2 | | 0 | row2 | 1 |
| 831 |
row5 | row2 | 1 | row2~row5 | 2 |
| 832 |
row9 | row5 | 2 | row2~row5~row9 | 3 |
| 833 |
row4 | row2 | 1 | row2~row4 | 4 |
| 834 |
row6 | row4 | 2 | row2~row4~row6 | 5 |
| 835 |
row8 | row6 | 3 | row2~row4~row6~row8 | 6 |
| 836 |
(6 rows) |
| 837 |
|
| 838 |
-- without branch, with orderby_fld (notice that row5 comes before row4) |
| 839 |
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0) |
| 840 |
AS t(keyid text, parent_keyid text, level int, pos int); |
| 841 |
keyid | parent_keyid | level | pos |
| 842 |
-------+--------------+-------+----- |
| 843 |
row2 | | 0 | 1 |
| 844 |
row5 | row2 | 1 | 2 |
| 845 |
row9 | row5 | 2 | 3 |
| 846 |
row4 | row2 | 1 | 4 |
| 847 |
row6 | row4 | 2 | 5 |
| 848 |
row8 | row6 | 3 | 6 |
| 849 |
(6 rows) |
| 850 |
</programlisting> |
| 851 |
</para> |
| 852 |
</sect3> |
| 853 |
|
| 854 |
</sect2> |
| 855 |
|
| 856 |
<sect2 id="tablefunc-author"> |
| 857 |
<title>Author</title> |
| 858 |
|
| 859 |
<para> |
| 860 |
Joe Conway |
| 861 |
</para> |
| 862 |
|
| 863 |
</sect2> |
| 864 |
|
| 865 |
</sect1> |