- 论坛徽章:
- 0
|
5. 分区库(建表,函数)
– 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
– 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
– 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
– 创建cluster_all范围内使用insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
– 创建cluster_all范围插入针对某个表,高效插入时可以使用这种方式,但是需要每个表建立相应的函数
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
–在cluster_local_all范围内使用 , 当使用plproxy级联时
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
6. 代理库(建表,函数)
– 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
– 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
– cluster 版本记录表
create table testrole.tbl_cluster_version(cluster_name varchar(50),version int4);
insert into testrole.tbl_cluster_version values (‘all’,1),(‘plproxy_all’,1),(’sgap_testrole_0′,1),(’sgap_testrole_1′,1),(‘bj_testrole_0′,1),(‘bj_testrole_1′,1),(’sh_testrole_0′,1),(’sh_testrole_1′,1),(‘gz_testrole_0′,1),(‘gz_testrole_1′,1),(‘hz_testrole_0′,1),(‘hz_testrole_1′,1),(’sgap_testrole_all’,1),(‘bj_testrole_all’,1),(’sh_testrole_all’,1),(‘gz_testrole_all’,1),(‘hz_testrole_all’,1) ;
– 分区关键值表
create table testrole.pk_tbl_test(phonenum varchar(30));
create table testrole.pk_tbl_test_sgap() inherits (testrole.pk_tbl_test);
create table testrole.pk_tbl_test_bj() inherits (testrole.pk_tbl_test);
create table testrole.pk_tbl_test_sh() inherits (testrole.pk_tbl_test);
create table testrole.pk_tbl_test_gz() inherits (testrole.pk_tbl_test);
create table testrole.pk_tbl_test_hz() inherits (testrole.pk_tbl_test);
create table testrole.pk_tbl_test_def() inherits (testrole.pk_tbl_test);
insert into testrole.pk_tbl_test_sgap values(‘0′),(‘1′),(‘2′),(‘3′),(‘4′),(‘5′),(‘6′),(‘7′),(‘8′),(‘9′);
insert into testrole.pk_tbl_test_bj values(‘10′),(‘11′),(‘12′),(‘13′),(‘14′),(‘15′),(‘16′),(‘17′),(‘18′),(‘19′);
insert into testrole.pk_tbl_test_sh values(‘20′),(‘21′),(‘22′),(‘23′),(‘24′),(‘25′),(‘26′),(‘27′),(‘28′),(‘29′);
insert into testrole.pk_tbl_test_gz values(‘30′),(‘31′),(‘32′),(‘33′),(‘34′),(‘35′),(‘36′),(‘37′),(‘38′),(‘39′);
insert into testrole.pk_tbl_test_hz values(‘40′),(‘41′),(‘42′),(‘43′),(‘44′),(‘45′),(‘46′),(‘47′),(‘48′),(‘49′);
– cluster_all范围内使用的查询函数 , 每个表需要一个查询函数
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
cluster ‘all’;
run on all;
select * from testrole.ca_tbl_test_sel(v_sql);
$BODY$ language plproxy;
– 每个代理库仅需要建一个,用于cluster_all范围内Insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns setof void as $BODY$
cluster ‘all’;
run on all;
$BODY$ language plproxy;
– cluster_plproxy_all范围内使用的函数 , 级联
create or replace function testrole.cpa_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
cluster ‘plproxy_all’;
run on testrole.get_pk_tbl_test(i_phonenum);
select * from testrole.cla_tbl_test_ins(i_phonenum,i_price,i_createtime);
$BODY$ language plproxy;
–(各代理库配置不一样)对上面函数的接口,每个代理库配置对应自己的代理名
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
cluster ’sgap_testrole_all’; — 对应自己所在的代理
run on abs(trunc(mod(hashtext(i_phonenum),2))::int4);
$BODY$ language plproxy;
– cluster_plproxy_all 范围内对数据进行分类的函数
create or replace function testrole.get_pk_tbl_test(i_phonenum varchar) returns int4 as $BODY$
begin
perform 1 from testrole.pk_tbl_test_sgap where phonenum=i_phonenum;
if found then
return 0 ;
end if;
perform 1 from testrole.pk_tbl_test_bj where phonenum=i_phonenum;
if found then
return 1 ;
end if;
perform 1 from testrole.pk_tbl_test_sh where phonenum=i_phonenum;
if found then
return 2 ;
end if;
perform 1 from testrole.pk_tbl_test_gz where phonenum=i_phonenum;
if found then
return 3 ;
end if;
–perform 1 from testrole.pk_tbl_test_hz where phonenum=i_phonenum;
–if found then
–return 4 ;
–end if;
perform 1 from testrole.pk_tbl_test_def where phonenum=i_phonenum;
if found then
return 0 ; — 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end if ;
insert into testrole.pk_tbl_test_def values(i_phonenum);
return 0 ; – 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end;
$BODY$ language plpgsql;
7. 哈希分区 :
abs(trunc(mod(hashtext(createtime::text),2))::int4)
8. 测试 .
/* 分区库详细操作 */
/* 地区1sgap_testrole_0 */
–创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’;
–创建表空间
create tablespace tbs_sgap_testrole_0 owner testrole location ‘/database/pgdata/tbs_sgap_testrole_0_def’;
–创建分区数据库
create database sgap_testrole_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_sgap_testrole_0;
–连接至已常见好的分区数据库及role
\c sgap_testrole_0 testrole;
–创建本地schema
create schema testrole authorization testrole;
–创建过程语言
create language plpgsql;
–创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
–创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
–创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
–创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
–在cluster_all范围内使用,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
–在cluster_local_all范围内使用 , 当使用plproxy级联时
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
/* 地区1sgap_testrole_1 */
–创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’;
–创建表空间
create tablespace tbs_sgap_testrole_1 owner testrole location ‘/database/pgdata/tbs_sgap_testrole_1_def’;
–创建分区数据库
create database sgap_testrole_1 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_sgap_testrole_1;
–连接至已常见好的分区数据库及role
\c sgap_testrole_1 testrole;
–创建本地schema
create schema testrole authorization testrole;
–创建过程语言
create language plpgsql;
–创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
–创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
–创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
–创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
–创建global_all,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
–创建local_all
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
/* 地区2bj_testrole_0 */
–创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’;
–创建表空间
create tablespace tbs_bj_testrole_0 owner testrole location ‘/database/pgdata/tbs_bj_testrole_0_def’;
–创建分区数据库
create database bj_testrole_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_bj_testrole_0;
–连接至已常见好的分区数据库及role
\c bj_testrole_0 testrole;
–创建本地schema
create schema testrole authorization testrole;
–创建过程语言
create language plpgsql;
–创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
–创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
–创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
–创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
–创建global_all,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
–创建local_all
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql; |
|
免费注册
发表于 2011-02-22 20:01
