mJLbNjdm

1CREATE OR REPLACE FUNCTION chat_insert_function()
2RETURNS TRIGGER AS $$
3DECLARE
4	partition_date TEXT;
5	partition_name TEXT;
6	start_of_month TEXT;
7	end_of_next_month TEXT;
8BEGIN
9	partition_date := to_char(NEW.created_at,'YYYY_MM');
10 	partition_name := 'chat_' || partition_date;
11	start_of_month := to_char((NEW.created_at),'YYYY-MM') || '-01';
12	end_of_next_month := to_char((NEW.created_at + interval '1 month'),'YYYY-MM') || '-01';
13IF NOT EXISTS
14	(SELECT 1
15   	 FROM   information_schema.tables 
16   	 WHERE  table_name = partition_name) 
17THEN
18	RAISE NOTICE 'A partition has been created %', partition_name;
19	EXECUTE format(E'CREATE TABLE %I (CHECK ( date_trunc(\'day\', created_at) >= ''%s'' AND date_trunc(\'day\', created_at) < ''%s'')) INHERITS (public.chat_master)', partition_name, start_of_month,end_of_next_month);
20	-- EXECUTE format('GRANT SELECT ON TABLE %I TO readonly', partition_name); -- use this if you use role based permission
21END IF;
22EXECUTE format('INSERT INTO %I (program_id, user_id, dialogue, created_at) VALUES($1,$2,$3,$4)', partition_name) using NEW.program_id, NEW.user_id, NEW.dialogue, NEW.created_at;
23RETURN NULL;
24END
25$$
26LANGUAGE plpgsql;