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

发布时间：2020-12-13 17:16:33 所属栏目：百科来源：网络整理

导读：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 N

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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!