Mercurial > gemma
annotate schema/updates/1300/01.add_view_def.sql @ 4998:bb2123358bd8 wmst-config
Configure fairway marks layers with time support
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 09 Mar 2020 12:19:06 +0100 |
parents | ae840f9eb4c8 |
children |
rev | line source |
---|---|
4604
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 AS $$ |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
3 BEGIN |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 EXECUTE format('SELECT * FROM (%s) AS test', stmt); |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 RETURN true; |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 EXCEPTION |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 WHEN OTHERS THEN |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE; |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 RETURN false; |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 END |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 $$ |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 LANGUAGE plpgsql |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 STRICT; |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 |
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 ALTER TABLE sys_admin.published_services |
4605
7650d2431f86
Separate schema and table name
Tom Gottfried <tom@intevation.de>
parents:
4604
diff
changeset
|
16 ADD schema varchar; |
7650d2431f86
Separate schema and table name
Tom Gottfried <tom@intevation.de>
parents:
4604
diff
changeset
|
17 UPDATE sys_admin.published_services |
7650d2431f86
Separate schema and table name
Tom Gottfried <tom@intevation.de>
parents:
4604
diff
changeset
|
18 SET schema = substring(CAST(name AS varchar) from '(.*)\.'); |
7650d2431f86
Separate schema and table name
Tom Gottfried <tom@intevation.de>
parents:
4604
diff
changeset
|
19 ALTER TABLE sys_admin.published_services |
7650d2431f86
Separate schema and table name
Tom Gottfried <tom@intevation.de>
parents:
4604
diff
changeset
|
20 ADD CHECK(to_regnamespace(schema) IS NOT NULL), |
7650d2431f86
Separate schema and table name
Tom Gottfried <tom@intevation.de>
parents:
4604
diff
changeset
|
21 ALTER name TYPE varchar |
7650d2431f86
Separate schema and table name
Tom Gottfried <tom@intevation.de>
parents:
4604
diff
changeset
|
22 USING substring(CAST(name AS varchar) from '\.(.*)'), |
7650d2431f86
Separate schema and table name
Tom Gottfried <tom@intevation.de>
parents:
4604
diff
changeset
|
23 DROP CONSTRAINT published_services_pkey, |
7650d2431f86
Separate schema and table name
Tom Gottfried <tom@intevation.de>
parents:
4604
diff
changeset
|
24 ADD PRIMARY KEY (schema, name), |
4604
ca8d18f794f3
Add database update script to add SQL view definition field
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 ADD view_def text CHECK (is_valid_from_item(view_def)), |
4611
b5aa1eb83bb0
Add possibility to configure SRS for GeoServer SQL view
Tom Gottfried <tom@intevation.de>
parents:
4605
diff
changeset
|
26 ADD srid int REFERENCES spatial_ref_sys, |
4605
7650d2431f86
Separate schema and table name
Tom Gottfried <tom@intevation.de>
parents:
4604
diff
changeset
|
27 ADD CHECK (to_regclass(schema || '.' || name) IS NOT NULL |
7650d2431f86
Separate schema and table name
Tom Gottfried <tom@intevation.de>
parents:
4604
diff
changeset
|
28 OR view_def IS NOT NULL); |