PostgreSQL使用函数生成外部表DDL_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已经好很多了.

(责任编辑：好模板)

PostgreSQL使用函数生成 外部表DDL

PostgreSQL使用函数生成外部表DDL