From 543f54bb4e8742489f567467f906f7d92e4eac5e Mon Sep 17 00:00:00 2001
From: martinko <gamato@users.sf.net>
Date: Tue, 19 Feb 2013 19:40:20 +0100
Subject: [PATCH] londiste: imported drop_obsolete_partitions(2) function

---
 .../londiste.drop_obsolete_partitions.sql     | 39 +++++++++++++++++++
 1 file changed, 39 insertions(+)
 create mode 100644 sql/londiste/functions/londiste.drop_obsolete_partitions.sql

diff --git a/sql/londiste/functions/londiste.drop_obsolete_partitions.sql b/sql/londiste/functions/londiste.drop_obsolete_partitions.sql
new file mode 100644
index 00000000..a5b7627d
--- /dev/null
+++ b/sql/londiste/functions/londiste.drop_obsolete_partitions.sql
@@ -0,0 +1,39 @@
+
+CREATE OR REPLACE FUNCTION londiste.drop_obsolete_partitions
+(
+  IN i_parent_table text,
+  IN i_retention_period interval
+)
+  RETURNS SETOF text
+AS $$
+-------------------------------------------------------------------------------
+--  Function: londiste.drop_obsolete_partitions(2)
+--
+--    Drop obsolete partitions of partition-by-date parent table.
+--
+--  Parameters:
+--    i_parent_table      Master table from which partitions are inherited
+--    i_retention_period  How long to keep partitions around
+--
+--  Returns:
+--    Names of partitions dropped
+-------------------------------------------------------------------------------
+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;
+BEGIN
+  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')
+  LOOP
+    EXECUTE 'DROP TABLE '|| _part;
+    RETURN NEXT _part;
+  END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+-- vim:et:sw=2:ts=2:nowrap:
-- 
2.39.5