- 论坛徽章:
- 0
|
/* 地区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; |
|
免费注册
发表于 2011-02-22 20:02
