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相比已经好很多了.