Updated on 2023-10-23 GMT+08:00

ALTER DATA SOURCE

Function

ALTER DATA SOURCE modifies the attributes and content of the data source.

The attributes include the name and owner. The content includes the type, version, and connection options.

Precautions

  • Only the initial user, system administrator, and owner have the permission to modify data sources.
  • To change the owner, the new owner must be the initial user or a system administrator.
  • If the password option is displayed, ensure that the datasource.key.cipher and datasource.key.rand files exist in the $GAUSSHOME/bin directory of each node in the database. If the two files do not exist, use the gs_guc tool to generate them and use the gs_ssh tool to release them to the $GAUSSHOME/bin directory on each node.

Syntax

ALTER DATA SOURCE src_name
    [TYPE  'type_str']
    [VERSION  {'version_str' | NULL}]
    [OPTIONS ( {[ ADD | SET | DROP ] optname ['optvalue']} [, ...] )];
ALTER DATA SOURCE src_name RENAME TO src_new_name;
ALTER DATA SOURCE src_name OWNER TO new_owner;

Parameter Description

  • src_name

    Specifies the data source name to be modified.

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

  • TYPE

    Changes the original TYPE value of the data source to the specified value.

    Value range: an empty string or a non-empty string

  • VERSION

    Changes the original VERSION value of the data source to the specified value.

    Value range: an empty string, a non-empty string, or null

  • OPTIONS

    Specifies the column to be added, modified, or deleted. The value of optname should be unique. Comply with the following rules to set this parameter:

    To add a column, you can omit ADD and simply specify the column name, which cannot be an existing column name.

    To modify a column, specify SET and an existing column name.

    To delete a column, specify DROP and an existing column name. Do not set optvalue.

  • src_new_name

    Specifies the new data source name.

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

  • new_user

    Specifies the new owner of an object.

    Value range: a string. It must be a valid username.

Examples

-- Create an empty data source.
openGauss=# CREATE DATA SOURCE ds_test1;

-- Rename the data source.
openGauss=# ALTER DATA SOURCE ds_test1 RENAME TO ds_test;

-- Change the owner.
openGauss=# CREATE USER user_test1 IDENTIFIED BY 'Gs@123456';
openGauss=# ALTER USER user_test1 WITH SYSADMIN;
openGauss=# ALTER DATA SOURCE ds_test OWNER TO user_test1;

-- Modify TYPE and VERSION.
openGauss=# ALTER DATA SOURCE ds_test TYPE 'MPPDB_TYPE' VERSION 'XXX';

-- Add a column.
openGauss=# ALTER DATA SOURCE ds_test OPTIONS (add dsn 'gaussdb', username 'test_user');

-- Modify a column.
openGauss=# ALTER DATA SOURCE ds_test OPTIONS (set dsn 'unknown');

-- Delete a column.
openGauss=# ALTER DATA SOURCE ds_test OPTIONS (drop username);

-- Delete the data source and user objects.
openGauss=# DROP DATA SOURCE ds_test;
openGauss=# DROP USER user_test1;