Hierarchical Query Question (PHP)

Поиск
Список
Период
Сортировка
От David Blomstrom
Тема Hierarchical Query Question (PHP)
Дата
Msg-id CAA54Z0j1XP0t-RRZiBp=_p+tZEosrFwfix+84kKj_0_SxKi+Sg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Hierarchical Query Question (PHP)
Re: Hierarchical Query Question (PHP)
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Rob Sargent
Дата:
Сообщение: Re: Domain check constraint not honored?
Следующее
От: Florin Andrei
Дата:
Сообщение: BDR: name conflict when joining a rebuilt node