SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;
DROP VIEW IF EXISTS ucsb_view_identifier;
DROP VIEW IF EXISTS ucsb_view_systemmetadata;
DROP VIEW IF EXISTS orc_view_identifier;
DROP VIEW IF EXISTS orc_view_systemmetadata;
DROP VIEW IF EXISTS unm_view_iidentifier;
DROP VIEW IF EXISTS unm_view_systemmetadata;


DROP TABLE IF EXISTS ucsb_identifier_zed;
DROP TABLE IF EXISTS ucsb_systemmetadata_zed;
DROP TABLE IF EXISTS orc_identifier_zed;
DROP TABLE IF EXISTS orc_systemmetadata_zed;
DROP TABLE IF EXISTS unm_identifier_zed;
DROP TABLE IF EXISTS unm_systemmetadata_zed;


CREATE TABLE ucsb_identifier_zed
    AS (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and s.authoritive_member_node like 'urn:node:ESA' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CDL' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and  authoritive_member_node like 'urn:node:SEAD' order by i.guid )
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:KNB' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:USGSCSAS' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:PISCO' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:ONEShare' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CLOAKN' order by i.guid )
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:LTER' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:ORNLDAAC' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CNUCSB1' order by i.guid )
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:USANPN' order by i.guid )
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CNUNM1' order by i.guid )
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:GOA' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:SANPARKS' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from ucsb_identifier i, ucsb_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:TFRI' order by i.guid  LIMIT 100)
        UNION
        (select i.* 
        from ucsb_identifier i 
        where not exists 
        (select * from ucsb_systemmetadata s where i.guid = s.guid)) ;
    
CREATE TABLE ucsb_systemmetadata_zed
    AS (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:ESA' order by guid  LIMIT 100)
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:CDL' order by guid  LIMIT 100)
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:SEAD' order by guid )
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:KNB' order by guid  LIMIT 100)
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:USGSCSAS' order by guid  LIMIT 100)
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:PISCO' order by guid  LIMIT 100)
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:ONEShare' order by guid  LIMIT 100)
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:CLOAKN' order by guid )
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:LTER' order by guid  LIMIT 100)
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:ORNLDAAC' order by guid  LIMIT 100)
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:CNUCSB1' order by guid )
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:USANPN' order by guid )
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:CNUNM1' order by guid )
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:GOA' order by guid  LIMIT 100)
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:SANPARKS' order by guid  LIMIT 100)
        UNION
        (select *
        from ucsb_systemmetadata
        where authoritive_member_node like 'urn:node:TFRI' order by guid  LIMIT 100);

ALTER TABLE public.ucsb_identifier_zed OWNER TO metacat; 
ALTER TABLE public.ucsb_systemmetadata_zed OWNER TO metacat; 

--
-- Name: identifier_pk; Type: CONSTRAINT; Schema: public; Owner: metacat; Tablespace: 
--

ALTER TABLE ONLY ucsb_identifier_zed
    ADD CONSTRAINT ucsb_identifier_zed_pk PRIMARY KEY (guid);

--
-- Name: systemmetadata_pk; Type: CONSTRAINT; Schema: public; Owner: metacat; Tablespace: 
--

ALTER TABLE ONLY ucsb_systemmetadata_zed
    ADD CONSTRAINT ucsb_systemmetadata_zed_pk PRIMARY KEY (guid);

--
-- Name: identifier_docid; Type: INDEX; Schema: public; Owner: metacat; Tablespace: 
--
DROP INDEX IF EXISTS ucsb_identifier_zed_docid;
CREATE INDEX ucsb_identifier_zed_docid ON ucsb_identifier_zed USING btree (docid);

--
-- Name: identifier_guid; Type: INDEX; Schema: public; Owner: metacat; Tablespace: 
--
DROP INDEX IF EXISTS ucsb_identifier_guid_zed;
CREATE INDEX ucsb_identifier_guid_zed ON ucsb_identifier_zed USING btree (guid);


     
CREATE TABLE orc_identifier_zed
    AS (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and s.authoritive_member_node like 'urn:node:ESA' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CDL' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and  authoritive_member_node like 'urn:node:SEAD' order by i.guid )
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:KNB' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:USGSCSAS' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:PISCO' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:ONEShare' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CLOAKN' order by i.guid )
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:LTER' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:ORNLDAAC' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CNUCSB1' order by i.guid )
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:USANPN' order by i.guid )
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CNUNM1' order by i.guid )
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:GOA' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:SANPARKS' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from orc_identifier i, orc_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:TFRI' order by i.guid  LIMIT 100)
        UNION
        (select i.* 
        from orc_identifier i 
        where not exists 
        (select * from orc_systemmetadata s where i.guid = s.guid)) ;
 
CREATE TABLE orc_systemmetadata_zed
    AS (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:ESA' order by guid  LIMIT 100)
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:CDL' order by guid  LIMIT 100)
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:SEAD' order by guid )
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:KNB' order by guid  LIMIT 100)
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:USGSCSAS' order by guid  LIMIT 100)
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:PISCO' order by guid  LIMIT 100)
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:ONEShare' order by guid  LIMIT 100)
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:CLOAKN' order by guid )
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:LTER' order by guid  LIMIT 100)
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:ORNLDAAC' order by guid  LIMIT 100)
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:CNUCSB1' order by guid )
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:USANPN' order by guid )
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:CNUNM1' order by guid )
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:GOA' order by guid  LIMIT 100)
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:SANPARKS' order by guid  LIMIT 100)
        UNION
        (select *
        from orc_systemmetadata
        where authoritive_member_node like 'urn:node:TFRI' order by guid  LIMIT 100);

ALTER TABLE public.orc_identifier_zed OWNER TO metacat; 
ALTER TABLE public.orc_systemmetadata_zed OWNER TO metacat;
--
-- Name: identifier_pk; Type: CONSTRAINT; Schema: public; Owner: metacat; Tablespace: 
--

ALTER TABLE ONLY orc_identifier_zed
    ADD CONSTRAINT orc_identifier_zed_pk PRIMARY KEY (guid);

--
-- Name: systemmetadata_pk; Type: CONSTRAINT; Schema: public; Owner: metacat; Tablespace: 
--

ALTER TABLE ONLY orc_systemmetadata_zed
    ADD CONSTRAINT orc_systemmetadata_zed_pk PRIMARY KEY (guid);

--
-- Name: identifier_docid; Type: INDEX; Schema: public; Owner: metacat; Tablespace: 
--
DROP INDEX IF EXISTS orc_identifier_zed_docid;
CREATE INDEX orc_identifier_zed_docid ON orc_identifier_zed USING btree (docid);

--
-- Name: identifier_guid; Type: INDEX; Schema: public; Owner: metacat; Tablespace: 
--
DROP INDEX IF EXISTS orc_identifier_guid_zed;
CREATE INDEX orc_identifier_guid_zed ON orc_identifier_zed USING btree (guid);


    
CREATE TABLE unm_identifier_zed
    AS (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and s.authoritive_member_node like 'urn:node:ESA' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CDL' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and  authoritive_member_node like 'urn:node:SEAD' order by i.guid )
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:KNB' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:USGSCSAS' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:PISCO' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:ONEShare' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CLOAKN' order by i.guid )
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:LTER' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:ORNLDAAC' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CNUCSB1' order by i.guid )
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:USANPN' order by i.guid )
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:CNUNM1' order by i.guid )
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:GOA' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:SANPARKS' order by i.guid  LIMIT 100)
        UNION
        (select i.*
        from unm_identifier i, unm_systemmetadata s
        where i.guid = s.guid
        and authoritive_member_node like 'urn:node:TFRI' order by i.guid  LIMIT 100)
        UNION
        (select i.* 
        from unm_identifier i 
        where not exists 
        (select * from unm_systemmetadata s where i.guid = s.guid)) ;
     
CREATE TABLE unm_systemmetadata_zed
    AS (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:ESA' order by guid  LIMIT 100)
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:CDL' order by guid  LIMIT 100)
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:SEAD' order by guid )
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:KNB' order by guid  LIMIT 100)
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:USGSCSAS' order by guid  LIMIT 100)
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:PISCO' order by guid  LIMIT 100)
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:ONEShare' order by guid  LIMIT 100)
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:CLOAKN' order by guid )
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:LTER' order by guid  LIMIT 100)
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:ORNLDAAC' order by guid  LIMIT 100)
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:CNUCSB1' order by guid )
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:USANPN' order by guid )
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:CNUNM1' order by guid )
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:GOA' order by guid  LIMIT 100)
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:SANPARKS' order by guid  LIMIT 100)
        UNION
        (select *
        from unm_systemmetadata
        where authoritive_member_node like 'urn:node:TFRI' order by guid  LIMIT 100);

ALTER TABLE public.unm_identifier_zed OWNER TO metacat; 
ALTER TABLE public.unm_systemmetadata_zed OWNER TO metacat;
--
-- Name: identifier_pk; Type: CONSTRAINT; Schema: public; Owner: metacat; Tablespace: 
--

ALTER TABLE ONLY unm_identifier_zed
    ADD CONSTRAINT unm_identifier_zed_pk PRIMARY KEY (guid);

--
-- Name: systemmetadata_pk; Type: CONSTRAINT; Schema: public; Owner: metacat; Tablespace: 
--

ALTER TABLE ONLY unm_systemmetadata_zed
    ADD CONSTRAINT unm_systemmetadata_zed_pk PRIMARY KEY (guid);

--
-- Name: identifier_docid; Type: INDEX; Schema: public; Owner: metacat; Tablespace: 
--
DROP INDEX IF EXISTS unm_identifier_zed_docid;
CREATE INDEX unm_identifier_zed_docid ON unm_identifier_zed USING btree (docid);

--
-- Name: identifier_guid; Type: INDEX; Schema: public; Owner: metacat; Tablespace: 
--
DROP INDEX IF EXISTS unm_identifier_guid_zed;
CREATE INDEX unm_identifier_guid_zed ON unm_identifier_zed USING btree (guid);