Re(2): [SQL] help on creating table

Поиск
Список
Период
Сортировка
Искать

Re(2): [SQL] help on creating table

От:
"pgsql-sql" <pgsql-sql@fc.emc.com.ph>
Дата:
thank you very much.
i'll try this.

regards,
sherwin

josh@agliodbs.com writes:
>Sherwin,
>
>	I've done this before for one project.  Here's what you do:
>
>CREATE TABLE sample_heirarchy (
>	unique_id 	SERIAL CONSTRAINT PRIMARY KEY,
>	node_linkup 	INT4,
>	node_level	INT2,
>	label		VARCHAR(30)
>	data		whatever
>	);
>
>Then you use the unique_id and node_linkup fields to create a heirarchy
>of data nodes, with an indefinite number of levels, where the
>node_linkup of each lower level equals the id of its parent record.  For
>example:
>
>id	linkup	level		label		data
>3	0	1		Node1		Node1
>4	3	2		Node1.1		Node1.1
>6	3	2		Node1.2		Node1.2
>7	6	3		Node1.2.1	Node1.2.1
>5	0	1		Node2		Node2
>
>etc.
>
>You can then access the whole heirarchy through moderately complex, but
>very fast-executing UNION queries.  The one drawback is that you need to
>know in advance the maximum number of levels (3 in this example), but
>I'm sure someone on this list can find a way around that:
>
>SELECT n1.unique_id, n1.label, n1.data, n1.node_level, n1.unique_id AS
>level1,
>	0 AS level2, 0 AS level3
>FROM sample_heirarchy n1
>WHERE n1.node_level = 1
>UNION ALL
>SELECT n2.unique_id, n2.label, n2.data, n2.node_level, n1.unique_id, 
>	n2.unique_id, 0
>FROM sample_heirarchy n2, sample_heirarchy n1
>WHERE n1.unique_id = n2.node_linkup
>	AND n2.node_level = 2
>UNION ALL
>SELECT n3.unique_id, n3.label, n3.data, n3.node_level, n1.unique_id, 
>	n2.unique_id, n3.unique_id
>FROM sample_heirarchy n1, sample_heirarchy n2, sample_heirarchy n3
>WHERE n1.unique_id = n2.node_linkup AND
>	n2.unique_id = n3.node_linkup
>	AND n3.node_level = 3
>ORDER BY level1, level2, level3
>
>Should produce this output (pardon any parsing errors; I'm not at a
>PGSQL terminal right now):
>
>unique_id	label		data	level	level1	level2	level3
>3		Node1		Node1	  1	3	0	0
>4		Node1.1		Node1.1	  2	3	4	0
>6		Node1.2		Node1.2	  2	3	6	0
>7		Node1.2.1	Node1.2.1 3	3	6	7
>5		Node2		Node2	  1	7	0	0
>etc.
>
>This sorts them in numerical (id) order, but one could just as easily
>substitute the labels or data for the various levels and sort them
>alphabetically (although you do need to allow for NULL sort order on
>your database, and any label duplicates).
>
>The advantages of this structure are:
>1. It allows you to create, assign, and re-assign nodes freely all over
>the heirarchy ... just change the level and/or linkup.
>2. Aside from the Union query above, the table structure allows for any
>number of levels, unlike a set or relationally linked tables.
>3. Because the display query is entirely once table linking to itself on
>(hopefully) indexed fields, in my expreience it runs very, very fast.
>4. My PHP developer has reprogrammed the easily available PHP Tree
>Control to uses this table structure (I don't know if he's giving it
>out, but he said it wasn't very difficult).
>
>CHALLENGE FOR THE LIST:
>Re-write the above UNION query, possibly using a PL/PGSQL or C function,
>so that it works for any number of node levels.
>
>-Josh Berkus
>
>-- 
>______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 436-9166
>   for law firms, small businesses       fax  436-0137
>    and non-profit organizations.       pager 338-4078
>                        		San Francisco


FAQ