Mercurial > gemma

annotate schema/updates/1300/01.add_view_def.sql @ 5584:7ed9e32706d0 surveysperbottleneckid

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged delault
author Sascha Wilde <wilde@sha-bang.de>
date Fri, 01 Apr 2022 16:47:53 +0200
parents ae840f9eb4c8
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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);