Register OSS Foreign Tables with ST_RegForeignTables - PolarDB

Registers one or more tables from a data source as foreign tables in a single call, automatically creating the foreign server if needed.

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 path. For supported path formats, see Object storage paths.
`server_name`	The name of the foreign server to create. Default: `ganos_fdw_server`.
`driver`	The driver used to read the data source. If omitted, the default driver is used. For supported drivers, see ST_FDWDrivers.
`config_option`	The environment variables that you want to configure.
`open_option`	The options based on which the data source is accessed. For example, pass `SHAPE_ENCODING=LATIN1` to handle Shapefiles that use LATIN1 encoding.
`tables`	An array of table names to register as foreign tables. Use ST_ForeignTables to list available table names before calling this function.
`prefix`	A prefix to prepend to each foreign table name. Useful for avoiding naming conflicts when registering tables from multiple data sources.

Description

ST_RegForeignTables wraps the full Foreign Data Wrapper (FDW) setup into a single function call: it creates a foreign server with the specified name and then registers the target tables as foreign tables. The return value is a confirmation message in the format Create server '<server_name>' successfully.

After registration, query information_schema.foreign_tables to verify which foreign tables were created.

Examples

Register all tables using the default server name

Omit server_name to use the default server name ganos_fdw_server.

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

Output:

 Create server 'ganos_fdw_server' successfully

Register all tables with a custom server name

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

Output:

 Create server 'my_server' successfully

Register Shapefile data with encoding options

Use open_option to set the source encoding when the Shapefile uses a non-UTF-8 character encoding.

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

Output:

 Create server 'myserver' successfully

Register specific tables

Pass a cstring array to register only the named tables instead of all tables in the data source.

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

Output:

 Create server 'myserver' successfully

Register specific tables with a name prefix

Use prefix to prepend a string to each foreign table name, which helps avoid conflicts when registering tables from multiple data sources.

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

Output:

 Create server 'myserver' successfully