ST_RegForeignTables - ApsaraDB RDS - Alibaba Cloud Documentation Center

This topic describes the ST_RegForeignTables function. This function is used to register a table in a data source as a foreign table.

Syntax

cstring ST_RegForeignTables(cstring source,
                            cstring server_name default '',
                            cstring driver default '',
                            cstring config_option default '',
                            cstring open_option default '',
                            cstring[] tables default NULL,
                            cstring prefix default '' );

Parameters

Parameter	Description
source	The data source that contains the table. For more information, see Object storage paths.
driver	The driver that is used to access the data source. For more information, see ST_FDWDrivers. If you do not specify this parameter, the default driver is used.
server_name	The name of the foreign server. The name is automatically created. Default value: `ganos_fdw_server`.
config_option	The environment variables that you want to configure.
open_option	The options based on which the data source is accessed.
tables	The name of the table that you want to register as a foreign table. You can use the ST_ForeignTables function to query table names.
prefix	The prefix in the name of the foreign table.

Description

You can use this function to register a table in a data source as a foreign table. You can specify the server name and the data source. You can obtain the information that you need by using the information_schema.foreign_tables view.

Examples

Specify only the Object Storage Service (OSS) endpoint.

SELECT ST_RegForeignTables('OSS://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file');
--------------
 Create server 'ganos_fdw_server' successfully

Specify the server_name parameter.

SELECT ST_RegForeignTables('OSS://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file',
                           'my_server');

-------------
 Create server 'my_server' successfully

Specify the open_option parameter.

SELECT ST_RegForeignTables('OSS://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file',
                           'myserver',
                           'ESRI Shapefile',
                           '',
                           'SHAPE_ENCODING=LATIN1');

-------------
 Create server 'myserver' successfully

Specify the table that you want to register as a foreign table.

SELECT ST_RegForeignTables('OSS://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file',
                           'myserver',
                           'ESRI Shapefile',
                           '',
                           'SHAPE_ENCODING=LATIN1',
                           ARRAY['point', 'roads']::cstring[]);

-------------
 Create server 'myserver' successfully

Specify the prefix in the name of the foreign table.

SELECT ST_RegForeignTables('OSS://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file',
                           'myserver',
                           'ESRI Shapefile',
                           '',
                           'SHAPE_ENCODING=LATIN1',
                           ARRAY['point', 'roads']::cstring[],
                           'myprefix');

-------------
 Create server 'myserver' successfully