Revision 14346
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 7989 | 7989 |
|
| 7990 | 7990 |
|
| 7991 | 7991 |
-- |
| 7992 |
-- Name: TABLE taxon_trait; Type: COMMENT; Schema: public; Owner: - |
|
| 7993 |
-- |
|
| 7994 |
|
|
| 7995 |
|
|
| 7996 |
|
|
| 7997 |
|
|
| 7998 |
-- |
|
| 7999 | 7992 |
-- Name: trait; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 8000 | 7993 |
-- |
| 8001 | 7994 |
|
| trunk/schemas/public_.sql | ||
|---|---|---|
| 1988 | 1988 |
CREATE FUNCTION analytical_stem_view_modify() RETURNS void |
| 1989 | 1989 |
LANGUAGE sql |
| 1990 | 1990 |
AS $_$ |
| 1991 |
SELECT util.recreate($$ |
|
| 1992 |
DROP TABLE IF EXISTS analytical_stem; |
|
| 1993 |
SELECT util.copy('analytical_stem_view'::regclass, 'analytical_stem');
|
|
| 1991 |
SELECT util.rematerialize_view('analytical_stem', 'analytical_stem_view', $$
|
|
| 1994 | 1992 |
ALTER TABLE analytical_stem ADD COLUMN "TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org" serial PRIMARY KEY; |
| 1995 | 1993 |
COMMENT ON COLUMN analytical_stem."TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org" IS 'VegBIEN-autogenerated "identifier assigned to each unique observation of a taxon in a plot"'; |
| 1996 | 1994 |
|
| ... | ... | |
| 2173 | 2171 |
|
| 2174 | 2172 |
CREATE FUNCTION geoscrub_input_view_modify() RETURNS void |
| 2175 | 2173 |
LANGUAGE sql |
| 2176 |
AS $$ |
|
| 2177 |
DROP TABLE IF EXISTS geoscrub_input; |
|
| 2178 |
SELECT util.copy('geoscrub_input_view'::regclass, 'geoscrub_input');
|
|
| 2179 |
|
|
| 2174 |
AS $_$ |
|
| 2175 |
SELECT util.rematerialize_view('geoscrub_input', 'geoscrub_input_view', $$
|
|
| 2180 | 2176 |
GRANT SELECT ON TABLE geoscrub_input TO bien_read; |
| 2181 | 2177 |
GRANT SELECT ON TABLE geoscrub_input_view TO bien_read; |
| 2182 |
$$; |
|
| 2178 |
$$); |
|
| 2179 |
$_$; |
|
| 2183 | 2180 |
|
| 2184 | 2181 |
|
| 2185 | 2182 |
-- |
| ... | ... | |
| 2258 | 2255 |
|
| 2259 | 2256 |
CREATE FUNCTION iucn_red_list_view_modify() RETURNS void |
| 2260 | 2257 |
LANGUAGE sql |
| 2261 |
AS $$ |
|
| 2262 |
SELECT util.rematerialize_view('iucn_red_list', 'iucn_red_list_view');
|
|
| 2263 |
|
|
| 2258 |
AS $_$ |
|
| 2259 |
SELECT util.rematerialize_view('iucn_red_list', 'iucn_red_list_view', $$
|
|
| 2264 | 2260 |
ALTER TABLE iucn_red_list ADD PRIMARY KEY(accepted_family, accepted_species_binomial); |
| 2265 |
$$; |
|
| 2261 |
$$); |
|
| 2262 |
$_$; |
|
| 2266 | 2263 |
|
| 2267 | 2264 |
|
| 2268 | 2265 |
-- |
| ... | ... | |
| 2814 | 2811 |
|
| 2815 | 2812 |
CREATE FUNCTION provider_count_view_modify() RETURNS void |
| 2816 | 2813 |
LANGUAGE sql |
| 2817 |
AS $$ |
|
| 2818 |
DROP TABLE IF EXISTS provider_count; |
|
| 2819 |
SELECT util.copy('provider_count_view'::regclass, 'provider_count');
|
|
| 2820 |
|
|
| 2814 |
AS $_$ |
|
| 2815 |
SELECT util.rematerialize_view('provider_count', 'provider_count_view', $$
|
|
| 2821 | 2816 |
GRANT SELECT ON TABLE provider_count TO bien_read; |
| 2822 | 2817 |
GRANT SELECT ON TABLE provider_count TO public_; |
| 2823 | 2818 |
GRANT SELECT ON TABLE provider_count_view TO bien_read; |
| 2824 | 2819 |
GRANT SELECT ON TABLE provider_count_view TO public_; |
| 2825 | 2820 |
|
| 2826 | 2821 |
ALTER TABLE provider_count ADD PRIMARY KEY (dataprovider, dataset); |
| 2827 |
$$; |
|
| 2822 |
$$); |
|
| 2823 |
$_$; |
|
| 2828 | 2824 |
|
| 2829 | 2825 |
|
| 2830 | 2826 |
-- |
| ... | ... | |
| 3040 | 3036 |
|
| 3041 | 3037 |
CREATE FUNCTION taxon_trait_view_modify() RETURNS void |
| 3042 | 3038 |
LANGUAGE sql |
| 3043 |
AS $$ |
|
| 3044 |
DROP TABLE IF EXISTS taxon_trait; |
|
| 3045 |
SELECT util.copy('taxon_trait_view'::regclass, 'taxon_trait');
|
|
| 3046 |
|
|
| 3039 |
AS $_$ |
|
| 3040 |
SELECT util.rematerialize_view('taxon_trait', 'taxon_trait_view', $$
|
|
| 3047 | 3041 |
GRANT SELECT ON TABLE taxon_trait TO bien_read; |
| 3048 | 3042 |
GRANT SELECT ON TABLE taxon_trait_view TO bien_read; |
| 3049 | 3043 |
|
| ... | ... | |
| 3051 | 3045 |
ALTER TABLE taxon_trait ALTER COLUMN "measurementType" SET NOT NULL; |
| 3052 | 3046 |
|
| 3053 | 3047 |
CREATE INDEX ON taxon_trait ("scientificName", "measurementType" );
|
| 3054 |
$$; |
|
| 3048 |
$$); |
|
| 3049 |
$_$; |
|
| 3055 | 3050 |
|
| 3056 | 3051 |
|
| 3057 | 3052 |
-- |
| ... | ... | |
| 11278 | 11273 |
|
| 11279 | 11274 |
|
| 11280 | 11275 |
-- |
| 11281 |
-- Name: TABLE taxon_trait; Type: COMMENT; Schema: public; Owner: - |
|
| 11282 |
-- |
|
| 11283 |
|
|
| 11284 |
COMMENT ON TABLE taxon_trait IS ' |
|
| 11285 |
to export: |
|
| 11286 |
select * from taxon_trait order by "scientificName", "measurementType" |
|
| 11287 |
'; |
|
| 11288 |
|
|
| 11289 |
|
|
| 11290 |
-- |
|
| 11291 | 11276 |
-- Name: trait; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 11292 | 11277 |
-- |
| 11293 | 11278 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 1988 | 1988 |
CREATE FUNCTION analytical_stem_view_modify() RETURNS void |
| 1989 | 1989 |
LANGUAGE sql |
| 1990 | 1990 |
AS $_$ |
| 1991 |
SELECT util.recreate($$ |
|
| 1992 |
DROP TABLE IF EXISTS analytical_stem; |
|
| 1993 |
SELECT util.copy('analytical_stem_view'::regclass, 'analytical_stem');
|
|
| 1991 |
SELECT util.rematerialize_view('analytical_stem', 'analytical_stem_view', $$
|
|
| 1994 | 1992 |
ALTER TABLE analytical_stem ADD COLUMN "TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org" serial PRIMARY KEY; |
| 1995 | 1993 |
COMMENT ON COLUMN analytical_stem."TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org" IS 'VegBIEN-autogenerated "identifier assigned to each unique observation of a taxon in a plot"'; |
| 1996 | 1994 |
|
| ... | ... | |
| 2173 | 2171 |
|
| 2174 | 2172 |
CREATE FUNCTION geoscrub_input_view_modify() RETURNS void |
| 2175 | 2173 |
LANGUAGE sql |
| 2176 |
AS $$ |
|
| 2177 |
DROP TABLE IF EXISTS geoscrub_input; |
|
| 2178 |
SELECT util.copy('geoscrub_input_view'::regclass, 'geoscrub_input');
|
|
| 2179 |
|
|
| 2174 |
AS $_$ |
|
| 2175 |
SELECT util.rematerialize_view('geoscrub_input', 'geoscrub_input_view', $$
|
|
| 2180 | 2176 |
GRANT SELECT ON TABLE geoscrub_input TO bien_read; |
| 2181 | 2177 |
GRANT SELECT ON TABLE geoscrub_input_view TO bien_read; |
| 2182 |
$$; |
|
| 2178 |
$$); |
|
| 2179 |
$_$; |
|
| 2183 | 2180 |
|
| 2184 | 2181 |
|
| 2185 | 2182 |
-- |
| ... | ... | |
| 2258 | 2255 |
|
| 2259 | 2256 |
CREATE FUNCTION iucn_red_list_view_modify() RETURNS void |
| 2260 | 2257 |
LANGUAGE sql |
| 2261 |
AS $$ |
|
| 2262 |
SELECT util.rematerialize_view('iucn_red_list', 'iucn_red_list_view');
|
|
| 2263 |
|
|
| 2258 |
AS $_$ |
|
| 2259 |
SELECT util.rematerialize_view('iucn_red_list', 'iucn_red_list_view', $$
|
|
| 2264 | 2260 |
ALTER TABLE iucn_red_list ADD PRIMARY KEY(accepted_family, accepted_species_binomial); |
| 2265 |
$$; |
|
| 2261 |
$$); |
|
| 2262 |
$_$; |
|
| 2266 | 2263 |
|
| 2267 | 2264 |
|
| 2268 | 2265 |
-- |
| ... | ... | |
| 2814 | 2811 |
|
| 2815 | 2812 |
CREATE FUNCTION provider_count_view_modify() RETURNS void |
| 2816 | 2813 |
LANGUAGE sql |
| 2817 |
AS $$ |
|
| 2818 |
DROP TABLE IF EXISTS provider_count; |
|
| 2819 |
SELECT util.copy('provider_count_view'::regclass, 'provider_count');
|
|
| 2820 |
|
|
| 2814 |
AS $_$ |
|
| 2815 |
SELECT util.rematerialize_view('provider_count', 'provider_count_view', $$
|
|
| 2821 | 2816 |
GRANT SELECT ON TABLE provider_count TO bien_read; |
| 2822 | 2817 |
GRANT SELECT ON TABLE provider_count TO public_; |
| 2823 | 2818 |
GRANT SELECT ON TABLE provider_count_view TO bien_read; |
| 2824 | 2819 |
GRANT SELECT ON TABLE provider_count_view TO public_; |
| 2825 | 2820 |
|
| 2826 | 2821 |
ALTER TABLE provider_count ADD PRIMARY KEY (dataprovider, dataset); |
| 2827 |
$$; |
|
| 2822 |
$$); |
|
| 2823 |
$_$; |
|
| 2828 | 2824 |
|
| 2829 | 2825 |
|
| 2830 | 2826 |
-- |
| ... | ... | |
| 3040 | 3036 |
|
| 3041 | 3037 |
CREATE FUNCTION taxon_trait_view_modify() RETURNS void |
| 3042 | 3038 |
LANGUAGE sql |
| 3043 |
AS $$ |
|
| 3044 |
DROP TABLE IF EXISTS taxon_trait; |
|
| 3045 |
SELECT util.copy('taxon_trait_view'::regclass, 'taxon_trait');
|
|
| 3046 |
|
|
| 3039 |
AS $_$ |
|
| 3040 |
SELECT util.rematerialize_view('taxon_trait', 'taxon_trait_view', $$
|
|
| 3047 | 3041 |
GRANT SELECT ON TABLE taxon_trait TO bien_read; |
| 3048 | 3042 |
GRANT SELECT ON TABLE taxon_trait_view TO bien_read; |
| 3049 | 3043 |
|
| ... | ... | |
| 3051 | 3045 |
ALTER TABLE taxon_trait ALTER COLUMN "measurementType" SET NOT NULL; |
| 3052 | 3046 |
|
| 3053 | 3047 |
CREATE INDEX ON taxon_trait ("scientificName", "measurementType" );
|
| 3054 |
$$; |
|
| 3048 |
$$); |
|
| 3049 |
$_$; |
|
| 3055 | 3050 |
|
| 3056 | 3051 |
|
| 3057 | 3052 |
-- |
| ... | ... | |
| 11278 | 11273 |
|
| 11279 | 11274 |
|
| 11280 | 11275 |
-- |
| 11281 |
-- Name: TABLE taxon_trait; Type: COMMENT; Schema: public; Owner: - |
|
| 11282 |
-- |
|
| 11283 |
|
|
| 11284 |
COMMENT ON TABLE taxon_trait IS ' |
|
| 11285 |
to export: |
|
| 11286 |
select * from taxon_trait order by "scientificName", "measurementType" |
|
| 11287 |
'; |
|
| 11288 |
|
|
| 11289 |
|
|
| 11290 |
-- |
|
| 11291 | 11276 |
-- Name: trait; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 11292 | 11277 |
-- |
| 11293 | 11278 |
|
Also available in: Unified diff
schemas/public_.sql: *_view_modify(): use util.rematerialize_view(), which now supports table mods