root/sql/sprocs/stratcon.metric_name_summary_tsvector.sql

Revision 544a1ea5a8955bbe0932db12162b6fb260cd89e9, 2.1 kB (checked in by Denish Patel <denish@omniti.com>, 5 years ago)

Changed variable name to match with column name to avoid further conusion for ts_search_all vs fts_data

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