Quick start for FDW references - ApsaraDB RDS - Alibaba Cloud Documentation Center

A foreign data wrapper (FDW) is a PostgreSQL extension that lets you query data from external sources as if they were local tables. In GanosBase, the FDW extension adds spatial data support: it automatically maps geometric spatial data types to the Geometry type, so you can run spatial queries across both foreign tables and local tables in the same statement.

This guide shows you how to register an OSS shapefile as a foreign table and query it from your PolarDB for PostgreSQL cluster.

How it works

GanosBase FDW uses a three-layer model:

Extension — installs the FDW capability into your database (ganos_fdw, plus its dependencies ganos_spatialref and ganos_geometry)
Foreign server — a named connection object that points to the external data source (in this case, your Object Storage Service (OSS) bucket)
Foreign table — a virtual table mapped to a specific file or dataset on the foreign server; it has no local storage but behaves like a regular table in queries

Prerequisites

Before you begin, make sure you have:

A PolarDB for PostgreSQL cluster running GanosBase
An OSS bucket containing a shapefile; the shapefile must include at least three files — .shp, .shx, and .dbf — uploaded to the same folder
The OSS bucket and your PolarDB cluster in the same region, connected via the internal endpoint
An AccessKey pair (AccessKey ID and AccessKey secret) with read access to the OSS bucket. To get an AccessKey pair, see Obtain an AccessKey pair

Set up and query a foreign table

Step 1: Install the Ganos_FDW extension

Choose one of the following methods.

Method 1 (recommended): Install with CASCADE

The CASCADE option automatically installs the required dependencies (ganos_spatialref and ganos_geometry):

CREATE EXTENSION ganos_fdw WITH schema public CASCADE;

Install the extension in the public schema to avoid permissions issues.

Method 2: Install dependencies manually

If you need to control the installation order, run the three commands in sequence:

CREATE EXTENSION ganos_spatialref;
CREATE EXTENSION ganos_geometry;
CREATE EXTENSION ganos_fdw;

Step 2: Register a shapefile as a foreign table

2a. Register the shapefile

Use ST_RegForeignTables to register your OSS shapefile as a foreign table. Replace the placeholders with your actual values:

SELECT ST_RegForeignTables('OSS://<access_id>:<secret_key>@[<endpoint>]/<bucket>/path/to/file');

Placeholder	Description
`<access_id>`	Your AccessKey ID
`<secret_key>`	Your AccessKey secret
`<endpoint>`	The internal OSS endpoint for your region. For endpoint formats, see OSS domain names
`<bucket>/path/to/file`	The path to the shapefile directory in your OSS bucket

Use the internal OSS endpoint to ensure connectivity between your PolarDB cluster and the OSS bucket. Both must be in the same region.

2b. Verify the foreign table was registered

Query the information_schema.foreign_tables view to confirm the registration:

SELECT foreign_table_name FROM information_schema.foreign_tables ORDER BY foreign_table_name ASC;

Step 3: Query the foreign table

Run a spatial query against the foreign table. The following example retrieves the geometry and attributes for a specific record:

SELECT fid, ST_AsText(geom), name, age, height FROM poly WHERE fid = 1;

Expected output:

 fid  | ST_AsText(geom)                  | name      | age | height
------+----------------------------------+-----------+-----+--------
    1 | POLYGON((5 0,0 0,0 10,5 10,5 0)) | ZhangShan |  35 |   1.84

Step 4: Import the foreign table data into a local table

Choose the method that matches your situation.

If no local table exists

Create a local table and populate it from the foreign table in one step:

CREATE TABLE poly_db AS SELECT * FROM poly;

If a local table already exists

Choose either method to import the data:

INSERT INTO ... SELECT — append rows from the foreign table into the existing local table:
```
INSERT INTO poly_db SELECT * FROM poly;
```

IMPORT FOREIGN SCHEMA — import the entire foreign schema into a local schema:

CREATE SCHEMA imp;

IMPORT FOREIGN SCHEMA ganos_fdw
  FROM SERVER ganos_fdw_server
  INTO imp;

Step 5 (Optional): Remove the extension

To uninstall Ganos_FDW and drop all dependent objects, run:

DROP EXTENSION Ganos_FDW CASCADE;

Warning

CASCADE removes all foreign tables and other objects that depend on the extension. Make sure you no longer need them before running this command.