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