SET lock_timeout = 0;

--

-- Name: SCHEMA "TNRS"; Type: COMMENT; Schema: -; Owner: bien

--

COMMENT ON SCHEMA "TNRS" IS 'IMPORTANT: when changing this schema, you must also make the same changes on vegbiendev:

on vegbiendev:

# back up existing TNRS schema (in case of an accidental incorrect change):

$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")

$ svn up

$ svn di

# make the changes shown in the diff

## to change column types:

SELECT util.set_col_types(''"TNRS".tnrs'', ARRAY[

  (''col'', ''new_type'')

]::util.col_cast[]); -- runtime: 9 min ("531282 ms")

$ rm=1 inputs/.TNRS/schema.sql.run

# repeat until `svn di` shows no diff

# back up new TNRS schema:

$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")';

-- Name: MatchedTaxon_modify(text); Type: FUNCTION; Schema: TNRS; Owner: bien

--

CREATE FUNCTION "MatchedTaxon_modify"(view_query text) RETURNS void

    LANGUAGE sql

    AS $_$

SELECT util.recreate_view('"TNRS"."MatchedTaxon"', $1, $$

-- **IMPORTANT**: keep these updated as described in the views' comments

CREATE VIEW "TNRS"."ValidMatchedTaxon" AS 

 SELECT *

   FROM "TNRS"."MatchedTaxon"

  WHERE "MatchedTaxon".taxon_scrub__is_valid_match;

$$||util.mk_set_relation_metadata('"TNRS"."ValidMatchedTaxon"')||$$

CREATE VIEW "TNRS".taxon_scrub AS 

 SELECT *

   FROM "TNRS"."ValidMatchedTaxon"

   LEFT JOIN "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name);

$$||util.mk_set_relation_metadata('"TNRS".taxon_scrub')||$$

$$);

$_$;

ALTER FUNCTION "TNRS"."MatchedTaxon_modify"(view_query text) OWNER TO bien;

--

-- Name: FUNCTION "MatchedTaxon_modify"(view_query text); Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON FUNCTION "MatchedTaxon_modify"(view_query text) IS '

usage:

SELECT "TNRS"."MatchedTaxon_modify"($$

 SELECT *, __ AS accepted_morphospecies_binomial

   FROM __

$$);

idempotent

';

--

-- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: bien

CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) RETURNS text

    LANGUAGE sql IMMUTABLE

/* from http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields:

"taxonomic_status should be accepted instead of synonym when an accepted name is

available (this is not always the case when a name is marked as a synonym)" */

SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END

ALTER FUNCTION "TNRS".map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) OWNER TO bien;

-- Name: taxon_scrub_modify(text); Type: FUNCTION; Schema: TNRS; Owner: bien

--

CREATE FUNCTION taxon_scrub_modify(view_query text) RETURNS void

    LANGUAGE sql

    AS $_$

SELECT util.recreate_view('"TNRS".taxon_scrub', $1);

$_$;

ALTER FUNCTION "TNRS".taxon_scrub_modify(view_query text) OWNER TO bien;

--

-- Name: FUNCTION taxon_scrub_modify(view_query text); Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON FUNCTION taxon_scrub_modify(view_query text) IS '

usage:

SELECT "TNRS".taxon_scrub_modify($$

 SELECT *, __ AS scrubbed_morphospecies_binomial

   FROM "TNRS"."ValidMatchedTaxon"

   LEFT JOIN "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name);

$$);

idempotent

';

--

-- Name: tnrs__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: bien

--

CREATE FUNCTION tnrs__batch_begin() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

	PERFORM util.seq__reset('pg_temp.tnrs__match_num__seq', 0);

	RETURN NULL;

END;

$$;

ALTER FUNCTION "TNRS".tnrs__batch_begin() OWNER TO bien;

--

-- Name: tnrs__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: bien

--

CREATE FUNCTION tnrs__match_num__next() RETURNS bigint

    LANGUAGE sql

    AS $$

SELECT nextval('pg_temp.tnrs__match_num__seq');

$$;

ALTER FUNCTION "TNRS".tnrs__match_num__next() OWNER TO bien;

--

	"Specific_epithet_is_plant" boolean :=

		(CASE

		WHEN   new."Infraspecific_epithet_matched"	 IS NOT NULL

			OR new."Infraspecific_epithet_2_matched" IS NOT NULL

			OR new."Specific_epithet_score" >= 0.9 -- fuzzy match

			THEN true

		ELSE NULL -- ambiguous

		END);

	never_homonym boolean = COALESCE(new."Author_score" >= 0.6, false);

		-- author disambiguates

	family_is_homonym boolean = NOT never_homonym

		AND "TNRS".family_is_homonym(new."Family_matched");

	genus_is_homonym  boolean = NOT never_homonym

		AND "TNRS".genus_is_homonym(new."Genus_matched");

	matched_taxon_name_with_author text = NULLIF(concat_ws(' '

		, NULLIF(NULLIF(new."Name_matched_accepted_family", 'Unknown'),

			new."Name_matched")

		, NULLIF(new."Name_matched", 'No suitable matches found.')

		, new."Name_matched_author"

		), '');

	accepted_taxon_name_with_author text = NULLIF(concat_ws(' '

		, NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'),

			new."Accepted_name")

		, new."Accepted_name"

		, new."Accepted_name_author"

		), '');

	/* exclude homonyms because these are not valid matches (i.e. TNRS provides

	a name, but the name is not meaningful because it is not unambiguous). */

	new.is_valid_match = new."Taxonomic_status" != 'Invalid'

		AND COALESCE(CASE

		WHEN new."Family_score" = 1 AND NOT family_is_homonym -- exact match

			THEN true

		ELSE -- consider genus

			(CASE

			WHEN new."Genus_score" =  1	   -- exact match

				THEN

				(CASE

				WHEN NOT genus_is_homonym THEN true

				ELSE "Specific_epithet_is_plant"

				END)

			WHEN new."Genus_score" >= 0.85 -- fuzzy match

				THEN "Specific_epithet_is_plant"

			ELSE NULL -- ambiguous

			END)

		END, false);

	new.scrubbed_unique_taxon_name = COALESCE(

		accepted_taxon_name_with_author, matched_taxon_name_with_author);

	RETURN new;

