Hi,

I am trying to do a hierarchy design. We currently have it hard coded to 4
levels and the location table has an id field for each level where the data
has a location id.

This allows easy access by level (to restrict who see what) and easy to
arrogate for reporting by division, region, district, and location.

I am trying to make it more flexible some companies don't use 4 levels some
want more, some have different sets of users and would like their own
hierarchy.

My idea is to have a virtual hierarchy (all share the location record which
is the lowest level and a separate file that will be linked with a table
that has the locationid and the hierarchy id to enable multiple hierarchy).

I did a bit of research and found some code to do recursive sql (but it was
IBM and I am not sure I even got it right for postgres).



create or replace temp view test (clientnum,id ,parentid,descr, level) as (

select h.clientnum,h.id ,h.parentid,h.descr,0 as level

from tblhyerarchy h where parentid =0

union all

select h1.clientnum,h1.id ,h1.parentid,h1.descr,h.level +1 as level

from tblhyerarchy h1

inner join test h on h1.parentid =h.id

where h.level < 5

);

select * from test



but get a

ERROR: infinite recursion detected in rules for relation "test" SQL state:
42P17



I am basing on a table created with

CREATE TABLE tblhyerarchy

(

clientnum character varying(16) NOT NULL,

id integer NOT NULL,

parentid integer NOT NULL,

descr character varying(250),

CONSTRAINT pk_tblhyerarchy PRIMARY KEY (clientnum, id)

)

WITH OIDS;





Any help is much appreciated. I am also a little confused how to do a
crosstab on the output.

In the end I will want to get at a data record that has the location id and
then link to the hierarchy with each level of the hierarchy present for
aggregation etc.



Joel Fradkin



Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305



<mailto:jfradkin@wazagua.com> jfradkin@wazagua.com
<http://www.wazagua.com/> www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.

On Sep 5, 4:47 pm, jfrad...@wazagua.com ("Joel Fradkin") wrote:
> Hi,
>
> I am trying to do a hierarchy design. We currently have it hard coded to 4
> levels and the location table has an id field for each level where the data
> has a location id.
>
> This allows easy access by level (to restrict who see what) and easy to
> arrogate for reporting by division, region, district, and location.
>
> I am trying to make it more flexible some companies don't use 4 levels some
> want more, some have different sets of users and would like their own
> hierarchy.
>
> My idea is to have a virtual hierarchy (all share the location record which
> is the lowest level and a separate file that will be linked with a table
> that has the locationid and the hierarchy id to enable multiple hierarchy).
>
> I did a bit of research and found some code to do recursive sql (but it was
> IBM and I am not sure I even got it right for postgres).
>
> create or replace temp view test (clientnum,id ,parentid,descr, level) as (
>
> select h.clientnum,h.id ,h.parentid,h.descr,0 as level
>
> from tblhyerarchy h where parentid =0
>
> union all
>
> select h1.clientnum,h1.id ,h1.parentid,h1.descr,h.level +1 as level
>
> from tblhyerarchy h1
>
> inner join test h on h1.parentid =h.id
>
> where h.level < 5
>
> );
>
> select * from test
>
> but get a
>
> ERROR: infinite recursion detected in rules for relation "test" SQL state:
> 42P17
>
> I am basing on a table created with
>
> CREATE TABLE tblhyerarchy
>
> (
>
> clientnum character varying(16) NOT NULL,
>
> id integer NOT NULL,
>
> parentid integer NOT NULL,
>
> descr character varying(250),
>
> CONSTRAINT pk_tblhyerarchy PRIMARY KEY (clientnum, id)
>
> )
>
> WITH OIDS;
>
> Any help is much appreciated. I am also a little confused how to do a
> crosstab on the output.
>
> In the end I will want to get at a data record that has the location id and
> then link to the hierarchy with each level of the hierarchy present for
> aggregation etc.
>
> Joel Fradkin
>
> Wazagua, Inc.
> 2520 Trailmate Dr
> Sarasota, Florida 34243
> Tel. 941-753-7111 ext 305
>
> <mailto:jfrad...@wazagua.com> jfrad...@wazagua.com
> <http://www.wazagua.com/>www.wazagua.com
> Powered by Wazagua
> Providing you with the latest Web-based technology & advanced tools.
> C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
> This email message is for the use of the intended recipient(s) and may
> contain confidential and privileged information. Any unauthorized review,
> use, disclosure or distribution is prohibited. If you are not the intended
> recipient, please contact the sender by reply email and delete and destroy
> all copies of the original message, including attachments.

Have a look at the "ltree"[1] module for postgres. It works quite
well.
The one thing I found badly explained was how to get started. So
here's some details.

"ltree" is a "contrib" module. Most distributions have separate
packages. One for postgresql, and one for postgresql-contrib. You can
see if you have the contrib modules installed if you find a "contrib"
subfolder in the postgres files. This is usually (in *nix) in /usr/
share/posgres/contrib. Or somesuch.

In this folder you should have a file called "ltree.sql". You need to
import this into your database, that needs ltree support:

$ cat /usr/share/postgres/contrib/ltree.sql | psql -Uuser database

or, from whithin the DB-shell:

database=> \i /usr/share/postgres/contrib/ltree.sql

Once imported you have a new data-type at your disposal called
"ltree". Example DDL:

CREATE TABLE ltest(
id SERIAL NOT NULL PRIMARY KEY,
path ltree,
label VARCHAR(25)
);

INSERT INTO ltest (path, label) VALUES ('TOP', 'Root level');
INSERT INTO ltest (path, label) VALUES ('TOP.Division1', 'Division
1');
INSERT INTO ltest (path, label) VALUES ('TOP.Division2', 'Division
2');
INSERT INTO ltest (path, label) VALUES ('TOP.Division1.UnitA', 'This
is Unit A');
INSERT INTO ltest (path, label) VALUES ('TOP.Division1.UnitB', 'This
is Unit B');

This should give you a head-start so you can play with the examples
given on the ltree page[1].
Note that you can create an index on the ltree field (maybe a GIST
index?). This mostly depends on your needs.

If you are on windows: Do NOT select the ltree module in the postgers
installer. This will load the ltree module in *every* database you
will create. Even if you don't need it! The contrib modules will be
available to you even if you do not select it in the installer. You
just have to import them into you databases as needed as explained
above. Though, on Windows they will be located in a different path.

[1]: http://www.sai.msu.su/~megera/postgres/gist/ltree/