转自()
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;
/* 地区2bj_testrole_1 */
–创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’;
–创建表空间
create tablespace tbs_bj_testrole_1 owner testrole location ‘/database/pgdata/tbs_bj_testrole_1_def’;
–创建分区数据库
create database bj_testrole_1 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_bj_testrole_1;
–连接至已常见好的分区数据库及role
\c bj_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;
/* 地区3sh_testrole_0 */
–创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’;
–创建表空间
create tablespace tbs_sh_testrole_0 owner testrole location ‘/database/pgdata/tbs_sh_testrole_0_def’;
–创建分区数据库
create database sh_testrole_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_sh_testrole_0;
–连接至已常见好的分区数据库及role
\c sh_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;
/* 地区3sh_testrole_1 */
–创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’;
–创建表空间
create tablespace tbs_sh_testrole_1 owner testrole location ‘/database/pgdata/tbs_sh_testrole_1_def’;
–创建分区数据库
create database sh_testrole_1 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_sh_testrole_1;
–连接至已常见好的分区数据库及role
\c sh_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;
/* 地区4gz_testrole_0 */
–创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’;
–创建表空间
create tablespace tbs_gz_testrole_0 owner testrole location ‘/database/pgdata/tbs_gz_testrole_0_def’;
–创建分区数据库
create database gz_testrole_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_gz_testrole_0;
–连接至已常见好的分区数据库及role
\c gz_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;
/* 地区4gz_testrole_1 */
–创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’;
–创建表空间
create tablespace tbs_gz_testrole_1 owner testrole location ‘/database/pgdata/tbs_gz_testrole_1_def’;
–创建分区数据库
create database gz_testrole_1 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_gz_testrole_1;
–连接至已常见好的分区数据库及role
\c gz_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;
/* 地区5hz_testrole_0 */
–创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’;
–创建表空间
create tablespace tbs_hz_testrole_0 owner testrole location ‘/database/pgdata/tbs_hz_testrole_0_def’;
–创建分区数据库
create database hz_testrole_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_hz_testrole_0;
–连接至已常见好的分区数据库及role
\c hz_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;
/* 地区5hz_testrole_1 */
–创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’;
–创建表空间
create tablespace tbs_hz_testrole_1 owner testrole location ‘/database/pgdata/tbs_hz_testrole_1_def’;
–创建分区数据库
create database hz_testrole_1 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_hz_testrole_1;
–连接至已常见好的分区数据库及role
\c hz_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;
/* 地区1sgap_plproxy_0 */
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’;
create tablespace tbs_sgap_plproxy_0 owner testrole location ‘/database/pgdata/tbs_sgap_plproxy_0_def’;
create database sgap_plproxy_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_sgap_plproxy_0;
\q;
psql sgap_plproxy_0 postgres -f $PGHOME/share/contrib/plproxy.sql
psql sgap_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 sgap_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 ’sgap_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 0 ; — 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end if ;
insert into testrole.pk_tbl_test_def values(i_phonenum);
return 0 ; – 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end;
$BODY$ language plpgsql;