runtime: 30 min ("5564082 rows affected, 1918900 ms execution time")

runtime: 1.5 min ("92633 ms")';

    match_num integer,

    is_valid_match boolean NOT NULL,

    scrubbed_unique_taxon_name text

COMMENT ON TABLE tnrs IS 'IMPORTANT: when changing this table''s schema, you may need to re-run TNRS:

$ inputs/.TNRS/data.sql.run refresh';

 SELECT s."*Name_matched.batch", 

    s."concatenatedScientificName", 

    s."matchedTaxonName", 

    s."matchedTaxonRank", 

    s."*Name_matched.Name_score", 

    s."matchedScientificNameAuthorship", 

    s."matchedScientificNameID", 

    s."*Name_matched.Author_score", 

    s."matchedFamilyConfidence_fraction", 

    s."matchedFamily", 

    s."matchedGenus", 

    s."matchedGenusConfidence_fraction", 

    s."matchedSpecificEpithet", 

    s."matchedSpeciesConfidence_fraction", 

    s."matchedInfraspecificEpithet", 

    s."*Name_matched.Infraspecific_epithet_score", 

    s."identificationQualifier", 

    s."morphospeciesSuffix", 

    s."taxonomicStatus", 

    s.accepted_taxon_name_no_author, 

    s.accepted_author, 

    s.accepted_taxon_rank, 

    s."acceptedScientificNameID", 

    s.accepted_species_binomial, 

    s.accepted_family, 

    s."*Name_matched.Selected", 

    s."*Name_matched.Source", 

    s."*Name_matched.Warnings", 

    s."*Name_matched.Accepted_name_lsid", 

    s.taxon_scrub__is_valid_match, 

    s.scrubbed_unique_taxon_name, 

        CASE

            WHEN (s.accepted_taxon_rank = 'family'::text) THEN concat_ws(' '::text, s.accepted_family, s."morphospeciesSuffix")

            WHEN (s.accepted_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, s.accepted_taxon_name_no_author, s."morphospeciesSuffix")

            ELSE s.accepted_species_binomial

        END AS accepted_morphospecies_binomial

   FROM ( SELECT tnrs.batch AS "*Name_matched.batch", 

            tnrs."Name_submitted" AS "concatenatedScientificName", 

            tnrs."Name_matched" AS "matchedTaxonName", 

            tnrs."Name_matched_rank" AS "matchedTaxonRank", 

            tnrs."Name_score" AS "*Name_matched.Name_score", 

            tnrs."Name_matched_author" AS "matchedScientificNameAuthorship", 

            tnrs."Name_matched_url" AS "matchedScientificNameID", 

            tnrs."Author_score" AS "*Name_matched.Author_score", 

            tnrs."Family_score" AS "matchedFamilyConfidence_fraction", 

            COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily", 

            tnrs."Genus_matched" AS "matchedGenus", 

            tnrs."Genus_score" AS "matchedGenusConfidence_fraction", 

            tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet", 

            tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", 

            tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", 

            tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", 

            tnrs."Annotations" AS "identificationQualifier", 

            tnrs."Unmatched_terms" AS "morphospeciesSuffix", 

            map_taxonomic_status(tnrs."Taxonomic_status", tnrs."Accepted_name") AS "taxonomicStatus", 

            tnrs."Accepted_name" AS accepted_taxon_name_no_author, 

            tnrs."Accepted_name_author" AS accepted_author, 

            tnrs."Accepted_name_rank" AS accepted_taxon_rank, 

            tnrs."Accepted_name_url" AS "acceptedScientificNameID", 

            tnrs."Accepted_name_species" AS accepted_species_binomial, 

            tnrs."Accepted_name_family" AS accepted_family, 

            tnrs."Selected" AS "*Name_matched.Selected", 

            tnrs."Source" AS "*Name_matched.Source", 

            tnrs."Warnings" AS "*Name_matched.Warnings", 

            tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", 

            tnrs.is_valid_match AS taxon_scrub__is_valid_match, 

            tnrs.scrubbed_unique_taxon_name

           FROM tnrs) s;

-- Name: VIEW "MatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: bien

COMMENT ON VIEW "MatchedTaxon" IS '

to modify:

SELECT "TNRS"."MatchedTaxon_modify"($$

 SELECT *, __ AS accepted_morphospecies_binomial

   FROM __

$$);

';

-- Name: Source; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace: 

