Mn6RYzr8

1-- Function: chat_info_table_partition_function()
2
3-- DROP FUNCTION chat_info_table_partition_function();
4
5CREATE OR REPLACE FUNCTION chat_info_table_partition_function()
6  RETURNS trigger AS
7$BODY$
8DECLARE
9	_tablename text;
10	_enddate text;
11BEGIN
12	--_tablename := 'chat_'||NEW.chat_time::DATE;   --- 按天分表
13	--_tablename := 'chat_'||NEW.chat_time::DATE;   --- 按周分表
14  /*
15    根据用户user_id 分表
16  */
17  _tablename := 'tbChatInfor_'||(cast ((('x'||'24807158d3b14ac0b0d82a9a5a9e8f3e')::bit(64)::BIGINT)%100 as char(2)));
18  -- 	_tablename := 'tbChatInfor_'||to_char(NEW."Chat_Time", 'YYYY-MM'); --- 按月分表
19	-- Check if the partition needed for the current record exists
20	PERFORM 1 FROM  pg_catalog.pg_class c
21	JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
22	WHERE  c.relkind = 'r'
23	AND    c.relname = _tablename;
24
25	IF NOT FOUND THEN
26		--_enddate:= NEW.chat_time::DATE + INTERVAL '1 day'; --- 按天分表
27		-- _enddate:= NEW.chat_time::DATE + INTERVAL '1 week'; --- 按周分表
28		_enddate:= NEW."Chat_Time"::DATE + INTERVAL '1 month'; --- 按月分表
29		EXECUTE 'CREATE TABLE ' || quote_ident(_tablename) || '( CHECK ( "Chat_Time" >= '''|| NEW."Chat_Time"::DATE ||'''
30			AND "Chat_Time" <'''|| _enddate ||''')) INHERITS ("tbChatInfor")';
31
32		-- Table permissions are not inherited from the parent.
33		-- If permissions change on the master be sure to change them on the child also.
34		EXECUTE 'ALTER TABLE ' || quote_ident(_tablename) || ' OWNER TO admin';
35		EXECUTE 'GRANT ALL ON TABLE ' || quote_ident(_tablename) || ' TO admin';
36
37		-- Indexes are defined per child, so we assign a default index that uses the partition columns
38		EXECUTE 'CREATE INDEX ' || quote_ident(_tablename||'_uid_idx') || ' ON ' || quote_ident(_tablename) || ' USING btree ("Uid","IsRead")';
39		EXECUTE 'CREATE INDEX ' || quote_ident(_tablename||'_fid_idx') || ' ON ' || quote_ident(_tablename) || ' USING btree ("Fid","IsRead")';
40	END IF;
41
42	-- Insert the current record into the correct partition, which we are sure will now exist.
43	EXECUTE 'INSERT INTO ' || quote_ident(_tablename) || ' VALUES ($1.*)' USING NEW;
44	RETURN NULL;
45END;
46$BODY$
47  LANGUAGE plpgsql VOLATILE
48  COST 100;
49ALTER FUNCTION chat_info_table_partition_function()
50  OWNER TO postgres;