openGauss高级特性:QL语法（1）

最新推荐文章于 2024-07-23 17:52:34 发布

openGauss小助手

最新推荐文章于 2024-07-23 17:52:34 发布

阅读量399

点赞数 21

文章标签：数据库 openGauss

本文链接：https://blog.csdn.net/weixin_53596073/article/details/138302984

版权

ABORT

退出当前事务。

ABORT [ WORK | TRANSACTION ] ;

ALTER AUDIT POLICY

修改统一审计策略。

ALTER AUDIT POLICY [ IF EXISTS ] policy_name { ADD | REMOVE } { [ privilege_audit_clause ] [ access_audit_clause ] };
ALTER AUDIT POLICY [ IF EXISTS ] policy_name MODIFY ( filter_group_clause );
ALTER AUDIT POLICY [ IF EXISTS ] policy_name DROP FILTER;
ALTER AUDIT POLICY [ IF EXISTS ] policy_name COMMENTS policy_comments;
ALTER AUDIT POLICY [ IF EXISTS ] policy_name { ENABLE | DISABLE };

where privilege_audit_clause can be:
PRIVILEGES { DDL | ALL }

where access_audit_clause can be:
ACCESS { DML | ALL }

where filter_group_clause can be:
FILTER ON { ( FILTER_TYPE ( filter_value [, ... ] ) ) [, ... ] }

where DDL can be:
{ ( ALTER | ANALYZE | COMMENT | CREATE | DROP | GRANT | REVOKE | SET | SHOW | LOGIN_ACCESS | LOGIN_FAILURE | LOGOUT | LOGIN ) }

where DML can be:
{ ( COPY | DEALLOCATE | DELETE_P | EXECUTE | REINDEX | INSERT | REPARE | SELECT | TRUNCATE | UPDATE ) }

ALTER DATA SOURCE

修改Data Source对象的属性和内容。

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;

Valid optname are:
    DSN, USERNAME, PASSWORD, ENCODING

ALTER DATABASE

修改数据库的属性，包括它的名称、所有者、连接数限制、对象隔离属性等。

ALTER DATABASE database_name
    [ [ WITH ] CONNECTION LIMIT connlimit ];
ALTER DATABASE database_name
    RENAME TO new_name;
ALTER DATABASE database_name
    OWNER TO new_owner;
ALTER DATABASE database_name
    SET TABLESPACE new_tablespace;
ALTER DATABASE database_name
    SET configuration_parameter { { TO | = } { value | DEFAULT }  | FROM CURRENT };
ALTER DATABASE database_name
    RESET { configuration_parameter | ALL };
ALTER DATABASE database_name
    [ WITH ] { ENABLE | DISABLE } PRIVATE OBJECT;

ALTER DEFAULT PRIVILEGES

设置应用于将来创建的对象的权限（这不会影响分配到已有对象中的权限）。

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke;

where abbreviated_grant_or_revoke can be:
grant_on_tables_clause
  | grant_on_sequences_clause
  | grant_on_functions_clause
  | grant_on_types_clause
  | grant_on_client_master_keys_clause
  | grant_on_column_encryption_keys_clause
  | revoke_on_tables_clause
  | revoke_on_sequences_clause
  | revoke_on_functions_clause
  | revoke_on_types_clause
  | revoke_on_client_master_keys_clause
  | revoke_on_column_encryption_keys_clause
where grant_on_tables_clause can be:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |
    ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ]
where grant_on_sequences_clause can be:
GRANT { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ]
where grant_on_functions_clause can be:
GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON FUNCTIONS
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ]
where grant_on_types_clause can be:
GRANT { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON TYPES
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ]
where grant_on_client_master_keys_clause can be:
GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON CLIENT_MASTER_KEYS
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ]
where grant_on_column_encryption_keys_clause can be:
GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON COLUMN_ENCRYPTION_KEYS
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ]
where revoke_on_tables_clause can be:
REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |
    ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
