Foreign data wrappers

Prerequisites

Before you begin, make sure you have:

• A PolarDB for PostgreSQL (Compatible with Oracle) cluster

• A geospatial file (such as a shapefile) stored in an OSS bucket in the same region as your cluster

• The AccessKey ID and AccessKey secret for accessing the OSS bucket

Install and register

1. Install the ganos_fdw extension:

   CREATE EXTENSION ganos_fdw WITH SCHEMA public CASCADE;

   Install into the public schema to avoid permission issues.

2. Register a geospatial file as a foreign table using ST_RegForeignTables:

   -- Register a shapefile stored in OSS as a foreign table
   SELECT ST_RegForeignTables('OSS://<AccessKey_ID>:<AccessKey_secret>@<internal_endpoint>/bucket/path/poly.shp');

   Replace <internal_endpoint> with the internal OSS endpoint for your region. For endpoint formats, see OSS domain names.

   Important

   Always use the internal OSS endpoint. For more information, see OSS domain names.

3. Confirm the foreign table was registered:

   SELECT foreign_table_name
   FROM information_schema.foreign_tables
   ORDER BY foreign_table_name ASC;

Query and import data

Query the foreign table directly with standard SQL:

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

Expected output:

1 | POLYGON((5 0,0 0,0 10,5 10,5 0)) | ZhangShan |  35 |   1.84

To import the data into a local table:

-- Create a local table with the same schema and data as the foreign table
CREATE TABLE poly_db AS
SELECT * FROM poly;

-- If the local table already exists, append the data
INSERT INTO poly_db
SELECT * FROM poly;

Remove the extension

DROP EXTENSION ganos_fdw CASCADE;

Step 1: Create a foreign server

CREATE SERVER <server_name>
FOREIGN DATA WRAPPER ganos_fdw
OPTIONS (
    datasource 'OSS://<internal_endpoint>/path/file',
    format '<driver>',
    open_options '<config>=<value>[ <config>=<value>]',
    config_options '<config>=<value>[ <config>=<value>]'
);

The datasource endpoint must be the internal OSS endpoint for the region where your cluster and OSS bucket reside. For endpoint formats, see OSS domain names.

Example — create a server for a shapefile with Latin-1 encoding:

CREATE SERVER myserver
FOREIGN DATA WRAPPER ganos_fdw
OPTIONS (
    datasource 'OSS://<internal_endpoint>/path/poly.shp',
    format 'ESRI Shapefile',
    open_options 'SHAPE_ENCODING=LATIN1',
    config_options ''
);

Step 2: Create a user mapping

Create a user mapping to provide your OSS credentials:

CREATE USER MAPPING
FOR <user_name>
SERVER <server_name>
OPTIONS (
    user '<AccessKey_ID>',
    password '<AccessKey_secret>'
);

Example:

CREATE USER MAPPING
FOR CURRENT_USER
SERVER myserver
OPTIONS (
    user 'id',
    password 'secret'
);

Step 3: Create a foreign table or import the schema

You have two options for defining the foreign table schema.

Option A: Import the schema automatically (recommended)

Use IMPORT FOREIGN SCHEMA to let GanosBase FDW detect layer definitions automatically:

IMPORT FOREIGN SCHEMA ganos_fdw
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER <server_name>
    INTO <local_schema>;

The schema name in IMPORT FOREIGN SCHEMA must be ganos_fdw.

Example — import all layers from myserver into the imp schema:

CREATE SCHEMA imp;

IMPORT FOREIGN SCHEMA ganos_fdw
    FROM SERVER myserver
    INTO imp;

Example — import only the cities layer:

IMPORT FOREIGN SCHEMA ganos_fdw
    LIMIT TO (cities)
    FROM SERVER myserver
    INTO imp;

Example — import all layers except countries:

IMPORT FOREIGN SCHEMA ganos_fdw
    EXCEPT (countries)
    FROM SERVER myserver
    INTO imp;

Option B: Define the foreign table manually

If you know the exact column names and data types, define the table directly:

CREATE FOREIGN TABLE <table_name> (
    column_name data_type
    [, ...]
) SERVER <server_name>
OPTIONS (layer '<layer_name>');

Example:

CREATE FOREIGN TABLE example_table (
    fid    bigint,
    name   varchar,
    age    varchar,
    value  varchar
) SERVER myserver
OPTIONS (layer 'poly');

Tip: When the column names or data types are uncertain, use IMPORT FOREIGN SCHEMA (Option A) to avoid schema mismatches.