PostgreSQL 使用函数生成 外部表DDL

如果要生成大量的外部表, 写代码闲麻烦的话, 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已经好很多了.