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

Lists the tables available in an external data source, such as an OSS bucket containing spatial data files.

Syntax

setof record ST_ForeignTables(cstring source,
                              cstring driver default '',
                              out integer id,
                              out cstring table_name);

Parameters

Parameter	Description
`source`	The external data source to inspect. Specify an OSS path in the format `OSS://<ak_id>:<ak_secret>@<endpoint>/<path>`. For supported path formats, see Object storage paths.
`driver`	The driver used to read the data source. If left blank, the default driver is used. For a list of supported drivers, see ST_FDWDrivers.
`id`	Output. The numeric ID assigned to the table within the data source.
`table_name`	Output. The name of the table.

Examples

Because ST_ForeignTables returns a setof record, wrap the call in a subquery and use .* to expand the composite output before selecting individual fields.

List all layers using the default driver

SELECT
    table_name
FROM
    (SELECT (ST_ForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/data')).*) table_test
ORDER BY table_name::text ASC;

Output:

 -----------------
 county
 poi
 road

List layers matched by the ESRI Shapefile driver

Specifying a driver filters results to the layers that the driver can read. In this example, the ESRI Shapefile driver matches two of the three layers in the same data source.

SELECT
    table_name
FROM
    (SELECT (ST_ForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/data', 'ESRI Shapefile')).*) table_test
ORDER BY table_name::text ASC;

Output:

 -----------------
 poi
 road