CREATE TABLE "Source" (

    "*row_num" integer NOT NULL,

    "sourceType" text DEFAULT 'aggregator'::text NOT NULL,

    "datasetURL" text DEFAULT 'http://tnrs.iplantcollaborative.org/TNRSapp.html'::text NOT NULL,

    CONSTRAINT nulls_mapped CHECK (true)

);

ALTER TABLE "TNRS"."Source" OWNER TO bien;

-- Name: COLUMN "Source"."sourceType"; Type: COMMENT; Schema: TNRS; Owner: bien

COMMENT ON COLUMN "Source"."sourceType" IS '

constant

';

-- Name: COLUMN "Source"."datasetURL"; Type: COMMENT; Schema: TNRS; Owner: bien

COMMENT ON COLUMN "Source"."datasetURL" IS '

constant

';

-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: bien

CREATE VIEW "ValidMatchedTaxon" AS

 SELECT "MatchedTaxon"."*Name_matched.batch", 

    "MatchedTaxon"."concatenatedScientificName", 

    "MatchedTaxon"."matchedTaxonName", 

    "MatchedTaxon"."matchedTaxonRank", 

    "MatchedTaxon"."*Name_matched.Name_score", 

    "MatchedTaxon"."matchedScientificNameAuthorship", 

    "MatchedTaxon"."matchedScientificNameID", 

    "MatchedTaxon"."*Name_matched.Author_score", 

    "MatchedTaxon"."matchedFamilyConfidence_fraction", 

    "MatchedTaxon"."matchedFamily", 

    "MatchedTaxon"."matchedGenus", 

    "MatchedTaxon"."matchedGenusConfidence_fraction", 

    "MatchedTaxon"."matchedSpecificEpithet", 

    "MatchedTaxon"."matchedSpeciesConfidence_fraction", 

    "MatchedTaxon"."matchedInfraspecificEpithet", 

    "MatchedTaxon"."*Name_matched.Infraspecific_epithet_score", 

    "MatchedTaxon"."identificationQualifier", 

    "MatchedTaxon"."morphospeciesSuffix", 

    "MatchedTaxon"."taxonomicStatus", 

    "MatchedTaxon".accepted_taxon_name_no_author, 

    "MatchedTaxon".accepted_author, 

    "MatchedTaxon".accepted_taxon_rank, 

    "MatchedTaxon"."acceptedScientificNameID", 

    "MatchedTaxon".accepted_species_binomial, 

    "MatchedTaxon".accepted_family, 

    "MatchedTaxon"."*Name_matched.Selected", 

    "MatchedTaxon"."*Name_matched.Source", 

    "MatchedTaxon"."*Name_matched.Warnings", 

    "MatchedTaxon"."*Name_matched.Accepted_name_lsid", 

    "MatchedTaxon".taxon_scrub__is_valid_match, 

    "MatchedTaxon".scrubbed_unique_taxon_name, 

    "MatchedTaxon".accepted_morphospecies_binomial

   FROM "MatchedTaxon"

  WHERE "MatchedTaxon".taxon_scrub__is_valid_match;

ALTER TABLE "TNRS"."ValidMatchedTaxon" OWNER TO bien;

-- Name: VIEW "ValidMatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: bien

COMMENT ON VIEW "ValidMatchedTaxon" IS '

to update, use * as the column list

';

-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: bien

--

CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS

 SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name, 

    tnrs."Name_matched_rank" AS scrubbed_taxon_rank, 

    COALESCE(tnrs."Name_matched_accepted_family", tnrs."Family_matched") AS scrubbed_family, 

    tnrs."Genus_matched" AS scrubbed_genus, 

    tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet, 

    tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank, 

    tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet, 

    tnrs."Name_matched_author" AS scrubbed_author, 

    tnrs."Name_matched" AS scrubbed_taxon_name_no_author, 

    (tnrs."Name_matched" || COALESCE((' '::text || tnrs."Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author

   FROM tnrs;

ALTER TABLE "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*" OWNER TO bien;

--

-- Name: VIEW "taxon_scrub.scrubbed_unique_taxon_name.*"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" IS 'scrubbed_family: Name_matched_accepted_family was missing from the TNRS results at one point, so Family_matched is used as a workaround to populate this. the workaround is for *accepted names only*, as no opinion names do not have an Accepted_name_family to prepend to the scrubbed name to parse.';

--

-- Name: taxon_scrub; Type: VIEW; Schema: TNRS; Owner: bien

--

CREATE VIEW taxon_scrub AS

 SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name, 

    "ValidMatchedTaxon"."*Name_matched.batch", 

    "ValidMatchedTaxon"."concatenatedScientificName", 

    "ValidMatchedTaxon"."matchedTaxonName", 

    "ValidMatchedTaxon"."matchedTaxonRank", 

    "ValidMatchedTaxon"."*Name_matched.Name_score", 

    "ValidMatchedTaxon"."matchedScientificNameAuthorship", 

    "ValidMatchedTaxon"."matchedScientificNameID", 

    "ValidMatchedTaxon"."*Name_matched.Author_score", 

    "ValidMatchedTaxon"."matchedFamilyConfidence_fraction", 

    "ValidMatchedTaxon"."matchedFamily", 

    "ValidMatchedTaxon"."matchedGenus", 

    "ValidMatchedTaxon"."matchedGenusConfidence_fraction", 

    "ValidMatchedTaxon"."matchedSpecificEpithet", 

    "ValidMatchedTaxon"."matchedSpeciesConfidence_fraction", 

    "ValidMatchedTaxon"."matchedInfraspecificEpithet", 

    "ValidMatchedTaxon"."*Name_matched.Infraspecific_epithet_score", 

    "ValidMatchedTaxon"."identificationQualifier", 

    "ValidMatchedTaxon"."morphospeciesSuffix", 

    "ValidMatchedTaxon"."taxonomicStatus", 

    "ValidMatchedTaxon".accepted_taxon_name_no_author, 

    "ValidMatchedTaxon".accepted_author, 

    "ValidMatchedTaxon".accepted_taxon_rank, 

    "ValidMatchedTaxon"."acceptedScientificNameID", 

    "ValidMatchedTaxon".accepted_species_binomial, 

    "ValidMatchedTaxon".accepted_family, 

    "ValidMatchedTaxon"."*Name_matched.Selected", 

    "ValidMatchedTaxon"."*Name_matched.Source", 

    "ValidMatchedTaxon"."*Name_matched.Warnings", 

    "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid", 

    "ValidMatchedTaxon".taxon_scrub__is_valid_match, 

    "ValidMatchedTaxon".accepted_morphospecies_binomial, 

    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank, 

    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, 

    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus, 

    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet, 

    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank, 

    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet, 

    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author, 

    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, 

    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author, 

        CASE

            WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'family'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "ValidMatchedTaxon"."morphospeciesSuffix")

            WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, "ValidMatchedTaxon"."morphospeciesSuffix")

            ELSE (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus || ' '::text) || "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet)

        END AS scrubbed_morphospecies_binomial

   FROM ("ValidMatchedTaxon"

   LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));

