root/sql/sprocs/prism.remove_tags.sql

Revision a27cd9b462a686609eff744d1e6553da5605090b, 1.9 kB (checked in by Depesz Lubaczewski <depesz@omniti.com>, 4 years ago)

prism procedures, and simple tool to split pg_dump -n prism -s output into many files

  • Property mode set to 100644
Line 
1 CREATE OR REPLACE FUNCTION prism.remove_tags(in_sid integer, in_metric_name text, in_tags text) RETURNS void
2     AS $$
3 DECLARE
4 v_tags_array text[];
5 p_sid integer;
6 p_tags_array text[];
7 new_tags_array text[];
8 i int;
9  BEGIN
10    v_tags_array:= string_to_array(in_tags,'');
11      SELECT sid,tags_array into p_sid ,p_tags_array
12       FROM stratcon.metric_tags
13       WHERE sid=in_sid AND metric_name=in_metric_name;
14      IF NOT FOUND THEN
15
16                RAISE EXCEPTION 'Metric tags does not found to be removed';
17
18      ELSE
19          FOR i IN array_lower(p_tags_array, 1)..array_upper(p_tags_array, 1) LOOP
20          IF NOT p_tags_array[i] =any(v_tags_array) THEN
21             new_tags_array = array_append(new_tags_array, p_tags_array[i]);
22           END IF;
23          END LOOP;
24
25            UPDATE  stratcon.metric_tags SET tags_array= new_tags_array WHERE sid=in_sid AND metric_name=in_metric_name;
26     END IF;
27   RETURN;
28 END
29 $$
30     LANGUAGE plpgsql;
31
32
33 CREATE OR REPLACE FUNCTION prism.remove_tags(in_sid integer, in_tags text) RETURNS void
34     AS $$
35 DECLARE
36 v_tags_array text[];
37 p_sid integer;
38 p_tags_array text[];
39 new_tags_array text[];
40 i int;
41  BEGIN
42    v_tags_array:= string_to_array(in_tags,'');
43      SELECT sid,tags_array into p_sid ,p_tags_array
44       FROM stratcon.check_tags
45       WHERE sid=in_sid;
46      IF NOT FOUND THEN
47
48                RAISE EXCEPTION 'Check tags does not found to be removed';
49
50      ELSE
51          FOR i IN array_lower(p_tags_array, 1)..array_upper(p_tags_array, 1) LOOP
52          IF NOT p_tags_array[i] =any(v_tags_array) THEN
53             new_tags_array = array_append(new_tags_array, p_tags_array[i]);
54           END IF;
55          END LOOP;
56
57            UPDATE  stratcon.check_tags SET tags_array= new_tags_array WHERE sid=in_sid;
58     END IF;
59   RETURN;
60 END
61 $$
62     LANGUAGE plpgsql;
63
64
65 ALTER FUNCTION prism.remove_tags(in_sid integer, in_metric_name text, in_tags text) OWNER TO reconnoiter;
66
67
68 ALTER FUNCTION prism.remove_tags(in_sid integer, in_tags text) OWNER TO reconnoiter;
Note: See TracBrowser for help on using the browser.