Revision 14464
Added by Aaron Marcuse-Kubitza over 10 years ago
| vegbien.sql | ||
|---|---|---|
| 1982 | 1982 |
|
| 1983 | 1983 |
|
| 1984 | 1984 |
-- |
| 1985 |
-- Name: analytical_stem_view_modify(boolean); Type: FUNCTION; Schema: public; Owner: -
|
|
| 1985 |
-- Name: analytical_stem_view_modify(text, anyelement); Type: FUNCTION; Schema: public; Owner: -
|
|
| 1986 | 1986 |
-- |
| 1987 | 1987 |
|
| 1988 |
CREATE FUNCTION analytical_stem_view_modify(repopulate boolean DEFAULT true) RETURNS void |
|
| 1989 |
LANGUAGE sql |
|
| 1990 |
AS $_$ |
|
| 1991 |
SELECT util.rematerialize_view('analytical_stem', 'analytical_stem_view', $$
|
|
| 1992 |
ALTER TABLE analytical_stem ADD COLUMN "TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org" serial PRIMARY KEY; |
|
| 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"'; |
|
| 1988 |
CREATE FUNCTION analytical_stem_view_modify(view_query text DEFAULT NULL::text, schema_anchor anyelement DEFAULT NULL::schema_anchor) RETURNS void |
|
| 1989 |
LANGUAGE plpgsql |
|
| 1990 |
AS $$ |
|
| 1991 |
/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime |
|
| 1992 |
changes of search_path (schema elements are bound at inline time rather than |
|
| 1993 |
runtime) */ |
|
| 1994 |
/* function option search_path is needed to limit the effects of |
|
| 1995 |
`SET LOCAL search_path` to the current function */ |
|
| 1996 |
BEGIN |
|
| 1997 |
PERFORM util.use_schema(schema_anchor); |
|
| 1998 |
|
|
| 1999 |
PERFORM util.recreate_view('analytical_stem_view', view_query);
|
|
| 2000 |
END; |
|
| 2001 |
$$; |
|
| 1994 | 2002 |
|
| 1995 |
GRANT SELECT ON TABLE analytical_stem TO bien_read; |
|
| 1996 |
GRANT SELECT ON TABLE analytical_stem_view TO bien_read; |
|
| 1997 | 2003 |
|
| 1998 |
ALTER TABLE analytical_stem ALTER COLUMN "datasource__@Brad__.identifier_examples@vegpath.org" SET NOT NULL; |
|
| 1999 |
ALTER TABLE analytical_stem ALTER COLUMN "locationID__@DwC__@vegpath.org" SET NOT NULL; |
|
| 2004 |
-- |
|
| 2005 |
-- Name: FUNCTION analytical_stem_view_modify(view_query text, schema_anchor anyelement); Type: COMMENT; Schema: public; Owner: - |
|
| 2006 |
-- |
|
| 2000 | 2007 |
|
| 2001 |
CREATE INDEX ON analytical_stem ("datasource__@Brad__.identifier_examples@vegpath.org", "occurrenceID__@DwC__@vegpath.org");
|
|
| 2002 |
CREATE INDEX ON analytical_stem ("datasource__@Brad__.identifier_examples@vegpath.org", "[custodial_]institutionCode[s]__@DwC__@vegpath.org", "collectionCode__@DwC__@vegpath.org", "catalogNumber__@DwC__@vegpath.org");
|
|
| 2003 |
CREATE INDEX ON analytical_stem ("datasource__@Brad__.identifier_examples@vegpath.org", "projectID__@VegX__.plotObservation@vegpath.org", "locationID__@DwC__@vegpath.org", "eventDate__@DwC__@vegpath.org");
|
|
| 2004 |
CREATE INDEX ON analytical_stem ("speciesBinomialWithMorphospecies__@VegCore__@vegpath.org");
|
|
| 2005 |
$$, repopulate); |
|
| 2006 |
$_$; |
|
| 2008 |
COMMENT ON FUNCTION analytical_stem_view_modify(view_query text, schema_anchor anyelement) IS ' |
|
| 2009 |
usage: |
|
| 2010 |
SELECT "analytical_stem_view_modify"($$ |
|
| 2011 |
SELECT __ |
|
| 2012 |
$$); |
|
| 2007 | 2013 |
|
| 2014 |
idempotent |
|
| 2015 |
'; |
|
| 2008 | 2016 |
|
| 2017 |
|
|
| 2009 | 2018 |
-- |
| 2010 | 2019 |
-- Name: datasource_publish(text, anyelement); Type: FUNCTION; Schema: public; Owner: - |
| 2011 | 2020 |
-- |
Also available in: Unified diff
bugfix: schemas/public_.sql: analytical_stem_view_modify(): updated to support being used directly instead of via a materialized table