PostgreSQL自定义表分区函数

PostgreSQL自定义表分区函数

hash

--建表时指定分区列 partition by hash (c_bh)
CREATE TABLE t_table1
(
    c_bh   character(32) NOT NULL,
    c_name character varying(300)
) partition by hash (c_bh);

--创建分区函数
create or replace function create_hash_partition(t text, n int) returns text as
$body$
begin
    for i in 0..n - 1
        loop
            execute format('create table %s_p%s partition of %s for values WITH (MODULUS %s, REMAINDER %s)', t, i, t, n, i);
            raise info 'create %_p%',t,i;
        end loop;
    return 'success';
end;
$body$ LANGUAGE plpgsql;

-- 执行分区
select create_hash_partition('t_table1', 4);

range

--建表时指定分区列 partition by range (c_status)
CREATE TABLE t_table1
(
    c_bh   character(32) NOT NULL,
    c_name character varying(300),
    c_status varchar
) partition by range (c_status);

drop function create_range_partition;
create or replace function create_range_partition(t text, n int) returns text as
$body$
begin
    for i in 0..n - 1
        loop
            execute format('create table %s_p%s partition of %s for values from  (%s) to (%s)', t, i, t, i, i+1);
            raise info 'create %_p%',t,i;
        end loop;
    return 'success';
end;
$body$ LANGUAGE plpgsql;

select create_range_partition('t_table1', 4);

list

--建表时指定分区列 partition by list (c_status)
CREATE TABLE t_table1
(
    c_bh   character(32) NOT NULL,
    c_name character varying(300),
    c_status varchar
) partition by list (c_status);

--创建List分区函数
create or replace function create_list_partition(t text, val text) returns text as
$body$
    DECLARE
    -- 分区参数（用英文【,】号分割）
    v varchar;
begin
    for v in (select regexp_split_to_table(val, ','))
        loop
            execute format('create table %s_%s partition of %s for values in (''%s'')', t, v, t, v);
            raise info 'create %_p%',t,v;
        end loop;
    -- 创建默认分区
    execute format('create table %s_%s partition of %s default', t, 'default', t);
    return 'success';
end;
$body$ LANGUAGE plpgsql;

select create_list_partition('t_table1', '100,200,300,400');

统计分区情况

SELECT nmsp_parent.nspname AS parent_schema,
       parent.relname      AS parent,
       nmsp_child.nspname  AS child,
       child.relname       AS child_schema
FROM pg_inherits
         JOIN pg_class parent
              ON pg_inherits.inhparent = parent.oid
         JOIN pg_class child
              ON pg_inherits.inhrelid = child.oid
         JOIN pg_namespace nmsp_parent
              ON nmsp_parent.oid = parent.relnamespace
         JOIN pg_namespace nmsp_child
              ON nmsp_child.oid = child.relnamespace
WHERE parent.relname = 't_table1';