postgresql建立外表

来源：互联网发布：ssm 打印sql 编辑：程序博客网时间：2024/05/17 09:22

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_serverHas 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"

看出外边只能进行查询操作不能修改