/* 地区2plproxy_0 */
psql postgres postgres
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’; – 改
create tablespace tbs_bj_plproxy_0 owner testrole location ‘/database/pgdata/tbs_bj_plproxy_0_def’; – 改
create database bj_plproxy_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_bj_plproxy_0; – 改
\q;
psql bj_plproxy_0 postgres -f $PGHOME/share/contrib/plproxy.sql
psql bj_plproxy_0 testrole
– 以上须改
create language plpgsql;
create schema testrole authorization testrole;
create schema plproxy authorization testrole;
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.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());
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′);
CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text)
RETURNS SETOF text AS $$
BEGIN
IF cluster_name = ‘all’ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 2
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 3
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 4
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 5
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 6
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 7
– RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 8
– RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 9
RETURN;
elsif cluster_name = ‘plproxy_all’ THEN
RETURN NEXT ‘dbname=sgap_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 0
RETURN NEXT ‘dbname=bj_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 1
RETURN NEXT ‘dbname=sh_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 2
RETURN NEXT ‘dbname=gz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 3
– RETURN NEXT ‘dbname=hz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′;– 4
RETURN;
elsif cluster_name = ’sgap_testrole_0′ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sgap_testrole_1′ THEN
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘bj_testrole_0′ THEN
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘bj_testrole_1′ THEN
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sh_testrole_0′ THEN
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sh_testrole_1′ THEN
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘gz_testrole_0′ THEN
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘gz_testrole_1′ THEN
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘hz_testrole_0′ THEN
RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘hz_testrole_1′ THEN
RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sgap_testrole_all’ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘bj_testrole_all’ THEN
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ’sh_testrole_all’ THEN
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘gz_testrole_all’ THEN
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘hz_testrole_all’ THEN
RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
END IF;
RAISE EXCEPTION ‘Unknown cluster’;
END;
$$ LANGUAGE plpgsql;
– 获取集群组配置版本
CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(i_cluster_name text)
RETURNS int4 AS $$
declare
i_ver int4;
BEGIN
select max(version) into i_ver from testrole.tbl_cluster_version where cluster_name=i_cluster_name;
if found then
RETURN i_ver;
end if;
RAISE EXCEPTION ‘Unknown cluster’;
END;
$$ LANGUAGE plpgsql;
– 获取集群组配置参数
CREATE OR REPLACE FUNCTION plproxy.get_cluster_config(
in cluster_name text,out key text,out val text )
RETURNS SETOF record AS $$
BEGIN
– lets use same config for all clusters
key := ‘connection_lifetime’;
val := 30*60; — 30m
RETURN NEXT;
– if cluster_name=’singap_db_testrole_2′ then
– – lets use same config for all clusters
– key := ‘connection_lifetime’;
– val := 30*60; — 30m
– RETURN NEXT;
– end if;
RETURN;
END;
$$ LANGUAGE plpgsql;
– 改
\c bj_plproxy_0 postgres
– 每个表需要一个查询函数
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;
– 每个代理库需要建一个,全局所有,本地所有,单一,数据库的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;
– 对应用程序的接口
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 ‘bj_plproxy_0′; –对应自己所在的代理
run on abs(trunc(mod(hashtext(i_phonenum),2))::int4);
$BODY$ language plproxy;
\c sgap_plproxy_0 testrole
– 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 1 ; — 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end if ;
insert into testrole.pk_tbl_test_def values(i_phonenum);
return 1 ; – 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end;
$BODY$ language plpgsql;
/* 地区3plproxy_0 */
psql postgres postgres
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’; – 改
create tablespace tbs_sh_plproxy_0 owner testrole location ‘/database/pgdata/tbs_sh_plproxy_0_def’; – 改
create database sh_plproxy_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_sh_plproxy_0; – 改
\q;
psql sh_plproxy_0 postgres -f $PGHOME/share/contrib/plproxy.sql
psql sh_plproxy_0 testrole
– 以上须改
create language plpgsql;
create schema testrole authorization testrole;
create schema plproxy authorization testrole;
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.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());
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′);
CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text)
RETURNS SETOF text AS $$
BEGIN
IF cluster_name = ‘all’ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 2
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 3
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 4
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 5
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 6
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 7
– RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 8
– RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 9
RETURN;
elsif cluster_name = ‘plproxy_all’ THEN
RETURN NEXT ‘dbname=sgap_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 0
RETURN NEXT ‘dbname=bj_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 1
RETURN NEXT ‘dbname=sh_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 2
RETURN NEXT ‘dbname=gz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 3
– RETURN NEXT ‘dbname=hz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′;– 4
RETURN;
elsif cluster_name = ’sgap_testrole_0′ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sgap_testrole_1′ THEN
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘bj_testrole_0′ THEN
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘bj_testrole_1′ THEN
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sh_testrole_0′ THEN
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sh_testrole_1′ THEN
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘gz_testrole_0′ THEN
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘gz_testrole_1′ THEN
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘hz_testrole_0′ THEN
RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘hz_testrole_1′ THEN
RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sgap_testrole_all’ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘bj_testrole_all’ THEN
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ’sh_testrole_all’ THEN
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘gz_testrole_all’ THEN
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘hz_testrole_all’ THEN
RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
END IF;
RAISE EXCEPTION ‘Unknown cluster’;
END;
$$ LANGUAGE plpgsql;
– 获取集群组配置版本
CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(i_cluster_name text)
RETURNS int4 AS $$
declare
i_ver int4;
BEGIN
select max(version) into i_ver from testrole.tbl_cluster_version where cluster_name=i_cluster_name;
if found then
RETURN i_ver;
end if;
RAISE EXCEPTION ‘Unknown cluster’;
END;
$$ LANGUAGE plpgsql;
– 获取集群组配置参数
CREATE OR REPLACE FUNCTION plproxy.get_cluster_config(
in cluster_name text,out key text,out val text )
RETURNS SETOF record AS $$
BEGIN
– lets use same config for all clusters
key := ‘connection_lifetime’;
val := 30*60; — 30m
RETURN NEXT;
– if cluster_name=’singap_db_testrole_2′ then
– – lets use same config for all clusters
– key := ‘connection_lifetime’;
– val := 30*60; — 30m
– RETURN NEXT;
– end if;
RETURN;
END;
$$ LANGUAGE plpgsql;
– 改
\c sh_plproxy_0 postgres
– 每个表需要一个查询函数
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;
– 每个代理库需要建一个,全局所有,本地所有,单一,数据库的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;
– 对应用程序的接口
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 ’sh_plproxy_0′; –对应自己所在的代理
run on abs(trunc(mod(hashtext(i_phonenum),2))::int4);
$BODY$ language plproxy;
\c sgap_plproxy_0 testrole
– 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 2 ; — 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end if ;
insert into testrole.pk_tbl_test_def values(i_phonenum);
return 2 ; – 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end;
$BODY$ language plpgsql;
/* 地区4plproxy_0 */
psql postgres postgres
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’; – 改
create tablespace tbs_gz_plproxy_0 owner testrole location ‘/database/pgdata/tbs_gz_plproxy_0_def’; – 改
create database gz_plproxy_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_gz_plproxy_0; – 改
\q;
psql gz_plproxy_0 postgres -f $PGHOME/share/contrib/plproxy.sql
psql gz_plproxy_0 testrole
– 以上须改
create language plpgsql;
create schema testrole authorization testrole;
create schema plproxy authorization testrole;
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.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());
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′);
CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text)
RETURNS SETOF text AS $$
BEGIN
IF cluster_name = ‘all’ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 2
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 3
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 4
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 5
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 6
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 7
– RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 8
– RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 9
RETURN;
elsif cluster_name = ‘plproxy_all’ THEN
RETURN NEXT ‘dbname=sgap_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 0
RETURN NEXT ‘dbname=bj_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 1
RETURN NEXT ‘dbname=sh_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 2
RETURN NEXT ‘dbname=gz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 3
– RETURN NEXT ‘dbname=hz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′;– 4
RETURN;
elsif cluster_name = ’sgap_testrole_0′ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sgap_testrole_1′ THEN
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘bj_testrole_0′ THEN
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘bj_testrole_1′ THEN
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sh_testrole_0′ THEN
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sh_testrole_1′ THEN
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘gz_testrole_0′ THEN
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘gz_testrole_1′ THEN
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘hz_testrole_0′ THEN
RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘hz_testrole_1′ THEN
RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sgap_testrole_all’ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘bj_testrole_all’ THEN
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ’sh_testrole_all’ THEN
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘gz_testrole_all’ THEN
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘hz_testrole_all’ THEN
RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
END IF;
RAISE EXCEPTION ‘Unknown cluster’;
END;
$$ LANGUAGE plpgsql;
– 获取集群组配置版本
CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(i_cluster_name text)
RETURNS int4 AS $$
declare
i_ver int4;
BEGIN
select max(version) into i_ver from testrole.tbl_cluster_version where cluster_name=i_cluster_name;
if found then
RETURN i_ver;
end if;
RAISE EXCEPTION ‘Unknown cluster’;
END;
$$ LANGUAGE plpgsql;
– 获取集群组配置参数
CREATE OR REPLACE FUNCTION plproxy.get_cluster_config(
in cluster_name text,out key text,out val text )
RETURNS SETOF record AS $$
BEGIN
– lets use same config for all clusters
key := ‘connection_lifetime’;
val := 30*60; — 30m
RETURN NEXT;
– if cluster_name=’singap_db_testrole_2′ then
– – lets use same config for all clusters
– key := ‘connection_lifetime’;
– val := 30*60; — 30m
– RETURN NEXT;
– end if;
RETURN;
END;
$$ LANGUAGE plpgsql;
– 改
\c gz_plproxy_0 postgres
– 每个表需要一个查询函数
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;
– 每个代理库需要建一个,全局所有,本地所有,单一,数据库的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;
– 对应用程序的接口
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 ‘gz_plproxy_0′; –对应自己所在的代理
run on abs(trunc(mod(hashtext(i_phonenum),2))::int4);
$BODY$ language plproxy;
\c sgap_plproxy_0 testrole
– 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 3 ; — 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end if ;
insert into testrole.pk_tbl_test_def values(i_phonenum);
return 3 ; – 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end;
$BODY$ language plpgsql;
/* 地区5plproxy_0 */
psql postgres postgres
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’; – 改
create tablespace tbs_hz_plproxy_0 owner testrole location ‘/database/pgdata/tbs_hz_plproxy_0_def’; – 改
create database hz_plproxy_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_hz_plproxy_0; – 改
\q;
psql hz_plproxy_0 postgres -f $PGHOME/share/contrib/plproxy.sql
psql hz_plproxy_0 testrole
– 以上须改
create language plpgsql;
create schema testrole authorization testrole;
create schema plproxy authorization testrole;
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.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());
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′);
CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text)
RETURNS SETOF text AS $$
BEGIN
IF cluster_name = ‘all’ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 2
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 3
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 4
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 5
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 6
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 7
– RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 8
– RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 9
RETURN;
elsif cluster_name = ‘plproxy_all’ THEN
RETURN NEXT ‘dbname=sgap_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 0
RETURN NEXT ‘dbname=bj_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 1
RETURN NEXT ‘dbname=sh_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 2
RETURN NEXT ‘dbname=gz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; — 3
– RETURN NEXT ‘dbname=hz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′;– 4
RETURN;
elsif cluster_name = ’sgap_testrole_0′ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sgap_testrole_1′ THEN
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘bj_testrole_0′ THEN
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘bj_testrole_1′ THEN
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sh_testrole_0′ THEN
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sh_testrole_1′ THEN
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘gz_testrole_0′ THEN
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘gz_testrole_1′ THEN
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘hz_testrole_0′ THEN
RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ‘hz_testrole_1′ THEN
RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN;
elsif cluster_name = ’sgap_testrole_all’ THEN
RETURN NEXT ‘dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘bj_testrole_all’ THEN
RETURN NEXT ‘dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ’sh_testrole_all’ THEN
RETURN NEXT ‘dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘gz_testrole_all’ THEN
RETURN NEXT ‘dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
elsif cluster_name = ‘hz_testrole_all’ THEN
RETURN NEXT ‘dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 0
RETURN NEXT ‘dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601′; – 1
RETURN;
END IF;
RAISE EXCEPTION ‘Unknown cluster’;
END;
$$ LANGUAGE plpgsql;
– 获取集群组配置版本
CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(i_cluster_name text)
RETURNS int4 AS $$
declare
i_ver int4;
BEGIN
select max(version) into i_ver from testrole.tbl_cluster_version where cluster_name=i_cluster_name;
if found then
RETURN i_ver;
end if;
RAISE EXCEPTION ‘Unknown cluster’;
END;
$$ LANGUAGE plpgsql;
– 获取集群组配置参数
CREATE OR REPLACE FUNCTION plproxy.get_cluster_config(
in cluster_name text,out key text,out val text )
RETURNS SETOF record AS $$
BEGIN
– lets use same config for all clusters
key := ‘connection_lifetime’;
val := 30*60; — 30m
RETURN NEXT;
– if cluster_name=’singap_db_testrole_2′ then
– – lets use same config for all clusters
– key := ‘connection_lifetime’;
– val := 30*60; — 30m
– RETURN NEXT;
– end if;
RETURN;
END;
$$ LANGUAGE plpgsql;
– 改
\c hz_plproxy_0 postgres
– 每个表需要一个查询函数
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;
– 每个代理库需要建一个,全局所有,本地所有,单一,数据库的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;
– 对应用程序的接口
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.poneins_tbl_test(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
cluster ‘hz_plproxy_0′; –对应自己所在的代理
run on abs(trunc(mod(hashtext(i_phonenum),2))::int4);
$BODY$ language plproxy;
\c sgap_plproxy_0 testrole
– 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 4 ; — 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end if ;
insert into testrole.pk_tbl_test_def values(i_phonenum);
return 4 ; – 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end;
$BODY$ language plpgsql;