recursive query - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I am trying to do a hierarchy design. We currently have it hard coded to 4 levels and the location
tablehas an id field for each level where the data has a location id.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">This allows easy access by level (to restrict who see what) and easy to arrogate for reporting by
division,region, district, and location.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">I am trying to make it more flexible some companies don’t use 4 levels some want more, some have
differentsets of users and would like their own hierarchy.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">My idea is to have a virtual hierarchy (all share the location record which is the lowest level and
aseparate file that will be linked with a table that has the locationid and the hierarchy id to enable multiple
hierarchy).</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> I did a bit of research and found some code to do recursive sql (but it was IBM and I am not sure I
evengot it right for postgres).</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"">create or replace temp view test (clientnum,id
,parentid,descr,level) as (</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"">select   h.clientnum,h.id ,h.parentid,h.descr,0 as
level</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> from tblhyerarchy h where parentid =0 </span></font><p
class="MsoNormal"style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">union all</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">select  h1.clientnum,h1.id ,h1.parentid,h1.descr,h.level +1 as level</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New""> fromtblhyerarchy h1</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"">inner join test h on h1.parentid =h.id
</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">where h.level < 5</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">);</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">select * from test</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New""> </span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">but get a </span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">ERROR:infinite recursion detected in rules for relation "test" SQL state: 42P17</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New""> </span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">I am basing on a table created with</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">CREATETABLE tblhyerarchy</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"">(</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New""> 
clientnumcharacter varying(16) NOT NULL,</span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">  id integer NOT
NULL,</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">  parentid integer NOT NULL,</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New""> 
descrcharacter varying(250),</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"">  CONSTRAINT pk_tblhyerarchy PRIMARY KEY (clientnum,
id)</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">) </span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">WITHOIDS;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Any help is much appreciated. I am also a little confused how to do a crosstab on the
output.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">In the end I will want to get at a data record that has the location id and then link to the
hierarchywith each level of the hierarchy present for aggregation etc.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial;color:navy">JoelFradkin</span></font><p class="MsoNormal"
style="margin-left:1.0in"><fontcolor="navy" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:navy"> </span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">Wazagua, Inc.<br /></span></font><font color="navy" face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial;color:navy">2520 Trailmate Dr</span></font><font color="navy" face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial; 
color:navy"><br /></span></font><font color="navy" face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial;color:navy">Sarasota</span></font><fontcolor="navy" face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial;color:navy">,</span></font><font color="navy" face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial;color:navy">Florida</span></font><fontcolor="navy" face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial;color:navy"> </span></font><font color="navy" face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial;color:navy">34243</span></font><font color="navy" face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial; 
color:navy"><br /> Tel.  941-753-7111 ext 305</span></font><p class="MsoNormal" style="margin-left:1.0in"><font
color="navy"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:navy"> </span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span style="font-size: 
12.0pt"><a href="mailto:jfradkin@wazagua.com"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">jfradkin@wazagua.com</span></font></a></span></font><fontcolor="navy"
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial; 
color:navy"><br /></span></font><a href="http://www.wazagua.com/"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">www.wazagua.com</span></font></a><fontcolor="navy" face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial; 
color:navy"><br /> Powered by Wazagua<br /> Providing you with the latest Web-based technology & advanced tools.<br
/>© 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc<br />  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
distributionis prohibited.  If you are not the intended recipient, please contact the sender by reply email and delete
anddestroy all copies of the original message, including attachments.</span></font><p class="MsoNormal"><font
face="TimesNew Roman" size="3"><span style="font-size: 
12.0pt"> </span></font></div>