Re: Hierarchical Query Question (PHP)

On 10/30/2015 3:47 PM, David Blomstrom wrote:
> No, I get the same T_FUNCTION error.
>
> Someone commented that the function...
>
>    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;
>
> ...should ideally be part of the table schema. Does that mean I need to
> go into pgAdmin, open up my table and paste this in somehow?
>
> I wonder if the function is even necessary. My goal is to create a
> hierarchical query that displays the number of children, grandchildren,
> etc. And, depending on the taxonomic level, it might display the result
> as "20 families, 74 genera and 413 species." With MySQL I could probably
> turn that into a series of echo values, which I could then display like
> this:
>
> echo ''.$NumberChildren.' families<br>
> '.$NumberGrandchildren.' genera<br>
> '.$NumberGreatgrandchildren.' species';
>
> I'm wondering if I should figure out this query or go back to square one
> with a simpler query. I've never seen a query with a function before. ;)
>
> Thanks for the tips.

Shoot, I should have read this before responding to the first one.

Yes, create function tax_rank, should be done in pgAdmin.

 > I wonder if the function is even necessary.

Correct, its not.  It should probably be a lookup table:

create table taxon (
   taxonid serial,
   descr text
);
create table gz_life_mammals (
   id serial,
   taxonid integer, -- use the lookup table
   parentid integer -- use the lookup table
);


-Andy