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

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2013-08-22 06:33:25 -0700 (Thu, 22 Aug 2013)
New Revision: 8689

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.3_to_2.7.4_pgsql_UTF-8.sql
Log:
Update postgres functions for collecting categories because now categories relations may be multiple.

Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.3_to_2.7.4_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.3_to_2.7.4_pgsql_UTF-8.sql	2013-08-22 05:56:12 UTC (rev 8688)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.3_to_2.7.4_pgsql_UTF-8.sql	2013-08-22 13:33:25 UTC (rev 8689)
@@ -388,3 +388,167 @@
       REFERENCES mb_metadata (metadata_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE;
 
+-- Function: f_collect_custom_cat_layer(integer)
+
+-- DROP FUNCTION f_collect_custom_cat_layer(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_custom_cat_layer(integer)
+  RETURNS text AS
+$BODY$DECLARE
+  i_layer_id ALIAS FOR $1;
+  custom_cat_string  TEXT;
+  custom_cat_record  RECORD;
+
+BEGIN
+custom_cat_string := '';
+
+FOR custom_cat_record IN SELECT DISTINCT layer_custom_category.fkey_custom_category_id from layer_custom_category WHERE layer_custom_category.fkey_layer_id=$1  LOOP
+custom_cat_string := custom_cat_string || '{' ||custom_cat_record.fkey_custom_category_id || '}';
+END LOOP ;
+  
+RETURN custom_cat_string;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_collect_custom_cat_layer(integer)
+  OWNER TO postgres;
+
+-- Function: f_collect_custom_cat_wfs_featuretype(integer)
+
+-- DROP FUNCTION f_collect_custom_cat_wfs_featuretype(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_custom_cat_wfs_featuretype(integer)
+  RETURNS text AS
+$BODY$DECLARE
+  i_featuretype_id ALIAS FOR $1;
+  custom_cat_string  TEXT;
+  custom_cat_record  RECORD;
+
+BEGIN
+custom_cat_string := '';
+
+FOR custom_cat_record IN SELECT DISTINCT wfs_featuretype_custom_category.fkey_custom_category_id from wfs_featuretype_custom_category WHERE wfs_featuretype_custom_category.fkey_featuretype_id=$1  LOOP
+custom_cat_string := custom_cat_string || '{' ||custom_cat_record.fkey_custom_category_id || '}';
+END LOOP ;
+  
+RETURN custom_cat_string;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_collect_custom_cat_wfs_featuretype(integer)
+  OWNER TO postgres;
+
+-- Function: f_collect_inspire_cat_layer(integer)
+
+-- DROP FUNCTION f_collect_inspire_cat_layer(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_inspire_cat_layer(integer)
+  RETURNS text AS
+$BODY$DECLARE
+  i_layer_id ALIAS FOR $1;
+  inspire_cat_string  TEXT;
+  inspire_cat_record  RECORD;
+
+BEGIN
+inspire_cat_string := '';
+
+FOR inspire_cat_record IN SELECT DISTINCT layer_inspire_category.fkey_inspire_category_id from layer_inspire_category WHERE layer_inspire_category.fkey_layer_id=$1  LOOP
+inspire_cat_string := inspire_cat_string || '{' ||inspire_cat_record.fkey_inspire_category_id || '}';
+END LOOP ;
+  
+RETURN inspire_cat_string;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_collect_inspire_cat_layer(integer)
+  OWNER TO postgres;
+
+-- Function: f_collect_inspire_cat_wfs_featuretype(integer)
+
+-- DROP FUNCTION f_collect_inspire_cat_wfs_featuretype(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_inspire_cat_wfs_featuretype(integer)
+  RETURNS text AS
+$BODY$DECLARE
+  i_wfs_featuretype_id ALIAS FOR $1;
+  inspire_cat_string  TEXT;
+  inspire_cat_record  RECORD;
+
+BEGIN
+inspire_cat_string := '';
+
+FOR inspire_cat_record IN SELECT DISTINCT wfs_featuretype_inspire_category.fkey_inspire_category_id from wfs_featuretype_inspire_category WHERE wfs_featuretype_inspire_category.fkey_featuretype_id=$1  LOOP
+inspire_cat_string := inspire_cat_string || '{' ||inspire_cat_record.fkey_inspire_category_id || '}';
+END LOOP ;
+  
+RETURN inspire_cat_string;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_collect_inspire_cat_wfs_featuretype(integer)
+  OWNER TO postgres;
+
+-- Function: f_collect_topic_cat_layer(integer)
+
+-- DROP FUNCTION f_collect_topic_cat_layer(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_topic_cat_layer(integer)
+  RETURNS text AS
+$BODY$DECLARE
+  i_layer_id ALIAS FOR $1;
+  topic_cat_string  TEXT;
+  topic_cat_record  RECORD;
+
+BEGIN
+topic_cat_string := '';
+
+FOR topic_cat_record IN SELECT DISTINCT layer_md_topic_category.fkey_md_topic_category_id from layer_md_topic_category WHERE layer_md_topic_category.fkey_layer_id=$1  LOOP
+topic_cat_string := topic_cat_string || '{' ||topic_cat_record.fkey_md_topic_category_id || '}';
+END LOOP ;
+  
+RETURN topic_cat_string;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_collect_topic_cat_layer(integer)
+  OWNER TO postgres;
+
+-- Function: f_collect_topic_cat_wfs_featuretype(integer)
+
+-- DROP FUNCTION f_collect_topic_cat_wfs_featuretype(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_topic_cat_wfs_featuretype(integer)
+  RETURNS text AS
+$BODY$DECLARE
+  i_featuretype_id ALIAS FOR $1;
+  topic_cat_string  TEXT;
+  topic_cat_record  RECORD;
+
+BEGIN
+topic_cat_string := '';
+
+FOR topic_cat_record IN SELECT DISTINCT wfs_featuretype_md_topic_category.fkey_md_topic_category_id from wfs_featuretype_md_topic_category WHERE wfs_featuretype_md_topic_category.fkey_featuretype_id=$1  LOOP
+topic_cat_string := topic_cat_string || '{' ||topic_cat_record.fkey_md_topic_category_id || '}';
+END LOOP ;
+  
+RETURN topic_cat_string;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_collect_topic_cat_wfs_featuretype(integer)
+  OWNER TO postgres;
+
+
+