DDL Syntax Overview

Data definition language (DDL) is used to define or modify an object in a database, such as a table, an index, or a view.

NOTE: openGauss does not support DDL when the primary node of the database is incomplete. For example, if the primary node of the database in openGauss is faulty, creating a database or a table will fail.

Defining a CMK

CMKs are used to encrypt CEKs for the encrypted database feature. CMK definition includes creating and deleting a CMK. For details about related SQL statements, see Table 1.

Table 1 SQL statements for defining a CMK

Function	SQL Statement
Creating a CMK	CREATE CLIENT MASTER KEY
Deleting a CMK	DROP CLIENT MASTER KEY

Defining a CEK

CEKs are used to encrypt data for the encrypted database feature. CEK definition includes creating and deleting a CEK. For details about related SQL statements, see Table 1.

Table 2 SQL statements for defining a CEK

Function	SQL Statement
Creating a CEK	CREATE COLUMN ENCRYPTION KEY
Deleting a CEK	DROP COLUMN ENCRYPTION KEY

Defining a Database

A database is the warehouse for organizing, storing, and managing data. Defining a database includes creating a database, altering the database attributes, and deleting the database. For details about related SQL statements, see Table 3.

Table 3 SQL statements for defining a database

Function	SQL Statement
Creating a database	CREATE DATABASE
Altering a database attribute	ALTER DATABASE
Deleting a database	DROP DATABASE

Defining a schema

A schema is the set of a group of database objects and is used to control the access to the database objects. For details about related SQL statements, see Table 4.

Table 4 SQL statements for defining a schema

Function	SQL Statement
Creating a schema	CREATE SCHEMA
Altering a schema attribute	ALTER SCHEMA
Deleting a schema	DROP SCHEMA

Defining a Tablespace

A tablespace is used to manage data objects and corresponds to a catalog on a disk. For details about related SQL statements, see Table 5.

Table 5 SQL statements for defining a tablespace

Function	SQL Statement
Creating a tablespace	CREATE TABLESPACE
Altering a tablespace attribute	ALTER TABLESPACE
Deleting a tablespace	DROP TABLESPACE

Defining a Table

A table is a special data structure in a database and is used to store data objects and relationship between data objects. For details about related SQL statements, see Table 6.

Table 6 SQL statements for defining a table

Function	SQL Statement
Creating a table	CREATE TABLE
Altering a table attribute	ALTER TABLE
Deleting a table	DROP TABLE

Defining a Partitioned Table

A partitioned table is a logical table used to improve query performance and does not store data (data is stored in common tables). For details about related SQL statements, see Table 7.

Table 7 SQL statements for defining a partitioned table

Function	SQL Statement
Creating a partitioned table	CREATE TABLE PARTITION
Creating a partition	ALTER TABLE PARTITION
Altering a partitioned table attribute	ALTER TABLE PARTITION
Deleting a partition	ALTER TABLE PARTITION
Deleting a partitioned table	DROP TABLE

Defining an Index

An index indicates the sequence of values in one or more columns in a database table. It is a data structure that improves the speed of data access to specific information in a database table. For details about related SQL statements, see Table 8.

Table 8 SQL statements for defining an index

Function	SQL Statement
Creating an index	CREATE INDEX
Altering an index attribute	ALTER INDEX
Deleting an index	DROP INDEX
Rebuilding an index	REINDEX

Defining a Stored Procedure

A stored procedure is a set of SQL statements for achieving specific functions and is stored in the database after compiling. Users can specify a name and provide parameters (if necessary) to execute the stored procedure. For details about related SQL statements, see Table 9.

Table 9 SQL statements for defining a stored procedure

Function	SQL Statement
Creating a stored procedure	CREATE PROCEDURE
Deleting a stored procedure	DROP PROCEDURE

Defining a Function

In openGauss, a function is similar to a stored procedure, which is a set of SQL statements. The function and stored procedure are used the same. For details about related SQL statements, see Table 10.

Table 10 SQL statements for defining a function

Function	SQL Statement
Creating a function	CREATE FUNCTION
Altering a function attribute	ALTER FUNCTION
Deleting a function	DROP FUNCTION

Defining a Package

A package consists of the package specification and package body. It is used to manage stored procedures and functions by class, which is similar to classes in languages such as Java and C++.

Table 11 SQL statements for defining a package

Function	SQL Statement
Creating a package	CREATE PACKAGE
Deleting a package	DROP PACKAGE
Altering a package attribute	ALTER PACKAGE

Defining a View

A view is a virtual table exported from one or more basic tables. It is used to control data accesses of users. Table 12 lists the related SQL statements.

Table 12 SQL statements for defining a view

Function	SQL Statement
Creating a view	CREATE VIEW
Deleting a view	DROP VIEW

Defining a Cursor

To process SQL statements, the stored procedure process assigns a memory segment to store context association. Cursors are handles or pointers to context regions. With a cursor, the stored procedure can control alterations in context areas. For details, see Table 13.

Table 13 SQL statements for defining a cursor

Function	SQL Statement
Creating a cursor	CURSOR
Moving a cursor	MOVE
Fetching data from a cursor	FETCH
Closing a cursor	CLOSE

Defining an Aggregate Function

Table 14 SQL statements for defining an aggregate function

Function	SQL Statement
Creating an aggregate function	CREATE AGGREGATE
Modifying an aggregate function	ALTER AGGREGATE
Deleting an aggregate function	DROP AGGREGATE

Defining Data Type Conversion

Table 15 SQL statements for defining a data type

Function	SQL Statement
Creating user-defined data type conversion	CREATE CAST
Deleting user-defined data type conversion	DROP CAST

Defining a Plug-in Extension

Table 16 SQL statements for defining a plug-in extension

Function	SQL Statement
Creating a plug-in extension	CREATE EXTENSION
Modifying a plug-in extension	ALTER EXTENSION
Deleting a plug-in extension	DROP EXTENSION

Defining an Operator

Table 17 SQL statements for defining an operator

Function	SQL Statement
Creating an operator	CREATE OPERATOR
Modifying an operator	ALTER OPERATOR
Deleting an operator	DROP OPERATOR

Defining a Procedural Language

Table 18 SQL statements for defining a procedural language

Function	SQL Statement
Creating a procedural language	CREATE LANGUAGE
Changing a procedural language	ALTER LANGUAGE
Deleting a procedural language	DROP LANGUAGE

Defining a Data Type

Table 19 SQL statements for defining a data type

Function	SQL Statement
Creating a data type	CREATE TYPE
Modifying a data type	ALTER TYPE
Deleting a data type	DROP TYPE