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