摘要：創(chuàng)建自動分區(qū)采用兩種方式采用視圖分區(qū)方式采用直接分區(qū)方式創(chuàng)建表創(chuàng)建索引采用視圖分區(qū)方式建立視圖定義分表保證分區(qū)后的可以自增按照時間進行分區(qū)分表觸發(fā)器定義更新更新觸發(fā)器直接分區(qū)方式分表觸發(fā)器兩種方式比較視圖分區(qū)所有操作都是對視圖的操

創(chuàng)建自動分區(qū)采用兩種方式

采用視圖分區(qū)方式
采用直接分區(qū)方式

CREATE TABLE IF NOT EXISTS public.sales
(
    id bigserial primary key ,
    store_id varchar(50) ,
    business_date date,
    start_time time,
    end_time time,
    dine_in_tc int,
    delivery_tc int,
    takeout_tc int,
    dine_in_s decimal(20,4),
    delivery_s decimal(20,4),
    takeout_s decimal(20,4),
    voucher_overcharge decimal(20,4),
    freight decimal(20,4),
    currency varchar(16),
    created_at timestamp default now(),
    updated_at timestamp default now()
);

CREATE INDEX sales_store_id ON public.sales (store_id);
CREATE INDEX sales_business_date ON public.sales (business_date);
ALTER TABLE public.sales  ADD CONSTRAINT sales_storeid_businessdate_starttime_endtime UNIQUE(store_id,business_date,start_time,end_time);

CREATE VIEW public.sales_view AS SELECT * FROM public.sales;

CREATE OR REPLACE FUNCTION public.insert_sales() 
RETURNS TRIGGER AS
"."$BODY"."$
DECLARE
    _start_dt text;
    _end_dt text;
    _table_name text;
BEGIN     
IF NEW.id IS NULL THEN
NEW.id := nextval("sales_id_seq");    // 保證分區(qū)后的id可以自增
END IF;
_table_name := "sales_view_" || to_char(NEW.business_date, "YYYY_MM");    // 按照時間進行分區(qū)

PERFORM 1
FROM   pg_catalog.pg_class c
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = "r"
AND    c.relname = _table_name
AND    n.nspname = public;
IF NOT FOUND THEN
    _start_dt := to_char(date_trunc("month", NEW.business_date), "YYYY-MM-DD");
    _end_dt:=_start_dt::timestamp + INTERVAL "1 month";
    EXECUTE 
    "CREATE TABLE public." 
    || quote_ident(_table_name) 
    || " (CHECK (business_date >= " 
    || quote_literal(_start_dt) 
    || "AND business_date < " 
    || quote_literal(_end_dt) 
    || ")) INHERITS (public.sales)";
    EXECUTE "CREATE INDEX " || quote_ident(_table_name||"_business_date"||_start_dt) || " ON public." || quote_ident(_table_name) || " (business_date)";
    EXECUTE "CREATE INDEX " || quote_ident(_table_name||"_store_id"||_start_dt) || " ON public." || quote_ident(_table_name) || " (store_id)";
    EXECUTE "ALTER TABLE public." || quote_ident(_table_name) || " ADD CONSTRAINT " || quote_ident(_table_name||"_storeid_businessdate_starttime_endtime"||_start_dt) || " UNIQUE (store_id,business_date,start_time,end_time)";
    EXECUTE "ALTER TABLE public." || quote_ident(_table_name) || " OWNER TO " || quote_ident(current_user);
    EXECUTE "GRANT ALL ON TABLE public." || quote_ident(_table_name) || " TO " || quote_ident(current_user);
END IF;
    EXECUTE "INSERT INTO public." || quote_ident(_table_name) || " VALUES ($1.*) RETURNING *" USING NEW;
    RETURN NEW;
END;
"."$BODY"."$
LANGUAGE plpgsql;
";

CREATE TRIGGER insert_sales_trigger INSTEAD OF INSERT ON public.sales_view FOR EACH ROW EXECUTE PROCEDURE insert_sales();

CREATE OR REPLACE FUNCTION update_sales()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM sales_view WHERE id = NEW.id;
    INSERT INTO sales_view VALUES (NEW.*);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
";

CREATE TRIGGER update_sales_trigger INSTEAD OF UPDATE ON sales_view FOR EACH ROW EXECUTE PROCEDURE update_oc_sales();

CREATE OR REPLACE FUNCTION insert_sales() 
RETURNS TRIGGER AS
"."$BODY"."$
DECLARE
    _start_dt text;
    _end_dt text;
    _table_name text;
BEGIN     
IF NEW.id IS NULL THEN
    NEW.id := nextval("".$this->tableName."_id_seq");  
END IF;
_table_name := "sales_" || to_char(NEW.business_date, "YYYY_MM");
PERFORM 1
FROM   pg_catalog.pg_class c
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = "r"
AND    c.relname = _table_name
AND    n.nspname = "public";
IF NOT FOUND THEN
    _start_dt := to_char(date_trunc("month", NEW.business_date), "YYYY-MM-DD");
    _end_dt:=_start_dt::timestamp + INTERVAL "1 month";
    EXECUTE 
    "CREATE TABLE IF NOT EXISTS public." 
    || quote_ident(_table_name) 
    || " (CHECK (business_date >= " 
    || quote_literal(_start_dt) 
    || "AND business_date < " 
    || quote_literal(_end_dt) 
    || ")) INHERITS (public.sales)";
    EXECUTE "CREATE INDEX IF NOT EXISTS" || quote_ident(_table_name||"_business_date"||_start_dt) || " ON public." || quote_ident(_table_name) || " (business_date)";
    EXECUTE "CREATE INDEX IF NOT EXISTS" || quote_ident(_table_name||"_store_id"||_start_dt) || " ON public." || quote_ident(_table_name) || " (store_id)";
    EXECUTE "CREATE UNIQUE INDEX IF NOT EXISTS" || quote_ident(_table_name||"_storeid_businessdate_starttime_endtime"||_start_dt) || " ON public." || quote_ident(_table_name) || " (store_id,business_date,start_time,end_time)";
    EXECUTE "ALTER TABLE public." || quote_ident(_table_name) || " OWNER TO " || quote_ident(current_user);
    EXECUTE "GRANT ALL ON TABLE public." || quote_ident(_table_name) || " TO " || quote_ident(current_user);
END IF;
    EXECUTE "INSERT INTO public." || quote_ident(_table_name) || " VALUES ($1.*) on conflict(store_id,business_date,start_time,end_time) do nothing RETURNING *" USING NEW;
    RETURN NULL;
END;
"."$BODY"."$
LANGUAGE plpgsql;

CREATE TRIGGER insert_sales_trigger BEFORE INSERT ON public.salses FOR EACH ROW EXECUTE PROCEDURE insert_sales();

視圖分區(qū)所有操作都是對視圖的操作，直接分區(qū)是對主表進行操作；

視圖分區(qū)觸發(fā)器使用instead of,直接分區(qū)使用before,因為無法直接用觸發(fā)器替代對主表的操作，只能操作視圖；

視圖分區(qū)用instead of，在function中可以RETURN NEW，對數(shù)據(jù)庫操作后有明確的返回，直接分區(qū)用before方式，在function中采用RETURN NULL,數(shù)據(jù)庫操作沒有返回；

直接分區(qū)可以用on conflict對主表insert進行ignore操作，視圖分區(qū)不能。