Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on Oct 29th, 2012  |  syntax: PL/SQL  |  size: 4.59 KB  |  views: 41  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1.  
  2.  
  3. -------------------------------------------------- SCHEMA CREATION
  4.  
  5. CREATE SCHEMA pproblem AUTHORIZATION postgres;
  6.  
  7.  
  8. -------------------------------------------------- HASH FUNCTION
  9.  
  10. CREATE OR REPLACE FUNCTION pproblem.hash(hashme uuid)
  11.   RETURNS text AS
  12. $BODY$
  13.  
  14. -- Hash Function
  15. -- ----------------------------------------------------------------------
  16. -- Returns:
  17. --      X - Returns First Digit Of the MD5
  18. -- ----------------------------------------------------------------------
  19.  
  20.         SELECT substring(md5($1::text) FOR 1)
  21.  
  22. $BODY$
  23.   LANGUAGE SQL IMMUTABLE STRICT
  24.   COST 100;
  25.  
  26.  
  27. -------------------------------------------------- TABLE CREATION
  28.  
  29.  
  30. CREATE TABLE pproblem.tablename (id uuid, mdate DATE);
  31. CREATE TABLE pproblem.tablename_partition_0 ( CHECK ( pproblem.hash(id) = '0' ) ) INHERITS (pproblem.tablename);
  32. CREATE TABLE pproblem.tablename_partition_1 ( CHECK ( pproblem.hash(id) = '1' ) ) INHERITS (pproblem.tablename);
  33. CREATE TABLE pproblem.tablename_partition_2 ( CHECK ( pproblem.hash(id) = '2' ) ) INHERITS (pproblem.tablename);
  34. CREATE TABLE pproblem.tablename_partition_3 ( CHECK ( pproblem.hash(id) = '3' ) ) INHERITS (pproblem.tablename);
  35. CREATE TABLE pproblem.tablename_partition_4 ( CHECK ( pproblem.hash(id) = '4' ) ) INHERITS (pproblem.tablename);
  36. CREATE TABLE pproblem.tablename_partition_5 ( CHECK ( pproblem.hash(id) = '5' ) ) INHERITS (pproblem.tablename);
  37. CREATE TABLE pproblem.tablename_partition_6 ( CHECK ( pproblem.hash(id) = '6' ) ) INHERITS (pproblem.tablename);
  38. CREATE TABLE pproblem.tablename_partition_7 ( CHECK ( pproblem.hash(id) = '7' ) ) INHERITS (pproblem.tablename);
  39. CREATE TABLE pproblem.tablename_partition_8 ( CHECK ( pproblem.hash(id) = '8' ) ) INHERITS (pproblem.tablename);
  40. CREATE TABLE pproblem.tablename_partition_9 ( CHECK ( pproblem.hash(id) = '9' ) ) INHERITS (pproblem.tablename);
  41. CREATE TABLE pproblem.tablename_partition_a ( CHECK ( pproblem.hash(id) = 'a' ) ) INHERITS (pproblem.tablename);
  42. CREATE TABLE pproblem.tablename_partition_b ( CHECK ( pproblem.hash(id) = 'b' ) ) INHERITS (pproblem.tablename);
  43. CREATE TABLE pproblem.tablename_partition_c ( CHECK ( pproblem.hash(id) = 'c' ) ) INHERITS (pproblem.tablename);
  44. CREATE TABLE pproblem.tablename_partition_d ( CHECK ( pproblem.hash(id) = 'd' ) ) INHERITS (pproblem.tablename);
  45. CREATE TABLE pproblem.tablename_partition_e ( CHECK ( pproblem.hash(id) = 'e' ) ) INHERITS (pproblem.tablename);
  46. CREATE TABLE pproblem.tablename_partition_f ( CHECK ( pproblem.hash(id) = 'f' ) ) INHERITS (pproblem.tablename);
  47.  
  48.  
  49. -------------------------------------------------- THE INSERT TRIGGER
  50.  
  51. -- Function: pproblem.insert_redirector()
  52.  
  53. -- DROP FUNCTION pproblem.insert_redirector();
  54.  
  55. CREATE OR REPLACE FUNCTION pproblem.insert_redirector()
  56.   RETURNS TRIGGER AS
  57. $BODY$
  58.  
  59. -- Helps Trigger Inserting Data In The Correct Table
  60. -- ----------------------------------------------------------------------
  61. --
  62. --      Insert The Trigger
  63. --
  64. -- Returns:
  65. --      Trigger
  66. -- ----------------------------------------------------------------------
  67. DECLARE
  68.  
  69.     sms_id       uuid;
  70.     sms_id_hash  text;
  71.    
  72. BEGIN
  73.  
  74.     -- Render New UUID
  75.     SELECT INTO sms_id uuid_generate_v4();
  76.  
  77.     -- Get Our Hash
  78.     SELECT INTO sms_id_hash pproblem.hash(sms_id);
  79.    
  80.     RAISE NOTICE 'Calling id_hash(%)', sms_id_hash;
  81.     RAISE NOTICE 'Calling sms_id(%)', sms_id;
  82.  
  83.     -- Now Point To Our Table
  84.     EXECUTE 'INSERT INTO pproblem.tablename_partition_'|| sms_id_hash ||' (id,mdate) VALUES'
  85.     ||' ('||quote_literal(sms_id)||','|| quote_literal(NEW.mdate::DATE) ||')';
  86.    
  87.     RETURN NULL;
  88.  
  89. END;
  90. $BODY$
  91.   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  92.   COST 100;
  93.  
  94. -------------------------------------------------- HOOK THE INSERT TRIGGER
  95. CREATE TRIGGER "InsertRedirector"
  96.   BEFORE INSERT
  97.   ON pproblem.tablename
  98.   FOR EACH ROW
  99.   EXECUTE PROCEDURE pproblem.insert_redirector(E'\\x');
  100.  
  101.  
  102. --- SOME TEST DATA
  103. INSERT INTO pproblem.tablename (mdate) VALUES ('2012-10-10');
  104. INSERT INTO pproblem.tablename (mdate) VALUES ('2012-10-11');
  105. INSERT INTO pproblem.tablename (mdate) VALUES ('2012-10-12');
  106.  
  107. -------------------------------------------------- HOOK THE DELETE TRIGGER
  108.  
  109. CREATE OR REPLACE FUNCTION pproblem.delete_me()
  110.   RETURNS TRIGGER AS
  111. $BODY$
  112.  
  113. BEGIN
  114.  
  115.     RAISE NOTICE 'HERE !!!';
  116.     RETURN OLD;
  117.  
  118. END;
  119. $BODY$
  120.   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  121.   COST 100;
  122.  
  123. CREATE TRIGGER "DeleteRedirector"
  124.   BEFORE DELETE
  125.   ON pproblem.tablename
  126.   FOR EACH ROW
  127.   EXECUTE PROCEDURE pproblem.delete_me(E'\\x');
  128.  
  129.  
  130.  
  131.  
  132. --DELETE FROM pproblem.tablename WHERE ID  ='5b912a4d-9bb2-4982-a802-62d99cf78f88'