Mercurial > gemma
changeset 4613:0c5829ff4dea geoserver_sql_views
Advance schema version number
1112 used in default branch, meanwhile.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 09 Sep 2019 17:16:41 +0200 |
| parents | a5dccbc5920d |
| children | 970e90d3d5eb |
| files | schema/updates/1112/01.add_view_def.sql schema/updates/1113/01.add_view_def.sql schema/version.sql |
| diffstat | 3 files changed, 29 insertions(+), 29 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/updates/1112/01.add_view_def.sql Fri Sep 06 16:26:34 2019 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,28 +0,0 @@ -CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean -AS $$ -BEGIN - EXECUTE format('SELECT * FROM (%s) AS test', stmt); - RETURN true; -EXCEPTION - WHEN OTHERS THEN - RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE; - RETURN false; -END -$$ - LANGUAGE plpgsql - STRICT; - -ALTER TABLE sys_admin.published_services - ADD schema varchar; -UPDATE sys_admin.published_services - SET schema = substring(CAST(name AS varchar) from '(.*)\.'); -ALTER TABLE sys_admin.published_services - ADD CHECK(to_regnamespace(schema) IS NOT NULL), - ALTER name TYPE varchar - USING substring(CAST(name AS varchar) from '\.(.*)'), - DROP CONSTRAINT published_services_pkey, - ADD PRIMARY KEY (schema, name), - ADD view_def text CHECK (is_valid_from_item(view_def)), - ADD srid int REFERENCES spatial_ref_sys, - ADD CHECK (to_regclass(schema || '.' || name) IS NOT NULL - OR view_def IS NOT NULL);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1113/01.add_view_def.sql Mon Sep 09 17:16:41 2019 +0200 @@ -0,0 +1,28 @@ +CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean +AS $$ +BEGIN + EXECUTE format('SELECT * FROM (%s) AS test', stmt); + RETURN true; +EXCEPTION + WHEN OTHERS THEN + RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE; + RETURN false; +END +$$ + LANGUAGE plpgsql + STRICT; + +ALTER TABLE sys_admin.published_services + ADD schema varchar; +UPDATE sys_admin.published_services + SET schema = substring(CAST(name AS varchar) from '(.*)\.'); +ALTER TABLE sys_admin.published_services + ADD CHECK(to_regnamespace(schema) IS NOT NULL), + ALTER name TYPE varchar + USING substring(CAST(name AS varchar) from '\.(.*)'), + DROP CONSTRAINT published_services_pkey, + ADD PRIMARY KEY (schema, name), + ADD view_def text CHECK (is_valid_from_item(view_def)), + ADD srid int REFERENCES spatial_ref_sys, + ADD CHECK (to_regclass(schema || '.' || name) IS NOT NULL + OR view_def IS NOT NULL);