where revoke_on_sequences_clause can be:
REVOKE [ GRANT OPTION FOR ]
    { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
where revoke_on_functions_clause can be:
REVOKE [ GRANT OPTION FOR ]
    { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON FUNCTIONS
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
where revoke_on_types_clause can be:
REVOKE [ GRANT OPTION FOR ]
    { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON TYPES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
where revoke_on_client_master_keys_clause can be:
REVOKE [ GRANT OPTION FOR ]
    { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON CLIENT_MASTER_KEYS
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
where revoke_on_column_encryption_keys_clause can be:
REVOKE [ GRANT OPTION FOR ]
    { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON COLUMN_ENCRYPTION_KEYS
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]

ALTER DIRECTORY

对directory属性进行修改。

CREATE [OR REPLACE] DIRECTORY directory_name
AS 'path_name';

ALTER EVENT TRIGGER

修改事件触发器。

ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
ALTER EVENT TRIGGER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER EVENT TRIGGER name RENAME TO new_name

ALTER EXTENSION

修改插件扩展。

ALTER EXTENSION name UPDATE [ TO new_version ];
ALTER EXTENSION name SET SCHEMA new_schema;
ALTER EXTENSION name ADD member_object;
ALTER EXTENSION name DROP member_object;

where member_object is:

  FOREIGN TABLE object_name |
  FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |
  [ PROCEDURAL ] LANGUAGE object_name |
  SCHEMA object_name |
  SERVER object_name |
  TABLE object_name |
  TEXT SEARCH CONFIGURATION object_name |
  TYPE object_name |
  VIEW object_name

ALTER FOREIGN TABLE

对外表进行修改。

1. GDS:
ALTER FOREIGN TABLE [ IF EXISTS ]  table_name
    OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ]);
ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    OWNER TO new_owner;

2. HDFS:
ALTER FOREIGN TABLE [ IF EXISTS ]  table_name
    OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ]);
ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    OWNER TO new_owner;
ALTER FOREIGN TABLE [ IF EXISTS ] table_name
    MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    ADD [CONSTRAINT constraint_name]
        {PRIMARY KEY | UNIQUE} (column_name)
        [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED];
ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    DROP CONSTRAINT constraint_name ;
ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    action [, ... ];

where action can be:
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
|   ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
|   ALTER [ COLUMN ] column_name SET STATISTICS  integer
|   ALTER [ COLUMN ] column_name OPTIONS ( {[ ADD | SET | DROP ] option ['value'] } [, ... ])
|   MODIFY column_name data_type
|   MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
|   MODIFY column_name [ CONSTRAINT constraint_name ] NULL

3. OBS:
ALTER FOREIGN TABLE [ IF EXISTS ]  table_name
    OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ]);
ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    OWNER TO new_owner;
ALTER FOREIGN TABLE [ IF EXISTS ] table_name
    MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    ADD [CONSTRAINT constraint_name]
        {PRIMARY KEY | UNIQUE} (column_name)
        [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED];
ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    DROP CONSTRAINT constraint_name ;
ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    action [, ... ];

where action can be:
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
|   ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
|   ALTER [ COLUMN ] column_name SET STATISTICS  integer
|   ALTER [ COLUMN ] column_name OPTIONS ( {[ ADD | SET | DROP ] option ['value'] } [, ... ])
|   MODIFY column_name data_type
|   MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
|   MODIFY column_name [ CONSTRAINT constraint_name ] NULL

