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