PostgreSQL 使用函数生成 外部表DDL(use function generate foreign table DDL in postgresql)
背景
如果要生成大量的外部表, 写代码闲麻烦的话, PostgreSQL 9.5 可以通过import foreign schema 一键创建外部表, 以前的版本则可以通过如下方法快速的生成外部表的DDL.
创建postgresql外部表fdw
postgres=# create extension postgres_fdw;
CREATE EXTENSION
创建server, 指定远端数据库的ip, port, dbname
postgres=# create server fs foreign data wrapper postgres_fdw options (hostaddr '172.16.3.150', port '1921', dbname 'postgres');
CREATE SERVER
创建一个本地角色
postgres=# create role test login encrypted password 'test';
CREATE ROLE
配置本地角色teset使用server的user mapping, 指定连接到server的用户,密码.
postgres=# CREATE USER MAPPING FOR test SERVER fs OPTIONS (user 'postgres', password 'postgres');
CREATE USER MAPPING
将server的使用权给本地用户test
postgres=# grant usage on FOREIGN SERVER fs to test;
GRANT
假设远端有一个表是orig
postgres=# \d orig
Table "public.orig"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
x | numeric |
在test 用户下创建外部表
postgres=# \c postgres test
You are now connected to database "postgres" as user "test".
postgres=> create foreign table f_orig (id int, x numeric) server fs options(schema_name 'public', table_name 'orig');
CREATE FOREIGN TABLE
配置远端数据库的 pg_hba.conf, 允许访问.
postgres@db-172-16-3-150-> cd $PGDATA
postgres@db-172-16-3-150-> vi pg_hba.conf
host all all 0.0.0.0/0 md5
"pg_hba.conf" 94L, 4495C written
postgres@db-172-16-3-150-> pg_ctl reload
server signaled
测试外部表的访问
postgres=# \c postgres test
You are now connected to database "postgres" as user "test".
postgres=> select * from f_orig;
id | x
------+--------
1 | 93.23
2 | 95.24
3 | 95.19
.............
为了快速刷新物化视图, 原表最好有UK或PK
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \d orig
Table "public.orig"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
x | numeric |
postgres=# create unique index uk_orig on orig(id);
CREATE INDEX
使用如下函数自动生成外部表的DDL, 需要指定本地表的schema, tbl_name, 以及外部表需要创建在哪个schema下, 外部表的名称.
create or replace function create_ft(i_schema name, i_tbl name, i_ftschema text, i_ftname text) returns text as $$
declare
v_attname name;
v_type text;
v_sql text;
v_nt text := '';
v_oid oid;
begin
select oid into v_oid from pg_class where relnamespace=(select oid from pg_namespace where nspname=i_schema) and relname=i_tbl;
v_sql := 'create foreign table '||i_ftschema||'.'||i_ftname||' (';
for v_attname,v_type in SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = v_oid AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
LOOP
v_nt := v_nt||v_attname||' '||v_type||','||chr(10);
END LOOP;
select regexp_replace(v_nt,','||chr(10)||'$','') into v_nt;
v_sql := v_sql||v_nt||$_$) server fs options(schema_name '$_$||i_schema||$_$', table_name '$_$||i_tbl||$_$');$_$;
return v_sql;
end;
$$ language plpgsql strict;
如下 :
postgres=# select create_ft('pg_catalog','pg_class','public','f_orig');
create_ft
----------------------------------------------------------------------------------------
create foreign table public.f_orig (relname name, +
relnamespace oid, +
reltype oid, +
reloftype oid, +
relowner oid, +
relam oid, +
relfilenode oid, +
reltablespace oid, +
relpages integer, +
reltuples real, +
relallvisible integer, +
reltoastrelid oid, +
relhasindex boolean, +
relisshared boolean, +
relpersistence "char", +
relkind "char", +
relnatts smallint, +
relchecks smallint, +
relhasoids boolean, +
relhaspkey boolean, +
relhasrules boolean, +
relhastriggers boolean, +
relhassubclass boolean, +
relispopulated boolean, +
relreplident "char", +
relfrozenxid xid, +
relminmxid xid, +
relacl aclitem[], +
reloptions text[]) server fs options(schema_name 'pg_catalog', table_name 'pg_class');
(1 row)
创建物化视图
postgres=# \c postgres test
You are now connected to database "postgres" as user "test".
postgres=> create materialized view mv1 as select * from f_orig;
SELECT 1053
快速刷新, 物化视图必须有UK或PK.
postgres=> create unique index uk_mv1 on mv1(id);
CREATE INDEX
postgres=> refresh materialized view concurrently mv1;
REFRESH MATERIALIZED VIEW
如果DDL不想输出换行符, 修改函数如下 :
create or replace function create_ft(i_schema name, i_tbl name, i_ftschema text, i_ftname text) returns text as $$
declare
v_attname name;
v_type text;
v_sql text;
v_nt text := '';
v_oid oid;
begin
select oid into v_oid from pg_class where relnamespace=(select oid from pg_namespace where nspname=i_schema) and relname=i_tbl;
v_sql := 'create foreign table '||i_ftschema||'.'||i_ftname||' (';
for v_attname,v_type in SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = v_oid AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
LOOP
v_nt := v_nt||v_attname||' '||v_type||',';
END LOOP;
select regexp_replace(v_nt,',$','') into v_nt;
v_sql := v_sql||v_nt||$_$) server fs options(schema_name '$_$||i_schema||$_$', table_name '$_$||i_tbl||$_$');$_$;
return v_sql;
end;
$$ language plpgsql strict;
批量输出DDL
postgres=# copy (select create_ft('public',tablename,'public','ft_'||tablename) from pg_tables where schemaname='public') to '/home/postgres/p';
COPY 14
postgres=> \!
[postgres@db-172-16-3-150 ~]$ cat p
create foreign table public.ft_p4 (ov integer,v_id integer,r_chkv numeric,p_yv numeric,r_xv numeric,dev numeric,v_slope numeric,v_inter numeric,v_r2 numeric,sampcnt integer) server fs options(schema_name 'public', table_name 'p4');
create foreign table public.ft_tmp1 (id integer,x numeric,y numeric) server fs options(schema_name 'public', table_name 'tmp1');
create foreign table public.ft_tmp2 (id integer,x numeric,y numeric) server fs options(schema_name 'public', table_name 'tmp2');
create foreign table public.ft_tmp3 (id integer,x numeric,y numeric) server fs options(schema_name 'public', table_name 'tmp3');
create foreign table public.ft_tmp4 (id integer,x numeric,y numeric) server fs options(schema_name 'public', table_name 'tmp4');
create foreign table public.ft_p2 (ov integer,v_id integer,r_chkv numeric,p_yv numeric,r_xv numeric,dev numeric,v_slope numeric,v_inter numeric,v_r2 numeric,sampcnt integer) server fs options(schema_name 'public', table_name 'p2');
create foreign table public.ft_zjdr (id numeric) server fs options(schema_name 'public', table_name 'zjdr');
create foreign table public.ft_zj (id integer,cnt numeric) server fs options(schema_name 'public', table_name 'zj');
create foreign table public.ft_t1 (c1 text,c2 numeric,c3 numeric,c4 numeric,c5 numeric,c6 numeric,c7 numeric) server fs options(schema_name 'public', table_name 't1');
create foreign table public.ft_test (id integer,cnt numeric) server fs options(schema_name 'public', table_name 'test');
create foreign table public.ft_orig (id integer,x numeric) server fs options(schema_name 'public', table_name 'orig');
create foreign table public.ft_tmp (id integer,x numeric,y numeric) server fs options(schema_name 'public', table_name 'tmp');
create foreign table public.ft_p3 (ov integer,v_id integer,r_chkv numeric,p_yv numeric,r_xv numeric,dev numeric,v_slope numeric,v_inter numeric,v_r2 numeric,sampcnt integer) server fs options(schema_name 'public', table_name 'p3');
create foreign table public.ft_p1 (ov integer,v_id integer,r_chkv numeric,p_yv numeric,r_xv numeric,dev numeric,v_slope numeric,v_inter numeric,v_r2 numeric,sampcnt integer) server fs options(schema_name 'public', table_name 'p1');
这样生成的代码比较紧凑, 直接执行以上生成的SQL就可以创建这些外部表了.
虽然没有import foreign schema语法简便, 但是相比手工写DDL已经好很多了.