ALTER DATA SOURCE

Function

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

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 cluster. 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 in the cluster.

Syntax

1
2
3
4
5
6

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 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.

• new_user

  Specifies the new owner of an object.

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

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Create an empty data source object.
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 'mppdb', 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;

Helpful Links

CREATE DATA SOURCE and DROP DATA SOURCE