4. GC:
ALTER FOREIGN TABLE [ IF EXISTS ]  tablename
    OPTIONS ( {[ SET ] option ['value']} [, ... ]);
ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    OWNER TO new_owner;
ALTER FOREIGN TABLE [ IF EXISTS ] table_name
    MODIFY ( { column_name data_type [, ...] );
ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    action [, ... ];

where action can be:
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
|   MODIFY column_name data_type

ALTER FUNCTION

修改自定义函数的属性。

ALTER FUNCTION function_name ( [ {[ argmode ] [ argname ] argtype} [, ...] ] )
    action [ ... ] [ RESTRICT ];
ALTER FUNCTION funname ( [ {[ argmode ] [ argname ] argtype} [, ...] ] )
    RENAME TO new_name;
ALTER FUNCTION funname ( [ {[ argmode ] [ argname ] argtype} [, ...] ] )
    OWNER TO new_owner;
ALTER FUNCTION funname ( [ {[ argmode ] [ argname ] argtype} [, ...] ] )
    SET SCHEMA new_schema;

where action can be:
{CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT}
|    {IMMUTABLE | STABLE | VOLATILE}
|    {NOT FENCED | FENCED}
|    [ NOT ] LEAKPROOF
|    {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER}
|    AUTHID { DEFINER | CURRENT_USER }
|    COST execution_cost
|    ROWS result_rows
|    SET configuration_parameter {{ TO | = } { value | DEFAULT }| FROM CURRENT}
|    RESET {configuration_parameter| ALL}

ALTER GROUP

修改一个用户组的属性。

ALTER GROUP group_name
    ADD USER user_name [, ... ];
ALTER GROUP group_name
    DROP USER user_name [, ... ];
ALTER GROUP group_name
   RENAME TO new_name;

ALTER INDEX

修改现有索引的定义。

ALTER INDEX [ IF EXISTS ] index_name
    RENAME TO new_name;
ALTER INDEX [ IF EXISTS ] index_name
    SET TABLESPACE tablespace_name;
ALTER INDEX [ IF EXISTS ] index_name
    SET ( {storage_parameter = value} [, ... ] );
ALTER INDEX [ IF EXISTS ] index_name
    RESET ( storage_parameter [, ... ] ) ;
ALTER INDEX [ IF EXISTS ] index_name
    [ MODIFY PARTITION partition_name ] UNUSABLE;
ALTER INDEX index_name
    REBUILD [ PARTITION partition_name ];
ALTER INDEX [ IF EXISTS ] index_name
    RENAME PARTITION partition_name TO new_partition_name;
ALTER INDEX [ IF EXISTS ] index_name
    MOVE PARTITION index_partition_name TABLESPACE new_tablespace;

ALTER LARGE OBJECT

用于更改一个large object的定义。它的唯一的功能是分配一个新的所有者。

ALTER LARGE OBJECT large_object_oid
    OWNER TO new_owner;

ALTER MASKING POLICY

修改脱敏策略。

ALTER MASKING POLICY policy_name { ADD | REMOVE | MODIFY } masking_actions [, ... ];
ALTER MASKING POLICY policy_name MODIFY ( filter_group_clause );
ALTER MASKING POLICY policy_name DROP FILTER;
ALTER MASKING POLICY policy_name { ENABLE | DISABLE };

where masking_actions can be:
masking_function ON LABEL(label_name [, ... ])

where masking_function can be:
{ maskall | randommasking | creditcardmasking | basicemailmasking | fullemailmasking | shufflemasking | alldigitsmasking | regexpmasking }

where filter_group_clause can be:
FILTER ON { ( FILTER_TYPE ( filter_value [, ... ] ) ) [, ... ] }

ALTER MATERIALIZED VIEW

更改一个现有物化视图的多个辅助属性。

ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
    OWNER TO new_owner;
ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
    RENAME [COLUMN] column_name to new_column_name;
ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
    RENAME TO new_name;

ALTER OPERATOR

修改一个操作符的定义。

ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) OWNER TO new_owner
ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) SET SCHEMA new_schema

ALTER RESOURCE LABEL

修改资源标签。

ALTER RESOURCE LABEL label_name { ADD | REMOVE } label_item_list [, ... ];

where label_item_list can be:
resource_type(resource_path[, ... ])

where resource_type can be:
{ TABLE | COLUMN | SCHEMA | VIEW | FUNCTION }

ALTER RESOURCE POOL

修改一个资源池，指定其他控制组。

