Use ST_RegForeignTables to register a data source table as a foreign table - ApsaraDB RDS

Registers one or more tables from an external data source as PostgreSQL foreign tables, creating a foreign server in a single call.

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	Required	Default	Description
`source`	Yes	—	The data source path. For supported path formats, see Object storage paths.
`server_name`	No	`ganos_fdw_server`	The name of the foreign server to create. If omitted, the server is named `ganos_fdw_server`.
`driver`	No	Default driver	The driver for accessing the data source. To list available drivers, call ST_FDWDrivers.
`config_option`	No	`''`	Environment variables to configure for the data source connection.
`open_option`	No	`''`	Driver-specific options for opening the data source. For example, pass `SHAPE_ENCODING=LATIN1` for ESRI Shapefiles with Latin-1 encoding.
`tables`	No	`NULL`	The names of the tables to register. To list available table names, call ST_ForeignTables.
`prefix`	No	`''`	A prefix to prepend to each foreign table name. Use this to avoid name conflicts when registering tables from multiple data sources.

Description

ST_RegForeignTables wraps the PostgreSQL foreign data wrapper (FDW) workflow—creating a foreign server and registering foreign tables—into a single function call. It reads the specified data source, creates a foreign server with the given name, and registers the discovered tables as foreign tables in the current database.

After registration, query information_schema.foreign_tables to confirm which tables were created and their names.

Examples

All examples use an Object Storage Service (OSS) path as the data source. Replace <access_id>, <secret_key>, <Endpoint>, <bucket>, and path_to/file with your actual values.

Register all tables with the default server name

When no custom server name is needed, omit server_name. The function creates a foreign server named ganos_fdw_server automatically.

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

 Create server 'ganos_fdw_server' successfully

Register all tables with a custom server name

When registering multiple data sources, use distinct server names to keep them separate.

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

 Create server 'my_server' successfully

Specify a driver and open option

When the data source requires a specific driver or encoding, set driver and open_option. This example registers an ESRI Shapefile directory with Latin-1 encoding.

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

 Create server 'myserver' successfully

Register a subset of tables

When a data source contains multiple tables but you only need some of them, pass the target table names as an array. To discover available table names first, call ST_ForeignTables.

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

 Create server 'myserver' successfully

Add a prefix to foreign table names

When foreign table names might conflict with existing tables, use prefix to namespace them.

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

 Create server 'myserver' successfully

Verify registration

After calling ST_RegForeignTables, confirm the foreign tables were created by querying information_schema.foreign_tables.

What's next

Call ST_ForeignTables to list all available tables in a data source before registering
Call ST_FDWDrivers to list available drivers for your data source format