SQL/MED((Management of External Data) )早在 PostgreSQL8.4 版本已谈到，但是到 9.1 版本已经可以创建外部表（ foreign tables）, 并通过外部表访问外部数据，这也是 SQL/MED 的主要目的之一。 SQL/MED 可以访问多种类型的外部数据，可以是操作系统的文件，也可以是其它类型数据库如 oracle ,MySQL 等，关于支持的外部数据类型，详见 http://wiki.postgresql.org/wiki/Foreign_data_wrappers 这篇 blog 主要讨论如何在数据库里直接访问文件系统上的 CSV 格式的数据文件。

环境准备

在测试库上 copy 表

1
2

skytf=# copy ( select * from skytf.activity limit 10000) to '/home/postgres/script/tf/skytf.activity.csv' ;  
COPY 10000

备注：在公司的测试库上取表 skytf.activity 少量数据，注意格式为 csv。

传送文件
将步骤1产生的文件上传到笔记本虚拟机，因为 PostgreSQL9.1 是装在笔记本虚拟机上。

部署 File_fdw

加载 file_fdw 外部模块

1
2
3
4
5
6
7
8
9

mydb=# create extension file_fdw with schema mydb;  
CREATE EXTENSION
mydb=# /dx  
 List of installed extensions  
 Name | Version | Schema | Description  
----------+---------+------------+-------------------------------------------  
file_fdw | 1.0 | mydb | foreign-data wrapper for flat file access  
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language  
(2 rows)

备注：以 postgres 用户连接虚拟机上的库 mydb, 创建外部模块 file_fdw。

创建 SERVER (外部服务器)

1
2
3
4
5
6
7
8
9

mydb=# create server ser_file FOREIGN DATA WRAPPER file_fdw;  
CREATE SERVER

mydb=# /des  
 List of foreign servers  
 Name | Owner | Foreign-data wrapper  
----------+----------+----------------------  
ser_file | postgres | file_fdw  
(1 row)

注：以 postgres 用户连接虚拟机上的库 mydb, 并创建 SERVER;Server 可以理解为外部服务器，是本地库访问外部数据的桥梁。

创建外部表

1
2
3
4
5
6
7
8
9

mydb=# CREATE FOREIGN TABLE fr_activity (  
mydb(# id character varying(32) NOT NULL,  
mydb(# identity_code integer NOT NULL,  
mydb(# skyid integer NOT NULL,  
mydb(# category character varying(50),  
mydb(# body character varying(500) NOT NULL,  
mydb(# create_time timestamp without time zone  
mydb(# ) server ser_file options (filename '/home/postgres/script/tf/skytf.activity.csv', format 'csv') ;  
CREATE FOREIGN TABLE

备注：这里创建外部表，并指定外部 Server 为 ser_file，在测试过程中，发现外部表有很多限制, 目前发现了以下：

• default 值不支持；

• 索引不支持；

  到了这步，终于完成了外部表创建的所有步骤，接下来验证下是否真的可以访问操作系统文件数据。

数据查询

1
2
3
4
5

mydb=# select * from fr_activity limit 1;  
 id | identity_code | skyid | category | body | create_time  
----------------------------------+---------------+-----------+----------------+--------------+-------------------------  
000000002a3554d5012a5518bf1f65d7 | -472428551 | 133257042 | SKY_VISIT_FROM | ${fromSkyid} | 2010-08-09 12:25:39.106  
(1 row)

备注： 果然文件’/home/postgres/script/tf/skytf.activity.csv’ 数据可以直接访问了。

执行计划

1
2
3
4
5
6
7
8

mydb=# explain select count(*) from fr_activity;  
 QUERY PLAN  
----------------------------------------------------------------------------  
Aggregate (cost=6429.76..6429.77 rows=1 width=0)  
 -> Foreign Scan on fr_activity (cost=0.00..6277.30 rows=60983 width=0)  
 Foreign File: /home/postgres/script/tf/skytf.activity.csv  
 Foreign File Size: 1463602  
(4 rows)

备注：外部表的PLAN里显示了外部文件的信息，包括文件路径，大小等。

附一: CREATE SERVER 语法

CREATE SERVER
Name
CREATE SERVER – define a new foreign server
Synopsis
CREATE SERVER server_name [ TYPE ‘server_type’ ] [ VERSION ‘server_version’ ]
FOREIGN DATA WRAPPER fdw_name
[ OPTIONS ( option ‘value’ [, … ] ) ]
Description
CREATE SERVER defines a new foreign server. The user who defines the server becomes its owner.
A foreign server typically encapsulates connection information that a foreign-data wrapper uses to access an external data resource. Additional user-specific connection information may be specified by means of user mappings.
The server name must be unique within the database.
Creating a server requires USAGE privilege on the foreign-data wrapper being used.

附二: CREATE FOREIGN TABLE 语法

CREATE FOREIGN TABLE
Name
CREATE FOREIGN TABLE – define a new foreign table
Synopsis
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ NULL | NOT NULL ] }
[, … ]
] )
SERVER server_name
[ OPTIONS ( option ‘value’ [, … ] ) ]
Description
CREATE FOREIGN TABLE will create a new foreign table in the current database. The table will be owned by the user issuing the command.