ALTER RESOURCE POOL pool_name
    WITH ({MEM_PERCENT=pct | CONTROL_GROUP="group_name" | ACTIVE_STATEMENTS=stmt | MAX_DOP = dop | MEMORY_LIMIT='memory_size' | io_limits=io_limits | io_priority='priority' | nodegroup='nodegroup_name' }[, ... ]);

ALTER ROLE

修改角色属性。

ALTER ROLE role_name [ [ WITH ] option [ ... ] ];
ALTER ROLE role_name
    RENAME TO new_name;
ALTER ROLE role_name [ IN DATABASE database_name ]
    SET configuration_parameter {{ TO | = } { value | DEFAULT }|FROM CURRENT};
ALTER ROLE role_name
    [ IN DATABASE database_name ] RESET {configuration_parameter|ALL};

where option can be:
{CREATEDB | NOCREATEDB}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {AUDITADMIN | NOAUDITADMIN}
    | {SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {USEFT | NOUSEFT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | {PERSISTENCE | NOPERSISTENCE}
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' [ EXPIRED ] | DISABLE | EXPIRED }
    | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' | EXPIRED ] | DISABLE }
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | USER GROUP 'groupuser'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | NODE GROUP logic_cluster_name
    | ACCOUNT { LOCK | UNLOCK }
    | PGUSER

ALTER ROW LEVEL SECURITY POLICY

对已存在的行访问控制策略（包括行访问控制策略的名称，行访问控制指定的用户，行访问控制的策略表达式）进行修改。

ALTER [ ROW LEVEL SECURITY ] POLICY [ IF EXISTS ] policy_name ON table_name RENAME TO new_policy_name

ALTER [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name
    [ TO { role_name | PUBLIC } [, ...] ]
    [ USING ( using_expression ) ]

ALTER SCHEMA

修改模式属性。

ALTER SCHEMA schema_name
    RENAME TO new_name;
ALTER SCHEMA schema_name
    OWNER TO new_owner;
ALTER SCHEMA schema_name {WITH | WITHOUT} BLOCKCHAIN;

ALTER SEQUENCE

修改一个现有的序列的参数。

ALTER SEQUENCE [ IF EXISTS ] name
    [ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE ]
    [ OWNED BY { table_name.column_name | NONE } ];
ALTER SEQUENCE [ IF EXISTS ] name OWNER TO new_owner;

ALTER SERVER

增加、修改和删除一个现有server的参数。已有server可以从pg_foreign_server系统表中查询。

ALTER SERVER server_name [ VERSION 'new_version' ]
    [ OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ] ) ];
ALTER SERVER server_name
    OWNER TO new_owner;
ALTER SERVER server_name
     RENAME TO new_name;

ALTER SESSION

ALTER SESSION命令用于定义或修改那些对当前会话有影响的条件或参数。修改后的会话参数会一直保持，直到断开当前会话。

ALTER SESSION SET
    {{config_parameter { { TO  | =  }  { value | DEFAULT }
      | FROM CURRENT }} | CURRENT_SCHEMA [ TO | = ] { schema | DEFAULT }
      | TIME ZONE time_zone
      | SCHEMA schema
      | NAMES encoding_name
      | ROLE role_name PASSWORD 'password'
      | SESSION AUTHORIZATION { role_name PASSWORD 'password' | DEFAULT }
      | XML OPTION { DOCUMENT | CONTENT }
    } ;
ALTER SESSION SET [ SESSION CHARACTERISTICS AS ] TRANSACTION
    { ISOLATION LEVEL  {  READ COMMITTED  | READ UNCOMMITTED  }  |  { READ ONLY  | READ WRITE  }  } [, ...] ;

ALTER SYNONYM

修改SYNONYM对象的属性。

ALTER SYNONYM synonym_name
    OWNER TO new_owner;

ALTER SYSTEM KILL SESSION

用于结束一个会话。

ALTER SYSTEM KILL SESSION 'session_sid, serial' [ IMMEDIATE ];

ALTER SYSTEM SET

用于设置POSTMASTER、SIGHUP、BACKEND级别的GUC参数。此命令会将参数写入配置文件，不同级别生效方式有所不同。

ALTER SYSTEM SET { GUC_name } TO { GUC_value };

ALTER TABLE

修改表，包括修改表的定义、重命名表、重命名表中指定的列、重命名表的约束、设置表的所属模式、添加/更新多个列、打开/关闭行访问控制开关。

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
    action [, ... ];
ALTER TABLE [ IF EXISTS ] table_name
    ADD ( { column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );
ALTER TABLE [ IF EXISTS ] table_name
    MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
ALTER TABLE [ IF EXISTS ] table_name
    RENAME TO new_table_name;
ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
    RENAME [ COLUMN ] column_name TO new_column_name;
ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
    RENAME CONSTRAINT constraint_name TO new_constraint_name;
ALTER TABLE [ IF EXISTS ] table_name
    SET SCHEMA new_schema;

where action can be:
column_clause
    | ADD table_constraint [ NOT VALID ]
    | ADD table_constraint_using_index
    | VALIDATE CONSTRAINT constraint_name
    | DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    | CLUSTER ON index_name
    | SET WITHOUT CLUSTER
    | SET ( {storage_parameter = value} [, ... ] )
    | RESET ( storage_parameter [, ... ] )
    | OWNER TO new_owner
    | SET TABLESPACE new_tablespace
    | SET {COMPRESS|NOCOMPRESS}
    | TO { GROUP groupname | NODE ( nodename [, ... ] ) }
    | ADD NODE ( nodename [, ... ] )
    | DELETE NODE ( nodename [, ... ] )
    | UPDATE SLICE LIKE table_name
    | DISABLE TRIGGER [ trigger_name | ALL | USER ]
    | ENABLE TRIGGER [ trigger_name | ALL | USER ]
    | ENABLE REPLICA TRIGGER trigger_name
    | ENABLE ALWAYS TRIGGER trigger_name
    | ENABLE ROW LEVEL SECURITY
    | DISABLE ROW LEVEL SECURITY
    | FORCE ROW LEVEL SECURITY
    | NO FORCE ROW LEVEL SECURITY
    | ENCRYPTION KEY ROTATION
where column_clause can be:
ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | MODIFY column_name data_type
    | MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
    | MODIFY column_name [ CONSTRAINT constraint_name ] NULL
    | DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
    | ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
    | ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }
    | ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    | ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer
    | ADD STATISTICS (( column_1_name, column_2_name [, ...] ))
    | DELETE STATISTICS (( column_1_name, column_2_name [, ...] ))
    | ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] )
    | ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    | ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
