PostgreSQL 使用函数生成 外部表DDL(use function generate foreign table DDL in postgresql)

4 minute read

背景

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

Flag Counter

digoal’s 大量PostgreSQL文章入口