Mercurial > gemma
view 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 source
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 CHECK (to_regclass(schema || '.' || name) IS NOT NULL OR view_def IS NOT NULL);