-------------------------------------------------- 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'