按月分区触发器函数编写，自动创建分区表

概述CREATE OR REPLACE FUNCTION trigger_function_test_yum()   RETURNS trigger AS $BODY$ DECLARE str_time varchar; str_sub_tablename varchar; str_sql_cmd varchar; BEGIN      IF TG_OP <> 'INSERT' OR TG_TABLE CREATE OR REPLACE FUNCTION trigger_function_test_yum() RETURNS trigger AS $BODY$ DECLARE str_time varchar; str_sub_tablename varchar; str_sql_cmd varchar; BEGIN IF TG_OP <> 'INSERT' OR TG_table_name <>'test_yum' OR TG_WHEN <> 'BEFORE' THEN RETURN NulL; END IF; --Generate table name str_time = date_part('year',NEW.A)::varchar || '_' || CASE WHEN date_part('month',NEW.A) <10 THEN '0' ELSE '' END ||date_part('month',NEW.A)::varchar; str_sub_tablename = 'test_yum_' || str_time; --Check if table not created select * from pg_tables where schemaname = 'public' and tablename=str_sub_tablename into str_sql_cmd; IF NOT FOUND THEN --Create table Cmd str_sql_cmd = 'CREATE table '||str_sub_tablename|| ' (CONSTRAINT chk_'|| str_sub_tablename|| ' CHECK(date_part(''year''::text,A) = '|| date_part('year',NEW.A)::varchar|| ' AND date_part(''month''::text,A) = '|| date_part('month',NEW.A)::varchar|| ' )) inheritS (test_yum) tableSPACE ts_yum_global;'; EXECUTE str_sql_cmd; END IF; --insert Data str_sql_cmd = 'INSERT INTO '||str_sub_tablename||' VALUES($1,$2);'; EXECUTE str_sql_cmd USING NEW.A,NEW.B; --return null because main table does not really contain data RETURN NulL; END; $BODY$ LANGUAGE plpgsql VolATILE COST 100; ALTER FUNCTION trigger_function_test_yum() OWNER TO postgres; 总结

以上是内存溢出为你收集整理的按月分区触发器函数编写，自动创建分区表全部内容，希望文章能够帮你解决按月分区触发器函数编写，自动创建分区表所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。