MED介绍

SQL/MED

SQL/MED介绍

SQL/MED是SQL语言中管理外部数据的一个扩展标准。MED是英文" Management of External Data"的缩写。这个扩展定义在SQL:2003标准的"ISO/IEC 9075-9:2003"中。它通过定义一个外部数据包装器和数据连接类型来管理外部数据。

PostgreSQL从9.1版开始提供了对SOL/MED标准的支持，通过SQL/MED可以连接到各种异构数据库或其他PostgreSQL数据库。SOL/MED可以让开发人员简化应用的架构，减少开发代价。

 

如果没有SOL/MED，数据库无法互联，应用必须和各种不同的数据库连接，应用与数据库的连接也很复杂，他们需要与各种不同的数据库交互，开发较困难，配置较复杂

 

使用SQL/MED后，PG可通过SQL/MED访问其他各种异构数据库或其他PG，应用程序只需要与这一台PG数据库交互，大大简化了应用架构，提高开发效率

 

SQL/MED相当于一套连接其他数据库源的框架和标准。第三方可以根据PG提供的外部数据源开发各种插件来连接其他数据库。各种常见的数据库或NoSQL产品基本上都有第三方的FDW插件

在SQL/MED标准中，有以下四类数据库对象来访问外部数据源

*Foreign Data Wrapper：外部数据包装器，可以缩写为"FDW"，相当于定义外部数据驱动

*Server：外部数据服务器，相当于定义一个外部数据源，需要指定外部数据源的Foreign Data Wrapper

*User mapping：用户映射，主要把外部数据源的用户映射到本地用户，用于控制权限

*Foreign Table：外部表，把外部数据源映射成数据库中的一张外部表

 

外部数据包装器对象---FDW

创建FDW时需要指定一个函数，这个函数定义了PostgreSQL数据库如何从外部数据源取得数据，它是使用C语言写的PostgreSQL扩展函数，返回类型为fdw_handler，在创建FDW时也可以指定一个可选的校验函数和一些参数，校验函数可以检查User Mapping、Server和FDW的参数

 

对于handle函数，有如下三点要求：

*必须是用C语言写的扩展函数

*不能有参数

*必须返回"fdw_handler"类型

 

validator函数的要求如下：

*必须有两个参数

*第一个参数的类型必须是[text]，表示要校验的可选参数

*第二个参数的类型必须是oid，指定可选参数的分类，分类为“server”、“usermapping”、“FDW”、“Table”

 

创建外部数据包装器的语法

CREATE FOREIGN DATA WRAPPER name

[ HANDLER handler_function | NO HANDLER ]

[ VALIDATOR validator_function | NO VALIDATOR ]

[ OPTIONS ( option 'value' [, ... ] ) ]

 

语法说明如下：

*name：指定要创建的外部包装器的名称

*NO HANDLER：此选项可以创建一个无handle函数的外部包装器，但是这会导致使用此包装器的外部表只能声明，不能被访问

*NO VALIDATOR：此选项可以创建一个无校验函数的外部包装器。无校验函数的包装器在创建时不对选项进行检查

*OPTIONS ( option 'value' [, .. ])：指定一些参数，参数名称必须是唯一的

 

注意：外部数据包装器需要由超级用户创建，其他用户没有创建权限，但超级用户创建后，其他用户可以使用它，赋权给其他用户的方法：

GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw ro user01;

 

"create extension"命令会自动创建外部数据包装器,如下:

create extension file_fdw;

CREATE EXTENSION postgres_fdw;

这样就创建好外部数据包装器file_fdw和postgres_fdw了，不需要再运行" CREATE FOREIGN DATA WRAPPER"命令了

 

外部服务器对象---server对象

server对象是把FDW与连接外部数据源的连接参数关联起来的对象，主要定义如何连接外部数据源

 

创建server对象的语法

CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]

FOREIGN DATA WRAPPER fdw_name

[ OPTIONS ( option 'value' [, ... ] ) ]

语法说明：

*server_name：外部Server的名称

*server_type：可选项，指定外部服务器的类型，是否使用此选项与具体的外部数据包装器有关，如果外部数据包装器没有此选项，则不需要定义此选项

*server_version：外部服务器的版本，也与具体的外部数据包装器有关

*fdw_name：指定此外部服务器的外部数据包装器

*OPTIONS ( option 'value' [, .. ] ) ：这些选项主要用于连接外部数据源，如连接外部数据源的IP地址、端口及其他一些参数，也与具体的外部数据包装器有关

 

用户映射对象

用户映射主要解决PostgreSQL用户与外部服务器的用户之间的映射关系

 

创建用户映射的语法：

CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }

SERVER server_name

[ OPTIONS ( option 'value' [ , ... ] ) ]

语法说明如下：

*user_name：代表本地PostgreSQL数据库的用户，如果为 "CURRENT_USER" 或"USER"则代表当前的用户。当声明PUBLIC时，一个所谓的公共映射就创建了，当没有特定用户的映射时就会使用该公共映射

*server_name：指定一个服务名称，就是前面用"CREATE SERVER"命令创建的名称

*OPTIONS ( option 'value' [, .. ]) ：该选项通常定义映射的远程数据源上的实际用户名和密码。选项名称必须是唯一的。具体允许哪些选项是由外部数据包装器决定的

 

外部表对象

实际上SOL/MED就是把外部数据源中的数据对象映射成一张外部表，然后就可以像访问普通表一样访问这张外部表了

 

创建外部表的语法：

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [

column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]

[, ... ]

] )

SERVER server_name

[ OPTIONS ( option 'value' [, ... ] ) ]

创建外部表的语法与创建本地表的语法类似，定义的列也可以加上一些列的约束：

[ CONSTRAINT constraint_name ]

{ NOT NULL |

NULL |

DEFAULT default_expr }