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