From: | David Blomstrom <david(dot)blomstrom(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Hierarchical Query Question (PHP) |
Date: | 2015-10-30 00:18:13 |
Message-ID: | CAA54Z0j1XP0t-RRZiBp=_p+tZEosrFwfix+84kKj_0_SxKi+Sg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Florin Andrei | 2015-10-30 00:24:40 | BDR: name conflict when joining a rebuilt node |
Previous Message | Rob Sargent | 2015-10-29 23:16:14 | Re: Domain check constraint not honored? |