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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
