Search Postgresql Archives

Re: Hierarchical Query Question (PHP)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



David,

Does wrapping the transaction with BEGIN; COMMIT; work as you would expect?


$sql = "BEGIN; 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; COMMIT;";

On Thu, Oct 29, 2015 at 8:18 PM, David Blomstrom <david.blomstrom@xxxxxxxxx> wrote:
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.




--
Jason O'Donnell
Crunchy Data Solutions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux