Re: Hierarchical Query Question (PHP)

On 10/30/2015 4:36 PM, Andy Colson wrote:
> 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
>
>
>
>

Humm, after looking at this further, my answer isn't right. I did not
notice rank (classes, orders, families...) is different than taxon
(mammilia, carnivora, ...)

But still, lookup table is better than function.

-Andy