Mercurial > gemma
diff schema/updates/1112/01.add_view_def.sql @ 4605:7650d2431f86 geoserver_sql_views
Separate schema and table name
Since a GeoServer data source is bound to a specific database schema,
names have to be given without schema qualification. Since we cannot
use regclass for names of SQL views, the name is now a varchar. This
way it's more straightforward to store schema and name separately.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 05 Sep 2019 11:33:01 +0200 |
parents | ca8d18f794f3 |
children | b5aa1eb83bb0 |
line wrap: on
line diff
--- a/schema/updates/1112/01.add_view_def.sql Tue Sep 03 18:01:10 2019 +0200 +++ b/schema/updates/1112/01.add_view_def.sql Thu Sep 05 11:33:01 2019 +0200 @@ -13,6 +13,15 @@ STRICT; ALTER TABLE sys_admin.published_services - ALTER name TYPE varchar, + 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 CHECK (to_regclass(name) IS NOT NULL OR view_def IS NOT NULL); + ADD CHECK (to_regclass(schema || '.' || name) IS NOT NULL + OR view_def IS NOT NULL);