-- Name: plantname; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE plantname (

    plantname_id int(11) NOT NULL,

    parent_id int(11),

    scope_id int(11),

    rank text NOT NULL,

    verbatimrank text,

    plantname text NOT NULL,

    description text,

    accessioncode text

);

--

-- Name: TABLE plantname; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN plantname.rank; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: plantname_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE plantname_ancestor (

    plantname_id int(11) NOT NULL,

    ancestor_id int(11) NOT NULL

);

--

    SELECT datasource.organizationname AS `dataSourceName`, family.plantname AS family, genus.plantname AS genus, species.plantname AS species, binomial.plantname AS taxon, authority.plantname AS `taxonAuthor`, variety.plantname AS `taxonMorphospecies`, country.placename AS country, stateprovince.placename AS `stateProvince`, county.placename AS `countyParish`, taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS `collectionNumber`, array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)) AS `identifiedBy`, aggregateoccurrence.collectiondate AS `observationDate`, location.authorlocationcode AS `plotCode`, location.area AS `plotAreaHa`, method.name AS `plotMethod`, locationdetermination.latitude, locationdetermination.longitude, location.elevation AS elevation_m, taxonoccurrence.iscultivated AS `isCultivated`, taxonoccurrence.cultivatedbasis AS `isCultivatedReason`, aggregateoccurrence.cover AS `pctCover` FROM (((((((((((((((((((((((((((((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) JOIN locationdetermination USING (location_id)) LEFT JOIN namedplace_ancestor continent_ancestor ON ((continent_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace continent ON (((continent.namedplace_id = continent_ancestor.ancestor_id) AND (continent.rank = CAST('continent' AS placerank))))) LEFT JOIN namedplace_ancestor country_ancestor ON ((country_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace country ON (((country.namedplace_id = country_ancestor.ancestor_id) AND (country.rank = CAST('country' AS placerank))))) LEFT JOIN namedplace_ancestor stateprovince_ancestor ON ((stateprovince_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace stateprovince ON (((stateprovince.namedplace_id = stateprovince_ancestor.ancestor_id) AND (stateprovince.rank = CAST('stateProvince' AS placerank))))) LEFT JOIN namedplace_ancestor county_ancestor ON ((county_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace county ON (((county.namedplace_id = county_ancestor.ancestor_id) AND (county.rank = CAST('county' AS placerank))))) JOIN locationevent USING (location_id)) JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN plantconcept USING (plantconcept_id)) LEFT JOIN plantname_ancestor binomial_ancestor ON ((binomial_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname binomial ON (((binomial.plantname_id = binomial_ancestor.ancestor_id) AND (binomial.rank = CAST('binomial' AS taxonrank))))) LEFT JOIN plantname_ancestor kingdom_ancestor ON ((kingdom_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname kingdom ON (((kingdom.plantname_id = kingdom_ancestor.ancestor_id) AND (kingdom.rank = CAST('kingdom' AS taxonrank))))) LEFT JOIN plantname_ancestor division_ancestor ON ((division_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname division ON (((division.plantname_id = division_ancestor.ancestor_id) AND (division.rank = CAST('division' AS taxonrank))))) LEFT JOIN plantname_ancestor class_ancestor ON ((class_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname class ON (((class.plantname_id = class_ancestor.ancestor_id) AND (class.rank = CAST('class' AS taxonrank))))) LEFT JOIN plantname_ancestor order_ancestor ON ((order_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname `order` ON (((`order`.plantname_id = order_ancestor.ancestor_id) AND (`order`.rank = CAST('order' AS taxonrank))))) LEFT JOIN plantname_ancestor family_ancestor ON ((family_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname family ON (((family.plantname_id = family_ancestor.ancestor_id) AND (family.rank = CAST('family' AS taxonrank))))) LEFT JOIN plantname_ancestor genus_ancestor ON ((genus_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname genus ON (((genus.plantname_id = genus_ancestor.ancestor_id) AND (genus.rank = CAST('genus' AS taxonrank))))) LEFT JOIN plantname_ancestor species_ancestor ON ((species_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname species ON (((species.plantname_id = species_ancestor.ancestor_id) AND (species.rank = CAST('species' AS taxonrank))))) LEFT JOIN plantname_ancestor subspecies_ancestor ON ((subspecies_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname subspecies ON (((subspecies.plantname_id = subspecies_ancestor.ancestor_id) AND (subspecies.rank = CAST('subspecies' AS taxonrank))))) LEFT JOIN plantname_ancestor authority_ancestor ON ((authority_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname authority ON (((authority.plantname_id = authority_ancestor.ancestor_id) AND (authority.rank = CAST('authority' AS taxonrank))))) LEFT JOIN plantname_ancestor variety_ancestor ON ((variety_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname variety ON (((variety.plantname_id = variety_ancestor.ancestor_id) AND (variety.rank = CAST('variety' AS taxonrank))))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

-- Name: plantname; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE plantname (

    plantname_id integer NOT NULL,

    parent_id integer,

    scope_id integer,

    rank taxonrank NOT NULL,

    verbatimrank text,

    plantname text NOT NULL,

    description text,

    accessioncode text

);

--

-- Name: TABLE plantname; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON TABLE plantname IS 'To include a taxon name at a rank with no explicit column, create a plantname for it and point to it using plantname_id. To include multiple such names, chain the plantnames together using parent_id, as a form of ordered linked list. Note that lower-level taxa should point to higher-level taxa.';

--

-- Name: COLUMN plantname.rank; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN plantname.rank IS 'Even if you specify a custom verbatimrank, you must also specify a closest-match rank from the taxonrank closed list.';

--

-- Name: plantname_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE plantname_ancestor (

    plantname_id integer NOT NULL,

    ancestor_id integer NOT NULL

);

--

    SELECT datasource.organizationname AS "dataSourceName", family.plantname AS family, genus.plantname AS genus, species.plantname AS species, binomial.plantname AS taxon, authority.plantname AS "taxonAuthor", variety.plantname AS "taxonMorphospecies", country.placename AS country, stateprovince.placename AS "stateProvince", county.placename AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", location.area AS "plotAreaHa", method.name AS "plotMethod", locationdetermination.latitude, locationdetermination.longitude, location.elevation AS elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", aggregateoccurrence.cover AS "pctCover" FROM (((((((((((((((((((((((((((((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) JOIN locationdetermination USING (location_id)) LEFT JOIN namedplace_ancestor continent_ancestor ON ((continent_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace continent ON (((continent.namedplace_id = continent_ancestor.ancestor_id) AND (continent.rank = 'continent'::placerank)))) LEFT JOIN namedplace_ancestor country_ancestor ON ((country_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace country ON (((country.namedplace_id = country_ancestor.ancestor_id) AND (country.rank = 'country'::placerank)))) LEFT JOIN namedplace_ancestor stateprovince_ancestor ON ((stateprovince_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace stateprovince ON (((stateprovince.namedplace_id = stateprovince_ancestor.ancestor_id) AND (stateprovince.rank = 'stateProvince'::placerank)))) LEFT JOIN namedplace_ancestor county_ancestor ON ((county_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace county ON (((county.namedplace_id = county_ancestor.ancestor_id) AND (county.rank = 'county'::placerank)))) JOIN locationevent USING (location_id)) JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN plantconcept USING (plantconcept_id)) LEFT JOIN plantname_ancestor binomial_ancestor ON ((binomial_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname binomial ON (((binomial.plantname_id = binomial_ancestor.ancestor_id) AND (binomial.rank = 'binomial'::taxonrank)))) LEFT JOIN plantname_ancestor kingdom_ancestor ON ((kingdom_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname kingdom ON (((kingdom.plantname_id = kingdom_ancestor.ancestor_id) AND (kingdom.rank = 'kingdom'::taxonrank)))) LEFT JOIN plantname_ancestor division_ancestor ON ((division_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname division ON (((division.plantname_id = division_ancestor.ancestor_id) AND (division.rank = 'division'::taxonrank)))) LEFT JOIN plantname_ancestor class_ancestor ON ((class_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname class ON (((class.plantname_id = class_ancestor.ancestor_id) AND (class.rank = 'class'::taxonrank)))) LEFT JOIN plantname_ancestor order_ancestor ON ((order_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname "order" ON ((("order".plantname_id = order_ancestor.ancestor_id) AND ("order".rank = 'order'::taxonrank)))) LEFT JOIN plantname_ancestor family_ancestor ON ((family_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname family ON (((family.plantname_id = family_ancestor.ancestor_id) AND (family.rank = 'family'::taxonrank)))) LEFT JOIN plantname_ancestor genus_ancestor ON ((genus_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname genus ON (((genus.plantname_id = genus_ancestor.ancestor_id) AND (genus.rank = 'genus'::taxonrank)))) LEFT JOIN plantname_ancestor species_ancestor ON ((species_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname species ON (((species.plantname_id = species_ancestor.ancestor_id) AND (species.rank = 'species'::taxonrank)))) LEFT JOIN plantname_ancestor subspecies_ancestor ON ((subspecies_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname subspecies ON (((subspecies.plantname_id = subspecies_ancestor.ancestor_id) AND (subspecies.rank = 'subspecies'::taxonrank)))) LEFT JOIN plantname_ancestor authority_ancestor ON ((authority_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname authority ON (((authority.plantname_id = authority_ancestor.ancestor_id) AND (authority.rank = 'authority'::taxonrank)))) LEFT JOIN plantname_ancestor variety_ancestor ON ((variety_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname variety ON (((variety.plantname_id = variety_ancestor.ancestor_id) AND (variety.rank = 'variety'::taxonrank)))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

plantname:

For each taxonrank value, sanitize and replace regexp ".*" with:

-----

LEFT JOIN plantname_ancestor AS $0_ancestor

ON $0_ancestor.plantname_id = plantconcept.plantname_id

LEFT JOIN plantname AS $0

ON $0.plantname_id = $0_ancestor.ancestor_id

AND $0.rank = ''$0''

-----

If sanitized, replace ''$0'' with original string.

If get syntax errors, double-quote applicable names.