ALTER TABLE "TNRS".taxon_scrub OWNER TO bien;

--

-- Name: VIEW taxon_scrub; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON VIEW taxon_scrub IS '

to modify:

SELECT "TNRS".taxon_scrub_modify($$

 SELECT *, __ AS scrubbed_morphospecies_binomial

   FROM "TNRS"."ValidMatchedTaxon"

   LEFT JOIN "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name);

$$);

';

--

1	aggregator	http://tnrs.iplantcollaborative.org/TNRSapp.html

2014-05-31 20:04:19.706592-07	2014-05-31 20:04:19.706592-07	2014-05-31 20:04:19.706592-07	\N

2014-05-31 20:04:25.799698-07	2014-05-31 20:04:25.799698-07	2014-05-31 20:04:25.799698-07	\N

COPY tnrs (batch, match_num, "Name_number", "Name_submitted", "Overall_score", "Name_matched", "Name_matched_rank", "Name_score", "Name_matched_author", "Name_matched_url", "Author_matched", "Author_score", "Family_matched", "Family_score", "Name_matched_accepted_family", "Genus_matched", "Genus_score", "Specific_epithet_matched", "Specific_epithet_score", "Infraspecific_rank", "Infraspecific_epithet_matched", "Infraspecific_epithet_score", "Infraspecific_rank_2", "Infraspecific_epithet_2_matched", "Infraspecific_epithet_2_score", "Annotations", "Unmatched_terms", "Taxonomic_status", "Accepted_name", "Accepted_name_author", "Accepted_name_rank", "Accepted_name_url", "Accepted_name_species", "Accepted_name_family", "Selected", "Source", "Warnings", "Accepted_name_lsid", is_valid_match, scrubbed_unique_taxon_name) FROM stdin;

2014-05-31 20:04:19.706592-07	\N	0	Fam_indet. Boyle#6501	0	No suitable matches found.	\N	0	\N	\N	\N	0	\N	0	\N	\N	0	\N	0	\N	\N	0	\N	\N	0	\N	\N	\N	\N	\N	\N	\N	\N	\N	true	\N	 	\N	f	\N

2014-05-31 20:04:19.706592-07	\N	1	Poa annua var. eriolepis	1	Poa annua var. eriolepis	var.	1	̉ۡ.Desv.	http://www.theplantlist.org/tpl1.1/record/kew-435206	\N	\N	\N	\N	\N	Poa	1	annua	1	var.	eriolepis	1	\N	\N	\N	\N	\N	Synonym	Poa annua	L.	species	http://www.theplantlist.org/tpl1.1/record/kew-435194	Poa annua	Poaceae	true	tpl	 	\N	t	Poaceae Poa annua L.

2014-05-31 20:04:19.706592-07	\N	2	Poa annua	1	Poa annua	species	1	L.	http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	Poa annua	Poaceae	true	tpl;tropicos;usda	 	\N	t	Poaceae Poa annua L.

2014-05-31 20:04:19.706592-07	\N	3	Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subsp.	0.770000000000000018	(S. Watson) C.L. Hitchc. & Maguire	http://www.theplantlist.org/tpl1.1/record/tro-6303627	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri subsp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subsp.	http://www.theplantlist.org/tpl1.1/record/tro-6303627	Silene scouleri	Caryophyllaceae	true	tpl	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire

2014-05-31 20:04:19.706592-07	\N	4	Fabaceae Boyle#6500	0.900000000000000022	Fabaceae	family	1	Lindl.	http://www.tropicos.org/Name/42000184	\N	\N	Fabaceae	1	Fabaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	Accepted	Fabaceae	Lindl.	family	http://www.tropicos.org/Name/42000184	\N	Fabaceae	true	tropicos	 	\N	t	Fabaceae Lindl.

