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 defautl '',
                            cstring[] tables default NULL,
                            cstring prefix default '' );

Parameters

Parameter Description
source The data source that contains the table. The data source must point to a valid Object Storage Service (OSS) endpoint.
The OSS endpoint must be in the following format:
OSS://<ak_id>:<ak_secret>@<endpoint>/path/file

The ak_id and ak_secret parameters specify the AccessKey ID and AccessKey secret of the AccessKey pair that is used to access your OSS bucket. For more information, see Obtain an AccessKey pair. The endpoint parameter specifies the region-specific OSS endpoint that is used to access your bucket. To ensure data accessibility, make sure that your RDS instance and your OSS bucket reside in the same region and are connected by using the internal OSS endpoint. For more information, see OSS endpoints.

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 OSS endpoint. 
    SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/data');
--------------
 Create server 'ganos_fdw_server' successfully
  • Specify the server_name parameter.
    SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/data',
                           'my_server');

-------------
 Create server 'my_server' successfully
  • Specify the open_option parameter.
    SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/data',
                           '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://<ak_id>:<ak_secret>@<endpoint>/data',
                           '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://<ak_id>:<ak_secret>@<endpoint>/data',
                           'myserver',
                           'ESRI Shapefile',
                           '',
                           'SHAPE_ENCODING=LATIN1',
                           ARRAY['point', 'roads']::cstring[],
                           'myprefix');

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