From: Joel Fradkin <jfradkin@wazagua.com>
To: pgsql-sql@postgresql.org
Subject: recursive query
Date: Wed, 5 Sep 2007 10:47:29 -0400
Message-ID: <003201c7efcb$af931500$837ba8c0@jfradkin64> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jfradkin@wazagua.com
Subject: Re: recursive query
In-Reply-To: <003201c7efcb$af931500$837ba8c0@jfradkin64>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox