All Products
Search
Document Center

ApsaraDB RDS:ST_RegForeignTables

Last Updated:Jun 17, 2024

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