ST_RegForeignTables-PolarDB(PolarDB)-阿里云帮助中心

Registers tables from a data source as foreign tables.

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. For more information, see Object Storage Service paths.
driver	The data source driver. You can get the driver using the ST_FDWDrivers function. If you pass an empty string, the function attempts to access the data source using the default driver.
server_name	The name of the foreign server that is automatically created. The default name is `ganos_fdw_server`.
config_option	The environment variable options.
open_option	The options for opening the data source.
tables	The names of the tables to register as foreign tables. You can get the names using the ST_ForeignTables function.
prefix	The prefix for the registered foreign tables.

Description

Registers tables from a data source as foreign tables. You can specify the server name and data source connection information. You can then query the information_schema.foreign_tables view to retrieve related information.

Examples

Specify only the path

SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/data');
--------------
 Create server 'ganos_fdw_server' successfully

Specify the server name

SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/data',
                           'my_server');

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

Specify the driver open options

SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/data',
                           'myserver',
                           'ESRI Shapefile',
                           '',
                           'SHAPE_ENCODING=LATIN1');

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

Specify the tables to register

SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/data',
                           'myserver',
                           'ESRI Shapefile',
                           '',
                           'SHAPE_ENCODING=LATIN1',
                           ARRAY['point', 'roads']::cstring[]);

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

Specify the foreign table prefix

SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/data',
                           'myserver',
                           'ESRI Shapefile',
                           '',
                           'SHAPE_ENCODING=LATIN1',
                           ARRAY['point', 'roads']::cstring[],
                           'myprefix');

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