The Ganos_FDW extension follows the foreign data wrapper (FDW) specifications. You can use SQL statements to create foreign tables. You can also use the foreign tables that you create to manage external data sources.
Procedure
- Define a server. Syntax:
CREATE SERVER <server_name> FOREIGN DATA WRAPPER ganos_fdw OPTIONS ( datasource 'OSS://<endpoint>/path/file', format '<driver>', open_options '<config>=<value>[ <config>=<value>]', config_options '<config>=<value>[ <config>=<value>]');
The following table describes the parameters in the CREATE SERVER statement. For more information, see CREATE SERVER.Parameter Description datasource The data source that you want to access. For more information, see Object storage paths. format The driver that is used to access the data source. For more information, see ST_FDWDrivers. If you do not specify this parameter, the default driver is used. config_options The environment variables that you want to configure. open_options The options based on which the data source is accessed. Example:CREATE SERVER myserver FOREIGN DATA WRAPPER ganos_fdw OPTIONS ( datasource 'OSS://<endpoint>/path/poly.shp', format 'ESRI Shapefile', open_options 'SHAPE_ENCODING=LATIN1', config_options '');
- Create a user mapping. Syntax:
CREATE USER MAPPING FOR <user_name> SERVER <server_name> OPTIONS ( user '<oss_ak_id>', password '<oss_ak_secret>');
The following table describes the parameters in the CREATE USER MAPPING statement. For more information, see CREATE USER MAPPING.Parameter Description SERVER The name of the server. The value of this parameter is the same as the value of the server_name parameter in the CREATE SERVER statement that you execute in Step 1. user The AccessKey ID of the AccessKey pair. For more information, see Obtain an AccessKey pair. password The AccessKey secret of the AccessKey pair. Example:CREATE USER MAPPING FOR CURRENT_USER SERVER myserver OPTIONS ( user 'id', password 'secret');
- Create a foreign table. Syntax:
CREATE FOREIGN TABLE <table_name> ( column_name data_type [, ...] ) SERVER <server_name> OPTIONS (layer '<layer_name>');
The following table describes the parameters in the CREATE FOREIGN TABLE statement. For more information, see CREATE FOREIGN TABLE.Parameter Description SERVER The name of the server. The value of this parameter is the same as the value of the server_name parameter in the CREATE SERVER statement that you execute in Step 1. layer The name of the layer that is associated with the foreign table. Example:CREATE FOREIGN TABLE example_table ( fid bigint, name varchar, age varchar, value varchar ) SERVER myserver OPTIONS (layer 'poly');
- Import the definition of the foreign table. Syntax:
IMPORT FOREIGN SCHEMA ganos_fdw [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ] FROM SERVER <server_name> INTO <local_schema>
Example:CREATE SCHEMA imp; IMPORT FOREIGN SCHEMA ganos_fdw FROM SERVER myserver INTO imp;
Note- If you use a remote schema, the value of the SCHEMA parameter is fixed as ganos_fdw.
- You can execute the CREATE SCHEMA statement to create a local schema.
- For more information about the parameters in the IMPORT FOREIGN SCHEMA statement, see IMPORT FOREIGN SCHEMA.