root/sql/sprocs/stratcon.metric_name_summary_compile_fts_data.sql

Revision 4a0968d07bb3e70d2212cb2ef9d79b6b1bc02b49, 2.0 kB (checked in by Robert Treat <robert@omniti.com>, 4 years ago)

and yes, renaming this

  • Property mode set to 100644
Line 
1 set search_path = noit,pg_catalog;
2
3 CREATE OR REPLACE FUNCTION stratcon.metric_name_summary_compile_fts_data
4 (in_sid integer, in_metric_name text, in_metric_type text)
5 RETURNS tsvector AS
6 $BODY$
7 DECLARE
8     ref_sid integer;
9     ref_module text;
10     ref_name text;
11     ref_target text;
12     ref_tags text;
13     ref_ctags text;
14     ref_hostname text;
15     ref_metric_name text;
16     ref_alias text;
17     v_fts_data tsvector;
18 BEGIN
19     SELECT sid, module, name, target FROM check_archive WHERE sid=in_sid INTO ref_sid,ref_module,ref_name,ref_target;
20     IF NOT FOUND THEN
21         RETURN NULL;
22     END IF;
23
24     SELECT COALESCE(array_to_string(tags_array, ' '), ' ') FROM metric_tag WHERE sid=in_sid and metric_name=in_metric_name INTO ref_tags;
25     IF NOT FOUND THEN
26         ref_tags := ' ';
27     END IF;
28
29     SELECT COALESCE(array_to_string(tags_array, ' '), ' ') FROM check_tag WHERE sid=in_sid INTO ref_ctags;
30     IF NOT FOUND THEN
31         ref_ctags := ' ';
32     END IF;
33
34     SELECT value FROM metric_text_currently mt JOIN check_archive s USING(sid)
35         WHERE module='dns' AND s.name='in-addr.arpa' AND target = ref_target INTO ref_hostname;
36      
37     SELECT mt.value FROM metric_text_currently mt JOIN check_archive s USING(sid)
38         WHERE s.module='snmp' AND mt.name='alias' AND s.sid=in_sid INTO ref_alias;
39
40     ref_hostname := coalesce(replace(ref_hostname, '.', ' '), ' ');
41     ref_metric_name := regexp_replace(in_metric_name, E'[_\`/.\\134]', ' ', 'g');
42     ref_alias := coalesce(regexp_replace(ref_alias, E'[_\`/.\\134]', ' ', 'g'), ' ');
43
44     v_fts_data := to_tsvector(ref_metric_name || ' ' ||
45                                 ref_module || ' ' ||
46                                 ref_name || ' ' ||
47                                 ref_target || ' ' ||
48                                 ref_hostname || ' ' ||
49                                 ref_alias || ' ' ||
50                                 ref_tags || ' ' ||
51                                 ref_ctags);
52     RETURN v_fts_data;
53 END
54 $BODY$
55 LANGUAGE 'plpgsql'
56 SECURITY DEFINER;
57  
Note: See TracBrowser for help on using the browser.