root/sql/sprocs/stratcon.archive_part_maint.sql

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

fix creation bug cross-schema

  • 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         RAISE INFO 'set permissions on partition %', v_table_name;
90
91         FOR v_rec in
92             select tgname, tgtype, nspname, proname
93               from pg_class as c join pg_trigger as t on(c.oid = t.tgrelid)
94               join pg_proc as p on(t.tgfoid = p.oid)
95               join pg_namespace as n on(p.pronamespace = n.oid)
96              where relname = v_parent_table
97                and relnamespace in (select oid from pg_namespace
98                                      where nspname = v_schema_name)
99                and proname <> 'parent_empty' LOOP
100           RAISE INFO 'creating trigger % on partition %', v_rec.tgname, v_table_name;
101           v_sql := 'CREATE TRIGGER ' || v_rec.tgname || '';
102           IF 0 != (v_rec.tgtype & 2) THEN
103             v_sql := v_sql || ' BEFORE ';
104           ELSE
105             v_sql := v_sql || ' AFTER ';
106           END IF;
107           IF 0 != (v_rec.tgtype & 4) THEN
108             v_sql := v_sql || ' INSERT ';
109           END IF;
110           IF 0 != (v_rec.tgtype & 8) THEN
111             IF 0 != (v_rec.tgtype & 4) THEN
112               v_sql := v_sql || ' OR ';
113             END IF;
114             v_sql := v_sql || ' DELETE ';
115           END IF;
116           IF 0 != (v_rec.tgtype & 16) THEN
117             IF 0 != (v_rec.tgtype & 12) THEN
118               v_sql := v_sql || ' OR ';
119             END IF;
120             v_sql := v_sql || ' UPDATE ';
121           END IF;
122           v_sql := v_sql || ' ON ' || v_schema_name || '.' || v_table_name;
123           IF 0 != (v_rec.tgtype & 1) THEN
124             v_sql := v_sql || ' FOR EACH ROW ';
125           ELSE
126             v_sql := v_sql || ' FOR EACH STATEMENT ';
127           END IF;
128           v_sql := v_sql || ' EXECUTE PROCEDURE ' || v_rec.nspname || '.' || v_rec.proname || '()';
129           execute v_sql;
130         END LOOP;
131
132         v_next_part := (v_next_part + ('1 '||in_units)::interval)::date;
133     END LOOP;
134 END
135 $$ LANGUAGE plpgsql;
136
Note: See TracBrowser for help on using the browser.