2014-05-31 20:04:19.706592-07	\N	5	Poa annua subsp. exilis	1	Poa annua subsp. exilis	subsp.	1	(Tomm. ex Freyn.) Asch. & Graebn.	http://www.theplantlist.org/tpl1.1/record/kew-435202	\N	\N	\N	\N	\N	Poa	1	annua	1	subsp.	exilis	1	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.theplantlist.org/tpl1.1/record/kew-436189	Poa infirma	Poaceae	true	tpl	 	\N	t	Poaceae Poa infirma Kunth

2014-05-31 20:04:19.706592-07	\N	6	Poa annua ssp. exilis	1	Poa annua subsp. exilis	subspecies	1	(Tomm. ex Freyn) Asch. & Graebn.	http://www.tropicos.org/Name/50063800	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	subsp.	exilis	1	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.tropicos.org/Name/25514158	Poa infirma	Poaceae	true	tropicos	 	\N	t	Poaceae Poa infirma Kunth

2014-05-31 20:04:19.706592-07	\N	7	Poa annua subvar. minima	1	Poa annua subvar. minima	subvariety	1	(Schur) Asch. & Graebn.	http://www.tropicos.org/Name/50158097	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	subvar.	minima	1	\N	\N	\N	\N	\N	Accepted	Poa annua subvar. minima	(Schur) Asch. & Graebn.	subvariety	http://www.tropicos.org/Name/50158097	Poa annua	Poaceae	true	tropicos	 	\N	t	Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn.

2014-05-31 20:04:19.706592-07	\N	8	Poa annua L.	1	Poa annua	species	1	L.	http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	L.	1	\N	\N	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	Poa annua	Poaceae	true	tpl;tropicos;usda	 	\N	t	Poaceae Poa annua L.

2014-05-31 20:04:19.706592-07	\N	9	Compositae indet. sp.1	0.900000000000000022	Compositae	family	1	\N	http://www.theplantlist.org/1.1/browse/A/Compositae/	\N	\N	Compositae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	indet. sp.1	Accepted	Compositae	\N	family	http://www.theplantlist.org/1.1/browse/A/Compositae/	\N	\N	true	tpl	 [Ambiguous match] 	\N	t	Compositae

2014-05-31 20:04:19.706592-07	\N	10	Poa annua fo. lanuginosa	1	Poa annua fo. lanuginosa	fo.	1	Sennen	http://www.theplantlist.org/tpl1.1/record/tro-50267771	\N	\N	\N	\N	\N	Poa	1	annua	1	fo.	lanuginosa	1	\N	\N	\N	\N	\N	Synonym	Poa annua	L.	species	http://www.theplantlist.org/tpl1.1/record/kew-435194	Poa annua	Poaceae	true	tpl	 	\N	t	Poaceae Poa annua L.

2014-05-31 20:04:19.706592-07	\N	11	Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subsp.	0.770000000000000018	(S. Watson) C.L. Hitchc. & Maguire	http://www.theplantlist.org/tpl1.1/record/tro-6303627	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri subsp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subsp.	http://www.theplantlist.org/tpl1.1/record/tro-6303627	Silene scouleri	Caryophyllaceae	true	tpl	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire

2014-05-31 20:04:19.706592-07	\N	12	Fabaceae Inga "fuzzy leaf"	0.900000000000000022	Inga	genus	1	\N	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	"fuzzy leaf"	Accepted	Inga	\N	genus	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	Fabaceae	true	tpl;usda	 	\N	t	Fabaceae Inga

2014-05-31 20:04:19.706592-07	\N	13	Fabaceae Inga sp.3	0.900000000000000022	Inga	genus	1	\N	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	sp.3	Accepted	Inga	\N	genus	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	Fabaceae	true	tpl;usda	 	\N	t	Fabaceae Inga

2014-05-31 20:04:19.706592-07	\N	14	Fabaceae unknown #2	0.900000000000000022	Fabaceae	family	1	Lindl.	http://www.tropicos.org/Name/42000184	\N	\N	Fabaceae	1	Fabaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	Accepted	Fabaceae	Lindl.	family	http://www.tropicos.org/Name/42000184	\N	Fabaceae	true	tropicos	 	\N	t	Fabaceae Lindl.

2014-05-31 20:04:25.799698-07	\N	0	Poaceae Poa annua L.	1	Poa annua	species	1	L.	http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	L.	1	Poaceae	1	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	Poa annua	Poaceae	true	tpl;tropicos;usda	 	\N	t	Poaceae Poa annua L.

2014-05-31 20:04:25.799698-07	\N	1	Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn.	1	Poa annua subvar. minima	subvariety	1	(Schur) Asch. & Graebn.	http://www.tropicos.org/Name/50158097	(Schur) Asch. & Graebn.	1	Poaceae	1	Poaceae	Poa	1	annua	1	subvar.	minima	1	\N	\N	\N	\N	\N	Accepted	Poa annua subvar. minima	(Schur) Asch. & Graebn.	subvariety	http://www.tropicos.org/Name/50158097	Poa annua	Poaceae	true	tropicos	 	\N	t	Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn.

2014-05-31 20:04:25.799698-07	\N	2	Compositae	1	Compositae	family	1	\N	http://www.theplantlist.org/1.1/browse/A/Compositae/	\N	\N	Compositae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Compositae	\N	family	http://www.theplantlist.org/1.1/browse/A/Compositae/	\N	\N	true	tpl	 [Ambiguous match] 	\N	t	Compositae