where column_constraint can be:
[ CONSTRAINT constraint_name ]
    { NOT NULL |
      NULL |
      CHECK ( expression ) |
      DEFAULT default_expr |
      GENERATED ALWAYS AS ( generation_expr ) STORED |
      UNIQUE index_parameters |
      PRIMARY KEY index_parameters |
      ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |
      REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
        [ ON DELETE action ] [ ON UPDATE action ] }
    [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
where compress_mode can be:
{ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }
where table_constraint can be:
[ CONSTRAINT constraint_name ]
    { CHECK ( expression ) |
      UNIQUE ( column_name [, ... ] ) index_parameters |
      PRIMARY KEY ( column_name [, ... ] ) index_parameters |
      PARTIAL CLUSTER KEY ( column_name [, ... ] ) |
      FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
        [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
    [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
where index_parameters can be:
[ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ USING INDEX TABLESPACE tablespace_name ]
where table_constraint_using_index can be:
[ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

ALTER TABLE INHERIT

ALTER TABLE table_name { inherit | no inherit } parent_name;

ALTER TABLE PARTITION

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
    action [, ... ];
ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name  )}
    RENAME PARTITION { partion_name | FOR ( partition_value [, ...] ) } TO partition_new_name;

where action can be:
move_clause  |
    exchange_clause  |
    row_clause  |
    merge_clause  |
    modify_clause  |
    split_clause  |
    add_clause  |
    drop_clause
where move_clause can be:
MOVE PARTITION { partion_name | FOR ( partition_value [, ...] ) } TABLESPACE tablespacename
where exchange_clause can be:
EXCHANGE PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) }
    WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )}
    [ { WITH | WITHOUT } VALIDATION ] [ VERBOSE ]
