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

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2017-03-15 05:09:16 -0700 (Wed, 15 Mar 2017)
New Revision: 9706

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
Allow dataset search for metadata_id and uuid

Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2017-03-09 12:59:56 UTC (rev 9705)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2017-03-15 12:09:16 UTC (rev 9706)
@@ -1744,3 +1744,38 @@
 
 ALTER TABLE search_wfs_view
   OWNER TO postgres;
+
+-- Function: f_collect_searchtext_dataset(integer)
+
+-- DROP FUNCTION f_collect_searchtext_dataset(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_searchtext_dataset(integer)
+  RETURNS text AS
+$BODY$
+DECLARE
+    p_dataset_id ALIAS FOR $1;
+    
+    r_keywords RECORD;
+    l_result TEXT;
+BEGIN
+    l_result := '';
+    l_result := l_result || (SELECT COALESCE(title, '') || ' ' || COALESCE(abstract, '') || ' ' || COALESCE(metadata_id::text, '')  || ' ' || COALESCE(uuid, '') FROM mb_metadata WHERE metadata_id = p_dataset_id);
+    FOR r_keywords IN SELECT DISTINCT keyword FROM
+        (SELECT keyword FROM mb_metadata_keyword L JOIN keyword K ON (K.keyword_id = L.fkey_keyword_id AND L.fkey_metadata_id = p_dataset_id)
+        ) AS __keywords__ LOOP
+        l_result := l_result || ' ' || COALESCE(r_keywords.keyword, '');
+    END LOOP;
+   l_result := UPPER(l_result);
+   l_result := replace(replace(replace(replace(replace(replace(replace(l_result,'Ä','AE'),'ß','SS'),'Ö','OE'),'Ü','UE'),'ä','AE'),'ü','UE'),'ö','OE');
+
+    RETURN l_result;
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100;
+ALTER FUNCTION f_collect_searchtext_dataset(integer)
+  OWNER TO postgres;
+
+
+
+