root/sql/sprocs/stratcon.archive_part_maint.sql

Revision b67d3e3528e327b7942ad1e179bf74c6b22ea0f5, 5.1 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 4 years ago)

limit to a single trigger

  • Property mode set to 100644
Line 
1 CREATE OR REPLACE FUNCTION stratcon.archive_part_maint(in_parent_table text, in_column text, in_units text, in_cnt integer) RETURNS void
2 AS $$
3 DECLARE
4     v_recent_part date;
5     v_schema_name text;
6     v_table_name text;
7     v_constraint_name text;
8     v_next_part date;
9     v_parent_table text;
10     v_rec record;
11     v_sql text;
12     v_has_perms boolean;
13 BEGIN
14     v_parent_table := substring(in_parent_table from E'\\.(.+)');
15     IF v_parent_table IS NULL THEN
16       v_parent_table := in_parent_table;
17     END IF;
18     v_schema_name := substring(in_parent_table from E'^([^.]+)');
19     IF v_schema_name IS NULL THEN
20       v_schema_name := 'stratcon';
21     END IF;
22
23     select d into v_recent_part from (
24         select overlay(
25                    overlay(
26                        substring(relname from E'_([0-9]{8})$')
27                        placing '-' from 7 for 0)
28                    placing '-' from 5 for 0)::date as d from pg_class
29          where oid in (select inhrelid
30                          from pg_inherits as i
31                          join pg_class as c on(i.inhparent = c.oid)
32                          join pg_namespace as n on (c.relnamespace = n.oid)
33                         where nspname = v_schema_name
34                           and relname = v_parent_table)) as x
35     order by d desc
36     limit 1;
37
38     IF v_recent_part IS NULL THEN
39         select (date_trunc(in_units, current_date) - ('1 '||in_units)::interval)::date
40           into v_recent_part;
41     END IF;
42
43     select date_trunc(in_units, v_recent_part + ('1 '||in_units)::interval)::date
44       into v_next_part;
45
46     LOOP
47         IF v_next_part > current_date + (in_cnt * ('1 '||in_units)::interval) THEN
48             EXIT;
49         END IF;
50         v_table_name := v_parent_table || '_' || extract(YEAR from v_next_part) ||
51                         lpad(extract(month from v_next_part)::text, 2, '0') ||
52                         lpad(extract(day from v_next_part)::text, 2, '0');
53         v_constraint_name := 'check_' || v_table_name;
54
55         execute 'CREATE TABLE ' || v_schema_name || '.' || v_table_name || '(' ||
56                 'CONSTRAINT ' || v_constraint_name ||
57                 ' CHECK (' || in_column || E' >= \'' || v_next_part::text || E' 00:00:00-00\'::timestamptz AND ' ||
58                 '        ' || in_column || E' < \'' || (v_next_part + ('1 '||in_units)::interval)::date::text || E' 00:00:00-00\'::timestamptz)' ||
59                 ') INHERITS (' || in_parent_table || ')';
60
61         RAISE INFO 'created partition %', v_table_name;
62
63         FOR v_rec in
64             select replace(indexdef, v_parent_table, v_table_name) as sql
65               from pg_indexes
66              where tablename = v_parent_table and schemaname = v_schema_name
67         LOOP
68           RAISE INFO 'recreated parent indexes on %', v_table_name;
69           execute v_rec.sql;
70         END LOOP;
71
72         -- no public access
73         select count(*) > 0 into v_has_perms
74           from information_schema.table_privileges
75          where table_schema=v_schema_name and table_name=v_parent_table;
76
77         IF v_has_perms THEN
78           execute 'REVOKE ALL ON ' || v_schema_name || '.' || v_table_name || ' FROM PUBLIC';
79         END IF;
80
81         FOR v_rec in
82             select 'GRANT ' || privilege_type || ' ON ' || v_schema_name || '.' || v_table_name ||
83                    ' TO ' || grantee as sql
84               from information_schema.table_privileges
85              where table_schema=v_schema_name and table_name=v_parent_table
86         LOOP
87           execute v_rec.sql;
88         END LOOP;
89
90         FOR v_rec in
91             select tgname, tgtype, nspname, proname
92               from pg_class as c join pg_trigger as t on(c.oid = t.tgrelid)
93               join pg_proc as p on(t.tgfoid = p.oid)
94               join pg_namespace as n on(p.pronamespace = n.oid)
95              where relname = v_parent_table and nspname = v_schema_name
96                and relnamespace = n.oid
97                and proname <> 'parent_empty' LOOP
98           v_sql := 'CREATE TRIGGER ' || v_rec.tgname || '';
99           IF 0 != (v_rec.tgtype & 2) THEN
100             v_sql := v_sql || ' BEFORE ';
101           ELSE
102             v_sql := v_sql || ' AFTER ';
103           END IF;
104           IF 0 != (v_rec.tgtype & 4) THEN
105             v_sql := v_sql || ' INSERT ';
106           END IF;
107           IF 0 != (v_rec.tgtype & 8) THEN
108             IF 0 != (v_rec.tgtype & 4) THEN
109               v_sql := v_sql || ' OR ';
110             END IF;
111             v_sql := v_sql || ' DELETE ';
112           END IF;
113           IF 0 != (v_rec.tgtype & 16) THEN
114             IF 0 != (v_rec.tgtype & 12) THEN
115               v_sql := v_sql || ' OR ';
116             END IF;
117             v_sql := v_sql || ' UPDATE ';
118           END IF;
119           v_sql := v_sql || ' ON ' || v_schema_name || '.' || v_table_name;
120           IF 0 != (v_rec.tgtype & 1) THEN
121             v_sql := v_sql || ' FOR EACH ROW ';
122           ELSE
123             v_sql := v_sql || ' FOR EACH STATEMENT ';
124           END IF;
125           v_sql := v_sql || ' EXECUTE PROCEDURE ' || v_rec.nspname || '.' || v_rec.proname || '()';
126           execute v_sql;
127         END LOOP;
128
129         v_next_part := (v_next_part + ('1 '||in_units)::interval)::date;
130     END LOOP;
131 END
132 $$ LANGUAGE plpgsql;
133
Note: See TracBrowser for help on using the browser.