where row_clause can be:
{ ENABLE | DISABLE } ROW MOVEMENT
where merge_clause can be:
MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name
    [ TABLESPACE tablespacename ]
where modify_clause can be:
MODIFY PARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }
where split_clause can be:
SPLIT PARTITION { partition_name | FOR ( partition_value [, ...] ) } { split_point_clause | no_split_point_clause }
where split_point_clause can be:
AT ( partition_value ) INTO ( PARTITION partition_name [ TABLESPACE tablespacename ] , PARTITION partition_name [ TABLESPACE tablespacename ] )
where no_split_point_clause can be:
INTO {(partition_less_than_item [, ...] ) | (partition_start_end_item [, ...] )}
where add_clause can be:
ADD {partition_less_than_item | partition_start_end_item}
where partition_less_than_item can be:
PARTITION partition_name VALUES LESS THAN ( { partition_value | MAXVALUE }      [, ...] ) [ TABLESPACE tablespacename ]
where partition_start_end_item can be:
PARTITION partition_name {
        {START(partition_value) END (partition_value) EVERY (interval_value)} |
        {START(partition_value) END ({partition_value | MAXVALUE})} |
        {START(partition_value)} |
        {END({partition_value | MAXVALUE})}
} [TABLESPACE tablespace_name]
where drop_clause can be:
DROP PARTITION  { partition_name  | FOR (  partition_value [, ...] )  }

ALTER TABLESPACE

修改表空间的属性。

ALTER TABLESPACE tablespace_name
    RENAME TO new_tablespace_name;
ALTER TABLESPACE tablespace_name
    OWNER TO new_owner;
ALTER TABLESPACE tablespace_name
    SET ( {tablespace_option = value} [, ... ] );
ALTER TABLESPACE tablespace_name
    RESET ( tablespace_option [, ... ] );
ALTER TABLESPACE tablespace_name
    RESIZE MAXSIZE { UNLIMITED | 'space_size' };

ALTER TEXT SEARCH CONFIGURATION

更改文本搜索配置的定义。用户可以将映射从字串类型调整为字典，或者改变配置的名称或者所有者，或者修改搜索配置的配置参数。

ALTER TEXT SEARCH CONFIGURATION name
    ADD MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]
ALTER TEXT SEARCH CONFIGURATION name
    ALTER MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]
ALTER TEXT SEARCH CONFIGURATION name
    ALTER MAPPING REPLACE old_dictionary WITH new_dictionary
ALTER TEXT SEARCH CONFIGURATION name
    ALTER MAPPING FOR token_type [, ... ] REPLACE old_dictionary WITH new_dictionary
ALTER TEXT SEARCH CONFIGURATION name
    DROP MAPPING [ IF EXISTS ] FOR token_type [, ... ]
ALTER TEXT SEARCH CONFIGURATION name RENAME TO new_name
ALTER TEXT SEARCH CONFIGURATION name OWNER TO new_owner
ALTER TEXT SEARCH CONFIGURATION name SET SCHEMA new_schema
ALTER TEXT SEARCH CONFIGURATION name SET ( {configuration_option = value} [, ...] )
ALTER TEXT SEARCH CONFIGURATION name RESET ( {configuration_option} [, ...] )

ALTER TEXT SEARCH DICTIONARY

修改全文检索词典的相关定义，包括参数、名称、所有者、以及模式等。

ALTER TEXT SEARCH DICTIONARY name ( option = value | option [, ...] );
ALTER TEXT SEARCH DICTIONARY name RENAME TO new_name;
ALTER TEXT SEARCH DICTIONARY name OWNER TO new_owner;
ALTER TEXT SEARCH DICTIONARY name SET SCHEMA new_schema

ALTER TRIGGER

修改触发器名称。

ALTER TRIGGER name ON table_name RENAME TO new_name

