* Sample to demonstrate C functions which return setof scalar

* and setof composite.

* Joe Conway <mail@joeconway.com>

* And contributors:

* Nabil Sayegh <postgresql@e-trolley.de>

*

* Copyright 2002 by PostgreSQL Global Development Group

*

- requires anonymous composite type syntax in the FROM clause. See

the instructions in the documentation below.

connectby(text relname, text keyid_fld, text parent_keyid_fld,

text start_with, int max_depth [, text branch_delim])

connectby(text relname, text keyid_fld, text parent_keyid_fld

[, text orderby_fld], text start_with, int max_depth

[, text branch_delim])

- returns keyid, parent_keyid, level, and an optional branch string

and an optional serial column for ordering siblings

- requires anonymous composite type syntax in the FROM clause. See

the instructions in the documentation below.

==================================================================

Name

connectby(text, text, text, text, int[, text]) - returns a set

connectby(text, text, text[, text], text, text, int[, text]) - returns a set

representing a hierarchy (tree structure)

Synopsis

connectby(text relname, text keyid_fld, text parent_keyid_fld,

text start_with, int max_depth [, text branch_delim])

connectby(text relname, text keyid_fld, text parent_keyid_fld

[, text orderby_fld], text start_with, int max_depth

[, text branch_delim])

Inputs

Name of the key_parent field

orderby_fld

If optional ordering of siblings is desired:

Name of the field to order siblings

start_with

root value of the tree input as a text value regardless of keyid_fld type

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)

AS t(keyid text, parent_keyid text, level int);

- or -

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')

AS t(keyid text, parent_keyid text, level int, branch text, pos int);

- or -

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)

AS t(keyid text, parent_keyid text, level int, pos int);

Notes

5. The parameters representing table and field names must include double

quotes if the names are mixed-case or contain special characters.

6. If sorting of siblings is desired, the orderby_fld input parameter *and*

a name for the resulting serial field (type INT32) in the query column

definition must be given.

Example usage

CREATE TABLE connectby_tree(keyid text, parent_keyid text);

CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);

INSERT INTO connectby_tree VALUES('row1',NULL);

INSERT INTO connectby_tree VALUES('row2','row1');

INSERT INTO connectby_tree VALUES('row3','row1');

INSERT INTO connectby_tree VALUES('row4','row2');

INSERT INTO connectby_tree VALUES('row5','row2');

INSERT INTO connectby_tree VALUES('row6','row4');

INSERT INTO connectby_tree VALUES('row7','row3');

INSERT INTO connectby_tree VALUES('row8','row6');

INSERT INTO connectby_tree VALUES('row9','row5');

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

-- with branch, without orderby_fld

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')

AS t(keyid text, parent_keyid text, level int, branch text);

keyid | parent_keyid | level | branch

row9 | row5 | 2 | row2~row5~row9

(6 rows)

-- without branch

-- without branch, without orderby_fld

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)

AS t(keyid text, parent_keyid text, level int);

keyid | parent_keyid | level

row9 | row5 | 2

(6 rows)

-- with branch, with orderby_fld (notice that row5 comes before row4)

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')

AS t(keyid text, parent_keyid text, level int, branch text, pos int) ORDER BY t.pos;

keyid | parent_keyid | level | branch | pos

-------+--------------+-------+---------------------+-----

row2 | | 0 | row2 | 1

row5 | row2 | 1 | row2~row5 | 2

row9 | row5 | 2 | row2~row5~row9 | 3

row4 | row2 | 1 | row2~row4 | 4

row6 | row4 | 2 | row2~row4~row6 | 5

row8 | row6 | 3 | row2~row4~row6~row8 | 6

(6 rows)

-- without branch, with orderby_fld (notice that row5 comes before row4)

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)

AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY t.pos;

keyid | parent_keyid | level | pos

-------+--------------+-------+-----

row2 | | 0 | 1

row5 | row2 | 1 | 2

row9 | row5 | 2 | 3

row4 | row2 | 1 | 4

row6 | row4 | 2 | 5

row8 | row6 | 3 | 6

(6 rows)

==================================================================

-- Joe Conway

row1\N

row2row1

row3row1

row4row2

row5row2

row6row4

row7row3

row8row6

row9row5

row1\N0

row2row10

row3row10

row4row21

row5row20

row6row40

row7row30

row8row60

row9row50

-- connectby

--

-- test connectby with text based hierarchy

CREATE TABLE connectby_text(keyid text, parent_keyid text);

CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int);

\copy connectby_text from 'data/connectby_text.data'

-- with branch

-- with branch, without orderby

SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);

keyid | parent_keyid | level | branch

-------+--------------+-------+---------------------

row9 | row5 | 2 | row2~row5~row9

(6 rows)

-- without branch

-- without branch, without orderby

SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);

keyid | parent_keyid | level

-------+--------------+-------

row9 | row5 | 2

(6 rows)

-- with branch, with orderby

SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int) ORDER BY t.pos;

keyid | parent_keyid | level | branch | pos

-------+--------------+-------+---------------------+-----

row2 | | 0 | row2 | 1

row5 | row2 | 1 | row2~row5 | 2

row9 | row5 | 2 | row2~row5~row9 | 3

row4 | row2 | 1 | row2~row4 | 4

row6 | row4 | 2 | row2~row4~row6 | 5

row8 | row6 | 3 | row2~row4~row6~row8 | 6

(6 rows)

-- without branch, with orderby

SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY t.pos;

keyid | parent_keyid | level | pos

-------+--------------+-------+-----

row2 | | 0 | 1

row5 | row2 | 1 | 2

row9 | row5 | 2 | 3

row4 | row2 | 1 | 4

row6 | row4 | 2 | 5

row8 | row6 | 3 | 6

(6 rows)

-- test connectby with int based hierarchy

CREATE TABLE connectby_int(keyid int, parent_keyid int);

\copy connectby_int from 'data/connectby_int.data'

CREATETABLEconnectby_text(keyidtext, parent_keyidtext);

CREATETABLEconnectby_text(keyidtext, parent_keyidtext, posint);

\copy connectby_textfrom'data/connectby_text.data'

SELECT*FROM connectby('connectby_text','keyid','parent_keyid','row2',0,'~')AS t(keyidtext, parent_keyidtext, levelint, branchtext);

SELECT*FROM connectby('connectby_text','keyid','parent_keyid','row2',0)AS t(keyidtext, parent_keyidtext, levelint);

SELECT*FROM connectby('connectby_text','keyid','parent_keyid','pos','row2',0,'~')AS t(keyidtext, parent_keyidtext, levelint, branchtext, posint)ORDER BYt.pos;

SELECT*FROM connectby('connectby_text','keyid','parent_keyid','pos','row2',0)AS t(keyidtext, parent_keyidtext, levelint, posint)ORDER BYt.pos;

CREATETABLEconnectby_int(keyidint, parent_keyidint);

\copy connectby_intfrom'data/connectby_int.data'