2014-05-31 20:04:25.799698-07	\N	3	Fabaceae Lindl.	0.5	Fabaceae	family	0.5	Lindl.	http://www.tropicos.org/Name/42000184	\N	\N	Fabaceae	1	Fabaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Lindl.	Accepted	Fabaceae	Lindl.	family	http://www.tropicos.org/Name/42000184	\N	Fabaceae	true	tropicos	 [Partial match] 	\N	t	Fabaceae Lindl.

2014-05-31 20:04:25.799698-07	\N	4	Fabaceae Inga	1	Inga	genus	1	\N	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Inga	\N	genus	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	Fabaceae	true	tpl;usda	 	\N	t	Fabaceae Inga

2014-05-31 20:04:25.799698-07	\N	5	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	1	Silene scouleri subsp. pringlei	subsp.	1	(S. Watson) C.L. Hitchc. & Maguire	http://www.theplantlist.org/tpl1.1/record/tro-6303627	(S. Watson) C.L. Hitchc. & Maguire	1	Caryophyllaceae	1	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	\N	Accepted	Silene scouleri subsp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subsp.	http://www.theplantlist.org/tpl1.1/record/tro-6303627	Silene scouleri	Caryophyllaceae	true	tpl	 	\N	t	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire

2014-05-31 20:04:25.799698-07	\N	6	Poaceae Poa infirma Kunth	1	Poa infirma	species	1	Kunth	http://www.theplantlist.org/tpl1.1/record/kew-436189;http://www.tropicos.org/Name/25514158;http://plants.usda.gov/java/profile?symbol=POIN30	Kunth	1	Poaceae	1	Poaceae	Poa	1	infirma	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa infirma	Kunth	species	http://www.theplantlist.org/tpl1.1/record/kew-436189;http://www.tropicos.org/Name/25514158;http://plants.usda.gov/java/profile?symbol=POIN30	Poa infirma	Poaceae	true	tpl;tropicos;usda	 	\N	t	Poaceae Poa infirma Kunth

-- Name: batch_client_version_idx; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace: 

CREATE INDEX batch_client_version_idx ON batch USING btree (client_version);

-- Name: tnrs__valid_match; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace: 

--

CREATE UNIQUE INDEX tnrs__valid_match ON tnrs USING btree ("Name_submitted") WHERE is_valid_match;

--

-- Name: tnrs__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: bien

--

CREATE TRIGGER tnrs__batch_begin BEFORE INSERT ON tnrs FOR EACH STATEMENT EXECUTE PROCEDURE tnrs__batch_begin();

--

-- Name: MatchedTaxon; Type: ACL; Schema: TNRS; Owner: bien

--

REVOKE ALL ON TABLE "MatchedTaxon" FROM PUBLIC;

REVOKE ALL ON TABLE "MatchedTaxon" FROM bien;

GRANT ALL ON TABLE "MatchedTaxon" TO bien;

GRANT SELECT ON TABLE "MatchedTaxon" TO bien_read;

--

-- Name: ValidMatchedTaxon; Type: ACL; Schema: TNRS; Owner: bien

--

REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM PUBLIC;

REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM bien;

GRANT ALL ON TABLE "ValidMatchedTaxon" TO bien;

GRANT SELECT ON TABLE "ValidMatchedTaxon" TO bien_read;

--

-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: bien

--

REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM PUBLIC;

REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM bien;

GRANT ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien;

GRANT SELECT ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien_read;

--

-- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: bien

--

REVOKE ALL ON TABLE taxon_scrub FROM PUBLIC;

REVOKE ALL ON TABLE taxon_scrub FROM bien;

GRANT ALL ON TABLE taxon_scrub TO bien;

GRANT SELECT ON TABLE taxon_scrub TO bien_read;

--

SET lock_timeout = 0;

COMMENT ON SCHEMA "public.test_taxonomic_names" IS 'Version: public (2014-5-31 20:04:29 PDT)';

COMMENT ON TYPE coordinatesource IS '

From <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore_data_dictionary#coordinateSource>

';

COMMENT ON TYPE establishmentmeans_dwc IS '

See <http://rs.tdwg.org/dwc/terms/#establishmentMeans>

';

COMMENT ON TYPE placerank IS '

county = parish, canton

From <http://rs.tdwg.org/dwc/terms/#dcindex#dcterms:Location>, <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1415&entity=dba_fielddescription&params=1415>

';

COMMENT ON TYPE taxonrank IS '

From <http://www.tdwg.org/standards/117/download/>

';

COMMENT ON TYPE role IS '

From <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1331&entity=dba_fielddescription&params=1331>, <http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=projectcontributor&entity=dba_tabledescription&where=where_tablename#ROLE_ID>, and misc terms.

';

-- Name: schema_anchor; Type: TYPE; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE TYPE schema_anchor AS (

);

ALTER TYPE "public.test_taxonomic_names".schema_anchor OWNER TO bien;

--

-- Name: TYPE schema_anchor; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON TYPE schema_anchor IS 'used with schema_anchor params';

--

COMMENT ON TYPE sourcetype IS '

From <https://projects.nceas.ucsb.edu/nceas/attachments/download/428/bien_web_datasource_schema.sql>, <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1300&entity=dba_fielddescription&params=1300>, <http://vegbiendev.nceas.ucsb.edu/phppgadmin/display.php?server=localhost%3A5432%3Aallow&database=vegbien&schema=VegBank&table=source&subject=table#sourcetype>

