POSTGRESQL9.1 版本支持了外表,今天进行一个简单的测试:
1. 导入外表插件:
pgdb1=# create extension file_fdw;
CREATE EXTENSION
create extension参见手册: http://www.postgresql.org/docs/9.1/static/sql-createextension.html
特别注意,在create extension要安装了contrib下面程序
2. 建立文件server:
pgdb1=# create server file_server foreign data wrapper file_fdw;
CREATE SERVER
3.建立外表:
create foreign table text_table(PART_KEY BIGINT,
pgdb1(# USER_KEY BIGINT,
LOG_COUNT BIGINT,
ONLINE_DURATION BIGINT,
pgdb1(# ONLINE_DAYS BIGINT,
pgdb1(# FREQUENT_PERIOD BIGINT,
pgdb1(# MSG_COUNT BIGINT,
pgdb1(# FRIENDS_COUNT BIGINT,
pgdb1(# CONTACT_COUNT BIGINT,
pgdb1(# RECENT_COUNTRY_ID BIGINT,
pgdb1(# RECENT_PROVINCE_ID BIGINT,
pgdb1(# RECENT_CITY_ID BIGINT,
pgdb1(# RECENT_PLACE VARCHAR(1000)
pgdb1(# pgdb1(# pgdb1(# ) server file_server OPTIONS(format 'text',filename '/data/vince/testdata/a.txt',delimiter ',',NULL '');
CREATE FOREIGN TABLE
4. 查看表属性
pgdb1=# \d+ text_table
Foreign table "public.text_table"
Column | Type | Modifiers | Storage | Description
--------------------+-------------------------+-----------+----------+-------------
part_key | bigint | | plain |
user_key | bigint | | plain |
log_count | bigint | | plain |
online_duration | bigint | | plain |
online_days | bigint | | plain |
frequent_period | bigint | | plain |
msg_count | bigint | | plain |
friends_count | bigint | | plain |
contact_count | bigint | | plain |
recent_country_id | bigint | | plain |
recent_province_id | bigint | | plain |
recent_city_id | bigint | | plain |
recent_place | character varying(1000) | | extended |
Server: file_server
Has OIDs: no
5 查询操作
pgdb1=# select count(*) from text_table;
count
--------
100000
(1 row)
6.delete,update操作
update text_table set log_count=0 where user_key=14154;
ERROR: cannot change foreign table "text_table"
delete from text_table where user_key=14154;
ERROR: cannot change foreign table "text_table"
看出外边只能进行查询操作不能修改