ALTER TYPE

修改一个类型的定义。

ALTER TYPE name action [, ... ]
ALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
ALTER TYPE name RENAME TO new_name
ALTER TYPE name SET SCHEMA new_schema
ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]
ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value

where action is one of:

    ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
    DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]
    ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]

ALTER USER

修改数据库用户的属性。

ALTER USER user_name [ [ WITH ] option [ ... ] ];
ALTER USER user_name
    RENAME TO new_name;
ALTER USER user_name [ IN DATABASE database_name ]
    SET configuration_parameter {{ TO | = } { value | DEFAULT }|FROM CURRENT};
ALTER USER user_name
    [ IN DATABASE database_name ] RESET {configuration_parameter|ALL};

where option can be:
{CREATEDB | NOCREATEDB}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {AUDITADMIN | NOAUDITADMIN}
    | {SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {USEFT | NOUSEFT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | {PERSISTENCE | NOPERSISTENCE}
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' [ EXPIRED ] | DISABLE | EXPIRED }
    | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' | EXPIRED ] | DISABLE }
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | USER GROUP 'groupuser'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | NODE GROUP logic_cluster_name
    | ACCOUNT { LOCK | UNLOCK }
    | PGUSER

ALTER VIEW

更改视图的各种辅助属性。

ALTER VIEW [ IF EXISTS ] view_name
    ALTER [ COLUMN ] column_name SET DEFAULT expression;
ALTER VIEW [ IF EXISTS ] view_name
    ALTER [ COLUMN ] column_name DROP DEFAULT;
ALTER VIEW [ IF EXISTS ] view_name
    OWNER TO new_owner;
ALTER VIEW [ IF EXISTS ] view_name
    RENAME TO new_name;
ALTER VIEW [ IF EXISTS ] view_name
    SET SCHEMA new_schema;
ALTER VIEW [ IF EXISTS ] view_name
    SET ( {view_option_name [= view_option_value]} [, ... ] );
ALTER VIEW [ IF EXISTS ] view_name
    RESET ( view_option_name [, ... ] );

ANALYSE|ANALYZE

用于收集与数据库中普通表内容相关的统计信息，统计结果存储在系统表PG_STATISTIC下。执行计划生成器会使用这些统计数据，以确定最有效的执行计划。

{ANALYZE | ANALYSE} [ VERBOSE ]
    [ table_name [ ( column_name [, ...] ) ] ];
{ANALYZE | ANALYSE} [ VERBOSE ]
    [ table_name [ ( column_name [, ...] ) ] ]
    PARTITION partition_name;
{ANALYZE | ANALYSE} [ VERBOSE ]
    { foreign_table_name | FOREIGN TABLES };
{ANALYZE | ANALYSE} [ VERBOSE ]
    table_name (( column_1_name, column_2_name [, ...] ));
{ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE};
{ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE}
    table_name|index_name [CASCADE];
{ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE}
    table_name PARTITION (partition_name) [CASCADE];

ANONYMOUS BLOCK

匿名块（Anonymous Block）一般用于不频繁执行的脚本或不重复进行的活动。它们在一个会话中执行，并不被存储。

[DECLARE [declare_statements]]
        BEGIN
        execution_staements
        END;
        /

openGauss小助手

关注

21
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
openGauss高级特性:QL语法（1）

修改表，包括修改表的定义、重命名表、重命名表中指定的列、重命名表的约束、设置表的所属模式、添加/更新多个列、打开/关闭行访问控制开关。用户可以将映射从字串类型调整为字典，或者改变配置的名称或者所有者，或者修改搜索配置的配置参数。对已存在的行访问控制策略（包括行访问控制策略的名称，行访问控制指定的用户，行访问控制的策略表达式）进行修改。它的唯一的功能是分配一个新的所有者。修改数据库的属性，包括它的名称、所有者、连接数限制、对象隔离属性等。设置应用于将来创建的对象的权限（这不会影响分配到已有对象中的权限）。
复制链接

扫一扫