';

COMMENT ON TYPE taxonclass IS '

= growthform + establishmentmeans_dwc + some others

';

COMMENT ON TYPE taxonomic_status IS '

See <http://tnrs.iplantcollaborative.org/instructions.html#Status>

';

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

SELECT util._km_to_m($1)

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '

_or() ignores NULL values, while OR combines them with the other values to potentially convert false to NULL. OR should be used with required fields, and _or() with optional fields.

';

    LANGUAGE sql IMMUTABLE

    LANGUAGE sql IMMUTABLE

-- Name: _taxonlabel_set_parent_id(integer, integer); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

CREATE FUNCTION _taxonlabel_set_parent_id(taxonlabel_id integer, parent_id integer) RETURNS integer

    LANGUAGE sql

    AS $_$

UPDATE taxonlabel SET parent_id = $2 WHERE taxonlabel_id = $1 RETURNING $1

$_$;

ALTER FUNCTION "public.test_taxonomic_names"._taxonlabel_set_parent_id(taxonlabel_id integer, parent_id integer) OWNER TO bien;

--

-- Name: _taxonomic_name_is_epithet(taxonrank); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION _taxonomic_name_is_epithet(rank taxonrank) RETURNS boolean

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT $1 <= 'genus'

$_$;

ALTER FUNCTION "public.test_taxonomic_names"._taxonomic_name_is_epithet(rank taxonrank) OWNER TO bien;

--

-- Name: datasource_publish(text, anyelement); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION datasource_publish(datasource text, schema_anchor anyelement DEFAULT NULL::schema_anchor) RETURNS void

    SET search_path TO pg_temp

/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime

changes of search_path (schema elements are bound at inline time rather than

runtime) */

/* function option search_path is needed to limit the effects of

`SET LOCAL search_path` to the current function */

DECLARE

	live_datasource text;

	PERFORM util.use_schema(schema_anchor);

	live_datasource = rm_version_suffix(datasource);

	-- don't datasource_rm() the datasource to publish!

	IF live_datasource = datasource THEN RETURN; END IF;

	PERFORM datasource_rm(live_datasource, schema_anchor);

	PERFORM datasource_rename(datasource, live_datasource, schema_anchor);

ALTER FUNCTION "public.test_taxonomic_names".datasource_publish(datasource text, schema_anchor anyelement) OWNER TO bien;

-- Name: FUNCTION datasource_publish(datasource text, schema_anchor anyelement); Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

COMMENT ON FUNCTION datasource_publish(datasource text, schema_anchor anyelement) IS '

secure against renamings of the public schema.

schema_anchor: identifies this function''s schema. should be left as the default.

';

--

-- Name: datasource_rename(text, text, anyelement); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION datasource_rename(old text, new text, schema_anchor anyelement DEFAULT NULL::schema_anchor) RETURNS void

    SET search_path TO pg_temp

/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime

changes of search_path (schema elements are bound at inline time rather than

runtime) */

/* function option search_path is needed to limit the effects of

`SET LOCAL search_path` to the current function */

	PERFORM util.use_schema(schema_anchor);

	UPDATE source SET shortname = new WHERE shortname = old;

ALTER FUNCTION "public.test_taxonomic_names".datasource_rename(old text, new text, schema_anchor anyelement) OWNER TO bien;

-- Name: FUNCTION datasource_rename(old text, new text, schema_anchor anyelement); Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

COMMENT ON FUNCTION datasource_rename(old text, new text, schema_anchor anyelement) IS '

secure against renamings of the public schema.

schema_anchor: identifies this function''s schema. should be left as the default.

';

-- Name: datasource_rm(text, anyelement); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION datasource_rm(datasource text, schema_anchor anyelement DEFAULT NULL::schema_anchor) RETURNS void

    LANGUAGE plpgsql

    SET search_path TO pg_temp

    AS $$

/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime

changes of search_path (schema elements are bound at inline time rather than

runtime) */

/* function option search_path is needed to limit the effects of

`SET LOCAL search_path` to the current function */

BEGIN

	PERFORM util.use_schema(schema_anchor);

	DELETE FROM source WHERE shortname = datasource;

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".datasource_rm(datasource text, schema_anchor anyelement) OWNER TO bien;

--

-- Name: FUNCTION datasource_rm(datasource text, schema_anchor anyelement); Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON FUNCTION datasource_rm(datasource text, schema_anchor anyelement) IS '

secure against renamings of the public schema.

schema_anchor: identifies this function''s schema. should be left as the default.

runtime:

ACAD:  30 s  :   27738 ms/   45,503 rows = 0.61 ms/row

MO  :  55 min: 3311962 ms/3,894,706 rows = 0.85 ms/row

GBIF: ~13 h  : 55,417,646 rows * 0.85 ms/row * 1 s/1000 ms * 1 h/3600 s = 13.1 h

to calculate runtime:

BEGIN;

SELECT datasource_rm(''datasource'');

ROLLBACK;

';

--

-- Name: datasource_unpublish(text, anyelement); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION datasource_unpublish(datasource text, schema_anchor anyelement DEFAULT NULL::schema_anchor) RETURNS void

    LANGUAGE plpgsql

    SET search_path TO pg_temp

    AS $$

/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime

changes of search_path (schema elements are bound at inline time rather than

runtime) */

