ALTER FOREIGN TABLE (SQL on other GaussDB(DWS))

Function

ALTER FOREIGN TABLE modifies a foreign table in associated analysis.

Precautions

None

Syntax

• Set a foreign table's attributes:

  1 2	ALTER FOREIGN TABLE [ IF EXISTS ] tablename OPTIONS ( {[ SET ] option ['value']} [, ... ]);

• Set the owner of the foreign table:

  1 2	ALTER FOREIGN TABLE [ IF EXISTS ] tablename OWNER TO new_owner;

• Update the type of a foreign table column:

  1 2	ALTER FOREIGN TABLE [ IF EXISTS ] table_name MODIFY ( { column_name data_type [, ...] } );

• Modify the column of the foreign table:

  1 2	ALTER FOREIGN TABLE [ IF EXISTS ] tablename action [, ... ];

  The action syntax is as follows:

  1 2	ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type | MODIFY column_name data_type

  For details, see ALTER TABLE.

Parameter Description

• IF EXISTS

  Sends a notification instead of an error if no tables have identical names. The notification prompts that the table you are querying does not exist.

• tablename

  Specifies the name of an existing foreign table to be modified.

  Value range: an existing foreign table name

• new_owner

  Specifies the new owner of the foreign table.

  Value range: a string indicating a valid user name

• data_type

  Specifies the new type for an existing column.

  Value range: a string compliant with the identifier naming rules

• column_name

  Specifies the name of an existing column.

  Value range: a string. It must comply with the naming convention.

For details on how to modify other parameters in the foreign table, see Parameter Description in ALTER TABLE.

Example:

1. Create a foreign server named server_remote. The corresponding foreign data wrapper is GC_FDW.

   1	CREATE SERVER server_remote FOREIGN DATA WRAPPER GC_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000',dbname 'test', username 'test', password '{Password}');

2. Create a foreign table named region.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   DROP FOREIGN TABLE IF EXISTS region; CREATE FOREIGN TABLE region ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER server_remote OPTIONS ( schema_name 'test', table_name 'region', encoding 'gbk' );

3. Modify the region option of the foreign table.

   1	ALTER FOREIGN TABLE region OPTIONS (SET schema_name 'test');

4. Change the type of the r_name column to text in the region foreign table.

   1	ALTER FOREIGN TABLE region ALTER r_name TYPE TEXT;

Helpful Links

DROP FOREIGN TABLE, CREATE FOREIGN TABLE (SQL on other GaussDB(DWS))