-- Name: _plots_06a_list_of_stems; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE VIEW _plots_06a_list_of_stems AS

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    (s.stem_id)::text AS stem_id

   FROM (((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))

   JOIN stems s ON ((po."PlotObsID" = s.plotobs_id)))

  ORDER BY p.project_name, (pm."SiteCode")::text, (s.stem_id)::text;

--

-- Name: keys_~type._plots_06a_list_of_stems; Type: TYPE; Schema: public_validations; Owner: -

--

--

-- Name: values__~type._plots_06a_list_of_stems; Type: TYPE; Schema: public_validations; Owner: -

--

--

-- Name: ~type._plots_06a_list_of_stems; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: 

--

CREATE TABLE `~type._plots_06a_list_of_stems` (

    project_name varchar(255),

    plot_code varchar(255),

    stem_id varchar(255)

);

--

-- Name: keys(`~type._plots_06a_list_of_stems`); Type: FUNCTION; Schema: public_validations; Owner: -

--

--

-- Name: values_(`~type._plots_06a_list_of_stems`); Type: FUNCTION; Schema: public_validations; Owner: -

--

--

-- Name: _plots_06a_list_of_stems; Type: VIEW; Schema: public_validations; Owner: -

--

--

-- Data for Name: ~type._plots_06a_list_of_stems; Type: TABLE DATA; Schema: public_validations; Owner: -

--

--

-- Name: keys_~type._plots_06a_list_of_stems; Type: TYPE; Schema: public_validations; Owner: -

--

CREATE TYPE "keys_~type._plots_06a_list_of_stems" AS (

	project_name text,

	plot_code text,

	stem_id text

);

--

-- Name: values__~type._plots_06a_list_of_stems; Type: TYPE; Schema: public_validations; Owner: -

--

CREATE TYPE "values__~type._plots_06a_list_of_stems" AS (

	project_name text,

	plot_code text,

	stem_id text

);

--

-- Name: ~type._plots_06a_list_of_stems; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: 

--

CREATE TABLE "~type._plots_06a_list_of_stems" (

    project_name text,

    plot_code text,

    stem_id text

);

--

-- Name: keys("~type._plots_06a_list_of_stems"); Type: FUNCTION; Schema: public_validations; Owner: -

--

CREATE FUNCTION keys(value "~type._plots_06a_list_of_stems") RETURNS "keys_~type._plots_06a_list_of_stems"

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT ROW($1.project_name, $1.plot_code, $1.stem_id)::public_validations."keys_~type._plots_06a_list_of_stems"

$_$;

--

-- Name: values_("~type._plots_06a_list_of_stems"); Type: FUNCTION; Schema: public_validations; Owner: -

--

CREATE FUNCTION values_(value "~type._plots_06a_list_of_stems") RETURNS "values__~type._plots_06a_list_of_stems"

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT ROW($1.project_name, $1.plot_code, $1.stem_id)::public_validations."values__~type._plots_06a_list_of_stems"

$_$;

--

-- Name: _plots_06a_list_of_stems; Type: VIEW; Schema: public_validations; Owner: -

--

CREATE VIEW _plots_06a_list_of_stems AS

 SELECT project.projectname AS project_name, 

    plot.authorlocationcode AS plot_code, 

    stemobservation.sourceaccessioncode AS stem_id

   FROM ((((((public.plot

   JOIN public.locationevent USING (plot_id))

   LEFT JOIN public.project USING (project_id))

   JOIN public.taxonoccurrence USING (locationevent_id))

   JOIN public.aggregateoccurrence USING (taxonoccurrence_id))

   JOIN public.plantobservation USING (aggregateoccurrence_id))

   JOIN public.stemobservation USING (plantobservation_id))

  WHERE ((plot.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND ((stemobservation.sourceaccessioncode IS NOT NULL) OR (stemobservation.authorstemcode IS NOT NULL)))

  ORDER BY project.projectname, plot.authorlocationcode, stemobservation.sourceaccessioncode;

--

-- Data for Name: ~type._plots_06a_list_of_stems; Type: TABLE DATA; Schema: public_validations; Owner: -

--

--