-- Name: sync_taxon_trait_to_view(); Type: FUNCTION; Schema: public; Owner: -

--

CREATE FUNCTION sync_taxon_trait_to_view() RETURNS void

    LANGUAGE sql

    AS $$

DROP TABLE IF EXISTS taxon_trait;

CREATE TABLE taxon_trait AS SELECT * FROM taxon_trait_view LIMIT 0;

GRANT SELECT ON TABLE taxon_trait TO bien_read;

GRANT SELECT ON TABLE taxon_trait_view TO bien_read;

ALTER TABLE taxon_trait ADD PRIMARY KEY ("scientificName", "measurementType");

$$;

--

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

--

CREATE TABLE taxon_trait (

    "scientificName" text NOT NULL,

    "measurementType" text NOT NULL,

    "measurementValue" text,

    "measurementUnit" text

);

--

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

--

CREATE TABLE trait (

    trait_id integer NOT NULL,

    taxonoccurrence_id integer NOT NULL,

    name text NOT NULL,

    value text,

    units text

);

--

-- Name: taxon_trait_view; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW taxon_trait_view AS

    SELECT accepted_taxonlabel.taxonomicname AS "scientificName", trait.name AS "measurementType", trait.value AS "measurementValue", trait.name AS "measurementUnit" FROM (((((trait LEFT JOIN taxonoccurrence USING (taxonoccurrence_id)) LEFT JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) LEFT JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) WHERE COALESCE(taxondetermination.iscurrent, true);

--

-- Data for Name: taxon_trait; Type: TABLE DATA; Schema: public; Owner: -

--

--

-- Name: taxon_trait_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

--

ALTER TABLE ONLY taxon_trait

    ADD CONSTRAINT taxon_trait_pkey PRIMARY KEY ("scientificName", "measurementType");

--

-- Name: taxon_trait; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE taxon_trait FROM PUBLIC;

REVOKE ALL ON TABLE taxon_trait FROM bien;

GRANT ALL ON TABLE taxon_trait TO bien;

GRANT SELECT ON TABLE taxon_trait TO bien_read;

--

-- Name: trait; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE trait FROM PUBLIC;

REVOKE ALL ON TABLE trait FROM bien;

GRANT ALL ON TABLE trait TO bien;

GRANT SELECT ON TABLE trait TO bien_read;

--

-- Name: taxon_trait_view; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE taxon_trait_view FROM PUBLIC;

REVOKE ALL ON TABLE taxon_trait_view FROM bien;

GRANT ALL ON TABLE taxon_trait_view TO bien;

GRANT SELECT ON TABLE taxon_trait_view TO bien_read;

--