Mercurial > gemma

diff schema/gemma.sql @ 4605:7650d2431f86 geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 d24e951206ca
children b5aa1eb83bb0
line wrap: on
line diff
--- a/schema/gemma.sql	Tue Sep 03 18:01:10 2019 +0200
+++ b/schema/gemma.sql	Thu Sep 05 11:33:01 2019 +0200
@@ -292,14 +292,17 @@
     )
 
     CREATE TABLE published_services (
-        name varchar PRIMARY KEY,
+        schema varchar CHECK(to_regnamespace(schema) IS NOT NULL),
+        name varchar,
+        PRIMARY KEY (schema, name),
         -- SQL statement used for an SQL view in GeoServer:
         view_def text CHECK (is_valid_from_item(view_def)),
         style xml CHECK(style IS DOCUMENT),
         as_wms boolean NOT NULL DEFAULT TRUE,
         as_wfs boolean NOT NULL DEFAULT TRUE,
-        -- Either give a valid relation name or a SQL statement:
-        CHECK (to_regclass(name) IS NOT NULL OR view_def IS NOT NULL)
+        -- Either give a valid relation or a SQL statement:
+        CHECK (to_regclass(schema || '.' || name) IS NOT NULL
+            OR view_def IS NOT NULL)
     )
 ;