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

--

CREATE FUNCTION make_family_higher_plant_group() RETURNS void

    LANGUAGE plpgsql

    AS $$

DECLARE

    reference_id_ integer :=

        (SELECT reference_id FROM reference WHERE shortname = 'NCBI');

    row_ higher_plant_group_nodes%ROWTYPE;

BEGIN

    TRUNCATE family_higher_plant_group;

    FOR row_ IN

        SELECT * FROM higher_plant_group_nodes

        ORDER BY higher_plant_group, node_name

    LOOP

        DECLARE

            higher_plant_group_id integer := (

                SELECT taxonlabel_id FROM taxonlabel

                WHERE reference_id = reference_id_

                AND taxonomicname = row_.node_name

            );

            family text;

        BEGIN

            FOR family IN

                SELECT taxonepithet

                FROM taxonlabel_relationship

                JOIN taxonlabel ON taxonlabel_id = descendant_id

                WHERE ancestor_id = higher_plant_group_id

                AND rank = 'family'

                AND reference_id = reference_id_

            LOOP

                BEGIN

                    INSERT INTO family_higher_plant_group

                    VALUES (family, row_.higher_plant_group)

                    ;

                EXCEPTION

                    WHEN unique_violation THEN NULL;

                END;

            END LOOP;

        END;

    END LOOP;

END;

$$;

--

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

--

CREATE TABLE family_higher_plant_group (

    family text NOT NULL,

    higher_plant_group higher_plant_group

);

--

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

--

COPY family_higher_plant_group (family, higher_plant_group) FROM stdin;

\.

--

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

--

ALTER TABLE ONLY family_higher_plant_group

    ADD CONSTRAINT family_higher_plant_group_pkey PRIMARY KEY (family);

--