Postgres Pro
Downloads
Home   >   mailing lists

Hierarchical Query Question (PHP) - Mailing list pgsql-general

From David Blomstrom
Subject Hierarchical Query Question (PHP)
Date
Msg-id CAA54Z0j1XP0t-RRZiBp=_p+tZEosrFwfix+84kKj_0_SxKi+Sg@mail.gmail.com
Whole thread Raw
Responses Re: Hierarchical Query Question (PHP)
Re: Hierarchical Query Question (PHP)
List pgsql-general
Tree view
Can anyone tell me how to write the query described @ http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query ?

The answer's very thorough, but I don't know how to string two queries and a function together like that. This doesn't work:

$sql = "select * from gz_life_mammals;";

create function tax_rank(id integer) returns text as $$
    select case id
             when 1 then 'Classes'
             when 2 then 'Orders'
             when 3 then 'Families'
             when 4 then 'Genera'
             when 5 then 'Species'
           end;
$$ language sql;

$sql = "with recursive hier(taxon,parent_id) as (
  select m.taxon, null::integer
  from gz_life_mammals m
  where taxon='Mammalia' --<< substitute me
  union all
  select m.taxon, m.parent_id
  from hier, gz_life_mammals m
  where m.parent=hier.taxon
)
select tax_rank(parent_id),
       count(*) num_of_desc
from   hier
where  parent_id is not null
group by parent_id
order by parent_id;";

Thanks.

pgsql-general by date:

Previous
From: Rob Sargent
Date:
Subject: Re: Domain check constraint not honored?
Next
From: Florin Andrei
Date:
Subject: BDR: name conflict when joining a rebuilt node