Mercurial > gemma
view schema/updates/1300/01.add_view_def.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
parents | ae840f9eb4c8 |
children |
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 srid int REFERENCES spatial_ref_sys, ADD CHECK (to_regclass(schema || '.' || name) IS NOT NULL OR view_def IS NOT NULL);