- 论坛徽章:
- 0
|
回复 1# wzhuzhu
请通过以下过程:- CREATE SEQUENCE public.seq_bigtable_id;
- CREATE TABLE public.bigtable
- (
- id bigint NOT NULL DEFAULT nextval('seq_bigtable_id'::regclass),
- tdate timestamp without time zone NOT NULL,
- CONSTRAINT bigtable_pkey PRIMARY KEY (id)
- );
- CREATE INDEX idx_bigtable_tdate
- ON public.bigtable (tdate);
- -- Trigger: bigtable_part_trig on public.bigtable
- -- DROP TRIGGER bigtable_part_trig ON public.bigtable;
- CREATE OR REPLACE TRIGGER bigtable_part_trig
- BEFORE INSERT
- ON public.bigtable
- FOR EACH ROW
- DECLARE
- v_part_tbl_name VARCHAR2;
- v_tbl_exist int;
- v_tbl_interval int;
- BEGIN
- IF mod(EXTRACT(DOY FROM :NEW.tdate)::int,2) = 0 THEN
- v_part_tbl_name := 'bigtable_' || to_char(NEW.tdate,'YYYYMMDD');
- v_tbl_interval := 1;
- ELSE
- v_part_tbl_name := 'bigtable_' || to_char(NEW.tdate-1,'YYYYMMDD');
- v_tbl_interval := -1;
- END IF;
- SELECT count(*) INTO v_tbl_exist FROM pg_tables WHERE schemaname='public' and tablename=v_part_tbl_name;
- IF v_tbl_exist = 0 THEN
- IF v_tbl_interval = 1 THEN
- EXECUTE IMMEDIATE 'CREATE TABLE public.' ||
- v_part_tbl_name || ' (CHECK (tdate >= ''' ||
- to_char(:NEW.tdate,'YYYY-MM-DD') ||
- ''' and tdate <= ''' || to_char(:NEW.tdate+v_tbl_interval,'YYYY-MM-DD') ||
- ''')) INHERITS (public.bigtable);';
- ELSE
- EXECUTE IMMEDIATE 'CREATE TABLE public.' ||
- v_part_tbl_name || ' (CHECK (tdate <= ''' ||
- to_char(:NEW.tdate,'YYYY-MM-DD') ||
- ''' and tdate >= ''' || to_char(:NEW.tdate+v_tbl_interval,'YYYY-MM-DD') ||
- ''')) INHERITS (public.bigtable);';
- END IF;
- END IF;
- EXECUTE IMMEDIATE 'INSERT INTO public.' || v_part_tbl_name || '
- VALUES (DEFAULT,''' || :NEW.tdate || ''');';
- RETURN NULL;
- END;
复制代码 |
|