Revision 14346
Added by Aaron Marcuse-Kubitza over 10 years ago
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