Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -------------------------------------------------- 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'
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement