-------------------------------------------------- SCHEMA CREATION

CREATE SCHEMA pproblem AUTHORIZATION postgres;
  

-------------------------------------------------- HASH FUNCTION

CREATE OR REPLACE FUNCTION pproblem.hash(hashme uuid)
  RETURNS text AS
$BODY$ 

-- Hash Function
-- ----------------------------------------------------------------------
-- Returns:
--      X - Returns First Digit Of the MD5
-- ----------------------------------------------------------------------

	select substring(md5($1::text) for 1)

$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;


-------------------------------------------------- TABLE CREATION


CREATE TABLE pproblem.tablename (id uuid, mdate date);
CREATE TABLE pproblem.tablename_partition_0 ( CHECK ( pproblem.hash(id) = '0' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_1 ( CHECK ( pproblem.hash(id) = '1' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_2 ( CHECK ( pproblem.hash(id) = '2' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_3 ( CHECK ( pproblem.hash(id) = '3' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_4 ( CHECK ( pproblem.hash(id) = '4' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_5 ( CHECK ( pproblem.hash(id) = '5' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_6 ( CHECK ( pproblem.hash(id) = '6' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_7 ( CHECK ( pproblem.hash(id) = '7' ) ) INHERITS (pproblem.tablename); 
CREATE TABLE pproblem.tablename_partition_8 ( CHECK ( pproblem.hash(id) = '8' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_9 ( CHECK ( pproblem.hash(id) = '9' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_a ( CHECK ( pproblem.hash(id) = 'a' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_b ( CHECK ( pproblem.hash(id) = 'b' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_c ( CHECK ( pproblem.hash(id) = 'c' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_d ( CHECK ( pproblem.hash(id) = 'd' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_e ( CHECK ( pproblem.hash(id) = 'e' ) ) INHERITS (pproblem.tablename);
CREATE TABLE pproblem.tablename_partition_f ( CHECK ( pproblem.hash(id) = 'f' ) ) INHERITS (pproblem.tablename);


-------------------------------------------------- THE INSERT TRIGGER

-- Function: pproblem.insert_redirector()

-- DROP FUNCTION pproblem.insert_redirector();

CREATE OR REPLACE FUNCTION pproblem.insert_redirector()
  RETURNS TRIGGER AS
$BODY$

-- Helps Trigger Inserting Data In The Correct Table
-- ----------------------------------------------------------------------
--
--      Insert The Trigger
--
-- Returns:
--      Trigger
-- ----------------------------------------------------------------------
declare

    sms_id       uuid;
    sms_id_hash  text;
    
begin

    -- Render New UUID
    SELECT INTO sms_id uuid_generate_v4();

    -- Get Our Hash
    SELECT INTO sms_id_hash pproblem.hash(sms_id);
    
    RAISE NOTICE 'Calling id_hash(%)', sms_id_hash;
    RAISE NOTICE 'Calling sms_id(%)', sms_id;

    -- Now Point To Our Table
    execute 'INSERT INTO pproblem.tablename_partition_'|| sms_id_hash ||' (id,mdate) VALUES'
    ||' ('||quote_literal(sms_id)||','|| quote_literal(NEW.mdate::date) ||')';
    
    return NULL;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

-------------------------------------------------- HOOK THE INSERT TRIGGER
CREATE TRIGGER "InsertRedirector"
  BEFORE INSERT
  ON pproblem.tablename
  FOR EACH ROW
  EXECUTE PROCEDURE pproblem.insert_redirector(E'\\x');
  
  
--- SOME TEST DATA
INSERT INTO pproblem.tablename (mdate) VALUES ('2012-10-10');
INSERT INTO pproblem.tablename (mdate) VALUES ('2012-10-11');
INSERT INTO pproblem.tablename (mdate) VALUES ('2012-10-12');

-------------------------------------------------- HOOK THE DELETE TRIGGER

CREATE OR REPLACE FUNCTION pproblem.delete_me()
  RETURNS trigger AS
$BODY$

begin

    RAISE NOTICE 'HERE !!!';
    return OLD;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
  
CREATE TRIGGER "DeleteRedirector"
  BEFORE DELETE
  ON pproblem.tablename
  FOR EACH ROW
  EXECUTE PROCEDURE pproblem.delete_me(E'\\x');




--DELETE FROM pproblem.tablename WHERE ID  ='5b912a4d-9bb2-4982-a802-62d99cf78f88'