{**使用Plproxy**设计**PostgreSQL**分布式数据库**(三)**}

/* 地区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;

现在的pgAdminⅢ做得挺不错。这些命令点都能点出来了。