self referencing table.

From: David Salisbury <salisbury(at)globe(dot)gov>
To: pgsql-general(at)postgresql(dot)org
Subject: self referencing table.
Date: 2012-01-18 00:31:10
Message-ID: 4F1612CE.3050702@globe.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I've got a table:

Taxa
Column | Type
----------------+-----------------------------
id | integer |
parent_id | integer |
taxonomic_rank | character varying(32) |
latin_name | character varying(32)

It's basically a self referential table, with
values in the taxonomic_rank like

phylum
family
order
genus
species

So at any row in the table I can get all the parent
information be traversing upward using the parent id.

However I'm interested in only getting just genus and species
when I'm given a taxa.id value. It would be a nice simple
self join if the taxa.id I was given was always to a
row with rank of 'species'. Problem is, grasses don't
have species, so sometimes my id is pointing to a genus
row instead ( the id will be to lowest rank ), so the
parent is of no use.

I'm thinking that I'm going to have to resort to a plpgsql
function to solve this, but I'm hoping for a straight sql
query that can return ...

genus, species ( if it's a grass id the species value will be null )

... for any given taxa.id without iterating in a function.

Actually, I was hoping to join this taxa table with it's
referencing table and pull a view with the genus/species
added in.

possible? Thanks for any ideas,

-ds

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2012-01-18 01:01:07 Re: self referencing table.
Previous Message Tim Uckun 2012-01-17 22:34:04 Re: HA options