[Mapbender-commits] r9972 - trunk/mapbender/resources/db/pgsql/UTF-8/update

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2018-10-10 06:29:48 -0700 (Wed, 10 Oct 2018)
New Revision: 9972

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.8_pgsql_UTF-8.sql
Log:
add simple postgres function to alter ows urls

Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.8_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.8_pgsql_UTF-8.sql	2018-10-09 06:59:56 UTC (rev 9971)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.8_pgsql_UTF-8.sql	2018-10-10 13:29:48 UTC (rev 9972)
@@ -98,3 +98,30 @@
 INSERT INTO gui_mb_group (fkey_gui_id, fkey_mb_group_id) VALUES ('service_container1_free', 22);
 */
 
+-- Function: f_simple_alter_service_operation_url(character varying, character varying, character varying)
+
+-- DROP FUNCTION f_simple_alter_service_operation_url(character varying, character varying, character varying);
+
+CREATE OR REPLACE FUNCTION f_simple_alter_service_operation_url(
+    s_service_type character varying,
+    s_old_service_operation_url character varying,
+    s_new_service_operation_url character varying)
+  RETURNS text AS
+$BODY$DECLARE
+	s_service_type ALIAS FOR $1;
+	s_old_service_operation_url ALIAS FOR $2;
+	s_new_service_operation_url ALIAS FOR $3;
+	
+  -- s_service_type is 'wms' or 'wfs'
+  -- select f_simple_alter_service_operation_url('wms', 'http://1', 'http://2');
+
+BEGIN
+
+IF s_service_type='wms' THEN 
+     update wms set wms_upload_url = replace(wms_upload_url,s_old_service_operation_url,s_new_service_operation_url), wms_getcapabilities = replace(wms_getcapabilities,s_old_service_operation_url,s_new_service_operation_url), wms_getlegendurl = replace(wms_getlegendurl,s_old_service_operation_url,s_new_service_operation_url), wms_getmap = replace(wms_getmap,s_old_service_operation_url,s_new_service_operation_url), wms_getfeatureinfo = replace(wms_getfeatureinfo,s_old_service_operation_url,s_new_service_operation_url) where wms_upload_url like (s_old_service_operation_url || '%');
+ELSIF s_service_type='wfs' THEN 
+     update wfs set wfs_upload_url = replace(wfs_upload_url, s_old_service_operation_url, s_new_service_operation_url), wfs_getcapabilities = replace(wfs_getcapabilities, s_old_service_operation_url, s_new_service_operation_url), wfs_getfeature = replace(wfs_getfeature, s_old_service_operation_url, s_new_service_operation_url), wfs_describefeaturetype = replace(wfs_describefeaturetype, s_old_service_operation_url, s_new_service_operation_url), wfs_transaction = replace(wfs_transaction, s_old_service_operation_url, s_new_service_operation_url) where wfs_upload_url like (s_old_service_operation_url || '%');
+END IF;
+
+RETURN 'urls exchanged';
+