/* function option search_path is needed to limit the effects of

`SET LOCAL search_path` to the current function */

BEGIN

	PERFORM util.use_schema(schema_anchor);

	PERFORM datasource_rename(datasource, datasource||'.new');

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".datasource_unpublish(datasource text, schema_anchor anyelement) OWNER TO bien;

--

-- Name: FUNCTION datasource_unpublish(datasource text, schema_anchor anyelement); Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON FUNCTION datasource_unpublish(datasource text, schema_anchor anyelement) IS '

secure against renamings of the public schema.

schema_anchor: identifies this function''s schema. should be left as the default.

';

--

    taxonoccurrence.source_id = (SELECT source_by_shortname($1))

AND taxondetermination.source_id = (SELECT source_by_shortname('TNRS'))

-- Name: location__pull_forward_from_parent(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION location__pull_forward_from_parent() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

DECLARE

	parent location;

BEGIN

	SELECT * INTO parent FROM location WHERE location_id = new.parent_id;

	IF new.place_id IS NULL THEN new.place_id = parent.place_id; END IF;

	RETURN new;

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".location__pull_forward_from_parent() OWNER TO bien;

--

-- Name: FUNCTION location__pull_forward_from_parent(); Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON FUNCTION location__pull_forward_from_parent() IS '

see wiki.vegpath.org/Pull-forward

';

--

-- Name: location_set_plot_location_id(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION location_set_plot_location_id() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

	new.plot_location_id = (CASE WHEN new.parent_id IS NULL

		THEN new.location_id

		ELSE (SELECT plot_location_id FROM location WHERE location_id = new.parent_id)

		END);

	RETURN new;

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".location_set_plot_location_id() OWNER TO bien;

--

-- Name: locationevent__communities(integer); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION locationevent__communities(locationevent_id integer) RETURNS SETOF text

    LANGUAGE sql STABLE

    AS $_$

SELECT commdescription

FROM      commclass

/* inner-join to commdetermination instead of left-joining, so that this does

not produce community entries (which occur because there is one commclass per

locationevent, but only some commclasses will then have a commdetermination) */

JOIN      commdetermination USING (commclass_id)

LEFT JOIN commconcept       USING (commconcept_id)

WHERE locationevent_id = $1

ORDER BY commdescription

$_$;

ALTER FUNCTION "public.test_taxonomic_names".locationevent__communities(locationevent_id integer) OWNER TO bien;

--

-- Name: locationevent__communities__array(integer); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION locationevent__communities__array(locationevent_id integer) RETURNS text[]

    LANGUAGE sql STABLE

    AS $_$

SELECT array_agg(locationevent__communities)

FROM locationevent__communities($1)

$_$;

ALTER FUNCTION "public.test_taxonomic_names".locationevent__communities__array(locationevent_id integer) OWNER TO bien;

--

-- Name: locationevent__contributors(integer); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION locationevent__contributors(locationevent_id integer) RETURNS SETOF text

    LANGUAGE sql STABLE

    AS $_$

SELECT fullname

FROM locationeventcontributor

LEFT JOIN party USING (party_id)

WHERE locationevent_id = $1

ORDER BY fullname

$_$;

ALTER FUNCTION "public.test_taxonomic_names".locationevent__contributors(locationevent_id integer) OWNER TO bien;

--

-- Name: locationevent__contributors__array(integer); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION locationevent__contributors__array(locationevent_id integer) RETURNS text[]

    LANGUAGE sql STABLE

    AS $_$

SELECT array_agg(locationevent__contributors)

FROM locationevent__contributors($1)

$_$;

ALTER FUNCTION "public.test_taxonomic_names".locationevent__contributors__array(locationevent_id integer) OWNER TO bien;

--

-- Name: locationevent_pull_forward_from_parent(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION locationevent_pull_forward_from_parent() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

DECLARE

	parent locationevent;

BEGIN

	SELECT * INTO parent FROM locationevent WHERE locationevent_id = new.parent_id;

	IF new.project_id IS NULL THEN new.project_id = parent.project_id; END IF;

	RETURN new;

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".locationevent_pull_forward_from_parent() OWNER TO bien;

--

-- Name: FUNCTION locationevent_pull_forward_from_parent(); Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON FUNCTION locationevent_pull_forward_from_parent() IS '

see wiki.vegpath.org/Pull-forward

';

--

-- Name: locationevent_pull_forward_from_parent_for_stratum(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION locationevent_pull_forward_from_parent_for_stratum() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

DECLARE

	parent locationevent;

BEGIN

	SELECT * INTO parent FROM locationevent WHERE locationevent_id = new.parent_id;

	IF new.location_id IS NULL THEN new.location_id = parent.location_id; END IF;

	RETURN new;

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".locationevent_pull_forward_from_parent_for_stratum() OWNER TO bien;

--

-- Name: FUNCTION locationevent_pull_forward_from_parent_for_stratum(); Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON FUNCTION locationevent_pull_forward_from_parent_for_stratum() IS '

see wiki.vegpath.org/Pull-forward

';

--

-- Name: locationevent_set_place_visit_id(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION locationevent_set_place_visit_id() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

	new.place_visit_id = (CASE WHEN new.parent_id IS NULL

		THEN new.locationevent_id

		ELSE (SELECT place_visit_id FROM locationevent WHERE locationevent_id = new.parent_id)

		END);

	RETURN new;

END;