londiste.drop_obsolete_partitions: added support for different partitioning periods

1 files changed, 23 insertions, 3 deletions

diff --git a/sql/londiste/functions/londiste.drop_obsolete_partitions.sql b/sql/londiste/functions/londiste.drop_obsolete_partitions.sql
index 771f1f34..56e00036 100644
--- a/sql/londiste/functions/londiste.drop_obsolete_partitions.sql
+++ b/sql/londiste/functions/londiste.drop_obsolete_partitions.sql
@@ -2,7 +2,8 @@
 create or replace function londiste.drop_obsolete_partitions
 (
   in i_parent_table text,
-  in i_retention_period interval
+  in i_retention_period interval,
+  in i_part_method text
 )
   returns setof text
 as $$
@@ -14,6 +15,7 @@ as $$
 --  Parameters:
 --    i_parent_table      Master table from which partitions are inherited
 --    i_retention_period  How long to keep partitions around
+--    i_part_method       One of: year, month, day, hour
 --
 --  Returns:
 --    Names of partitions dropped
@@ -22,13 +24,31 @@ declare
   _schema text not null := lower (split_part (i_parent_table, '.', 1));
   _table  text not null := lower (split_part (i_parent_table, '.', 2));
   _part   text;
+  _expr   text;
+  _dfmt   text;
 begin
+  if i_part_method = 'year' then
+    _expr := '_[0-9]{4}';
+    _dfmt := '_YYYY';
+  elsif i_part_method = 'month' then
+    _expr := '_[0-9]{4}_[0-9]{2}';
+    _dfmt := '_YYYY_MM';
+  elsif i_part_method = 'day' then
+    _expr := '_[0-9]{4}_[0-9]{2}_[0-9]{2}';
+    _dfmt := '_YYYY_MM_DD';
+  elsif i_part_method = 'hour' then
+    _expr := '_[0-9]{4}_[0-9]{2}_[0-9]{2}_[0-9]{2}';
+    _dfmt := '_YYYY_MM_DD_HH24';
+  else
+    raise exception 'i_part_method not supported: %', i_part_method;
+  end if;
+
   for _part in
     select quote_ident (t.schemaname) ||'.'|| quote_ident (t.tablename)
       from pg_catalog.pg_tables t
      where t.schemaname = _schema
-       and t.tablename ~ ('^'|| _table ||'_[0-9]{4}_[0-9]{2}_[0-9]{2}$')
-       and t.tablename < _table || to_char (now() - i_retention_period, '_YYYY_MM_DD')
+       and t.tablename ~ ('^'|| _table || _expr ||'$')
+       and t.tablename < _table || to_char (now() - i_retention_period, _dfmt)
   loop
     execute 'drop table '|| _part;
     return next _part;