Home • Explore • Blog • CI
☰



postgresql
10th chunk of `doc/src/sgml/tablefunc.sgml`
42450098f054d9709622c6b5a6c5a58b1b099a793913b16a000000010000096c
   parameter was given, the last output column is a serial number, and must
     be of type <type>integer</type>.
    </para>

    <para>
     The <quote>branch</quote> output column shows the path of keys taken to
     reach the current row.  The keys are separated by the specified
     <parameter>branch_delim</parameter> string.  If no branch display is
     wanted, omit both the <parameter>branch_delim</parameter> parameter
     and the branch column in the output column list.
    </para>

    <para>
     If the ordering of siblings of the same parent is important,
     include the <parameter>orderby_fld</parameter> parameter to
     specify which field to order siblings by.  This field can be of any
     sortable data type.  The output column list must include a final
     integer serial-number column, if and only if
     <parameter>orderby_fld</parameter> is specified.
    </para>

    <para>
     The parameters representing table and field names are copied as-is
     into the SQL queries that <function>connectby</function> generates internally.
     Therefore, include double quotes if the names are mixed-case or contain
     special characters.  You may also need to schema-qualify the table name.
    </para>

    <para>
     In large tables, performance will be poor unless there is an index on
     the parent-key field.
    </para>

    <para>
     It is important that the <parameter>branch_delim</parameter> string
     not appear in any key values, else <function>connectby</function> may incorrectly
     report an infinite-recursion error.  Note that if
     <parameter>branch_delim</parameter> is not provided, a default value
     of <literal>~</literal> is used for recursion detection purposes.
     <!-- That pretty well sucks.  FIXME -->
    </para>

    <para>
     Here is an example:
<programlisting>
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);

INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);

-- with branch, without
Title: connectby Function Details: Branch Display, Ordering Siblings, and Table/Field Naming
Summary
This section further elaborates on the connectby function, explaining the branch output column, which displays the path of keys separated by branch_delim. It emphasizes the importance of using orderby_fld for sibling ordering and adding a serial number column. Table and field names should be double-quoted if they contain mixed-case letters or special characters, schema qualification might be needed. Indexing the parent-key field is crucial for performance on large tables. The branch_delim string shouldn't exist in any key values to prevent errors. Finally, an example table creation and data insertion are shown.