SQL/MED是SQL语言中管理外部数据的一个扩展标准,MED=‘Management of External Data’。PgSQL可以通过SQL/MED访问其他各种异构数据库或其他PgSQL。
SQL/MED标准中的四类数据库对象
- Foreign Data Wrapper:外部数据包装器(FDW),相当于定义外部数据驱动;
- Server:外部数据服务器,相当于定义一个外部数据源,需要指定它的FDW;
- User Mapping:用户映射,把Server的用户映射到本地用户,用户控制权限;
- Foreign Table:外部表,把外部数据源应设成数据库中的一张外部表;
FDW对象
handle函数
创建FDW时需要指定一个函数,这个函数定义了PgSQL如何从外部数据源获取数据。
- 必须是C语言写的扩展函数
- 不能有参数
- 必须返回‘fdw_handler’类型
CREATE FUNCTION file_fdw_handler() RETURNS fdw_handler AS 'file_fdw' LANGUAGE C STRICT;
validator函数
也可以指定一个可选的校验函数和一些参数,校验函数可以检查User Mapping、Server和FDW的参数
- 必须有两个参数
- 第一个参数类型必须是text[],表示要校验的可选参数
- 第二个参数类型必须是oid,指定可选参数的分类,分类为‘server’,‘user mapping’,‘FDW’,‘Table’
CREATE FUNCTION file_fdw_validator(text[], oid) RETURNS void AS 'file_fdw' LANGUAGE C STRICT
创建FDW
CREATE FOREIGN DATA WRAPPER name
[ HANDLER handler_function | NO HANDLER ]
[ VALIDATOR validator_function | NO VALIDATOR ]
[ OPTIONS ( option 'value' [, ...] ) ]
- name: 指定要创建的FDW名称
- NO HANDLER:可以创建一个屋handle函数的FDW,但是会导致使用此FDW的外部表只能声明,不能被访问。
- NO VALIDATOR:可以创建一个无校验函数的FDW。
- OPTIONS:指定一些参数,参数名称必须是唯一的。
CREATE FOREIGN DATA WRAPPER file_fdw
HANDLER file_fdw_handler
VALIDATOR file_fdw_validator
注意:FDW需要由超级用户创建,其他用户没有权限,但创建后其他用户可以通过被授权来使用
GRANT USAGE ON FOREIGN DATA WARAPPER postgres_fdw TO user01;
实际上使用 CREATE EXTENSION postgres_fdw创建扩展后,会自动创建FDW
SERVER对象
Server对象是把FDW与连接外部数据源的连接参数关联起来的对象,主要定义如何连接外部数据源。
CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
FOREIGN DATA WRAPPER fdw_name
[ OPTIONS ( option 'value' [, ...] ) ]
- server_name:外部Server的名称
- server_type:可选,指定Server类型,是否使用与具体的FDW有关
- server_version:Server的版本,也与具体的FDW有关
- fdw_name:指定FDW
- OPTIONS:如连接Server的IP、端口及其他一些参数
CREATE SERVER mysql_fdw_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (address '10.0.3.236', port '3306')
一个用户创建的Server,如果想让另一个用户使用,也需要授权
GRANT USAGE ON FOREIGN SERVER mysql_fdw_server TO user02;
User Mapping对象
CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
SERVER server_name
[ OPTIONS ( option 'value' [, ...] ) ]
- user_name:代表本地PgSQL的用户,CURRENT_USER或USER代表当前的用户。当生命为PUBLIC时,一个所谓的公共映射就创建了,当没有特定用户的映射时就会使用公共映射。
- server_name:指定Server的名称
- OPTIONS:通常定义映射的远程数据源上的实际用户名和密码
CREATE USER MAPPING FOR user01
SERVER postgres_fdw_server
OPTIONS (user 'user02', password '123456')
Table对象
创建外部表的语法与创建本地表的语法类似,定义的列也可以加上一些约束
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name([
column_name data_type [ OPTIONS ( option 'value' [, ...] ) ] [ COLLATE collation ] [ colum_constraint [...] ]
[, ...]
])
SERVER server_name
[ OPTIONS ( option 'value' [, ...] ) ]
CREATE FOREIGN TABLE fttest(
id int,
note test
)
SERVER postgres_fdw_server
OPTIONS (table_name 'test01')