金仓数据库 KingbaseES SQL 语言参考手册 (3.1.3. 用户自定义类型)

3.1.3. 用户自定义类型

用户自定义的数据类型由 KingbaseES内置数据类型和其他用户自定义数据类型构成，用户自定义类型对应用程序中数据的结构和行为进行建模。以下各节介绍用户定义类型的各种类别。

自定义类型可以使用CREATE TYPE 和CREATE TYPE BODY命令创建。

3.1.3.1. 枚举类型

枚举（enum）类型是由一个静态、值的有序集合构成的数据类型。与很多编程语言所支持的 enum类型相似。

枚举类型可以使用 CREATE TYPE语句 命令创建，例如：

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

一旦被创建，枚举类型可以像很多其他类型一样在表和函数定义中使用：

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood
------+--------------
 Moe  | happy
(1 row)

3.1.3.1.1. 排序

一个枚举类型的值的排序是该类型被创建时所列出的值的顺序。枚举类型的所有标准的比较操作符以及相关聚集函数都被支持。例如：

INSERT INTO person VALUES ('Larry', 'sad');
INSERT INTO person VALUES ('Curly', 'ok');
SELECT * FROM person WHERE current_mood > 'sad';
 name  | current_mood
-------+--------------
 Moe   | happy
 Curly | ok
(2 rows)

SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
 name  | current_mood
-------+--------------
 Curly | ok
 Moe   | happy
(2 rows)

SELECT name
FROM person
WHERE current_mood = (SELECT MIN(current_mood) FROM person);
 name
-------
 Larry
(1 row)

3.1.3.1.2. 类型安全性

每一种枚举数据类型都是独立的并且不能和其他枚举类型相比较。看这样一个例子：

CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
CREATE TABLE holidays (
    num_weeks integer,
    happiness happiness
);
INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');
INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
ERROR:  invalid input value for enum happiness: "sad"
SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood = holidays.happiness;
ERROR:  operator does not exist: mood = happiness

如果你确实需要做这样的事情，你可以写一个自定义的操作符或者在查询中加上显式造型：

SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood::text = holidays.happiness::text;
 name | num_weeks
------+-----------
 Moe  |         4
(1 row)

3.1.3.1.3. 实现细节

枚举标签是大小写敏感的，因此'happy'与'HAPPY'是不同的。标签中的空格也是有意义的。

尽管枚举类型的主要目的是用于值的静态集合，但也有方法在现有枚举类型中增加新值和重命名值（见 ALTER TYPE语句 ） 。不能从枚举类型中去除现有的值，也不能更改这些值的排序顺序，如果要那样做可以删除并且重建枚举类型。

一个枚举值在磁盘上占据4个字节。一个枚举值的文本标签的长度受限于NAMEDATALEN设置，该设置被编译在KingbaseES中，在标准编译下它表示最多63字节。

从内部枚举值到文本标签的翻译被保存在系统目录 sys_enum 中。可以直接查询该目录。

3.1.3.2. 集合类型

集合类型包括三种具体的类型：关联数组（AssociativeArray）、嵌套表（Nested Table）和可变数组（Varray）。其中嵌套表和可变数组类型可以在SQL端通过CREATE TYPE创建，用于PL/SQL变量的数据类型，参数或者函数返回值，但不能用作数据表的列类型。

3.1.3.2.1. 嵌套表

嵌套表给定数据连续的索引，并从1开始。使用这些索引，用户可以访问各个嵌套表数据。

3.1.3.2.1.1. 嵌套表类型的声明

嵌套表类型可以使用 CREATE TYPE语句 命令创建，例如：

CREATE TYPE NestedTable_Int AS TABLE OF INT;

3.1.3.2.1.2. 嵌套表类型的使用

嵌套表适用于以下几种情况：

• 元素数不确定；

• 索引值可能出现不连续的情况；

• 必须单独删除或更新某些元素的情况；

• 嵌套表可以看做是index为int的关联数组。

3.1.3.2.1.3. 示例

CREATE TYPE Nested_Table_Int AS TABLE OF INT;
\set SQLTERM /
DO $$DECLARE
    a Nested_Table_Int;
BEGIN
    a = Nested_Table_Int(1, 2, 3);
END$$;
/

CREATE Function test_nestedtable1() return Nested_Table_Int AS
    result Nested_Table_Int = Nested_Table_Int(1, 2, 5);
BEGIN
    return result;
end;
/

Create PROCEDURE test_nestedtable2(nest_int Nested_Table_Int) AS
DECLARE
    i INT;
BEGIN
    i := nest_int.first();
    WHILE i IS NOT NULL LOOP
        RAISE NOTICE 'nest_int(%) is %', i, nest_int(i);
        i := nest_int.next(i);
    END LOOP;
END;
/
call test_nestedtable2(test_nestedtable1());
/

3.1.3.2.2. 可变数组

可变数组的下标索引类型为INT并且从1开始。与嵌套表不同，可变数组在定义的时候需要指定最大元素个数，使用中不能超过该限制。并且可变数组的下标是连续的，为了保证其连续性，可变数组不能通过delete(index)和delete(index1,index2)方法删除任何元素。

3.1.3.2.2.1. 可变数组的声明

可变数组类型可以使用 CREATE TYPE语句 命令创建，例如：

CREATE TYPE Varray_Int AS VARRAY(50) OF INT;

3.1.3.2.2.2. 可变数组的使用

嵌套表适用于以下几种情况：

• 元素数有最大限制；

• 索引值必须是连续的情况；

3.1.3.2.2.3. 示例

CREATE TYPE Varray_Int AS VARRAY(5) OF INT;
\set SQLTERM /
DO $$DECLARE
    a Varray_Int;
BEGIN
    a = Varray_Int(1, 2, 3);
END$$;
/

CREATE Function test_varray1() return Varray_Int AS
    result Varray_Int = Varray_Int(1, 2, 5);
BEGIN
    return result;
end;
/

Create PROCEDURE test_varray2(v_int Varray_Int) AS
DECLARE
    i INT;
BEGIN
    i := v_int.first();
    WHILE i IS NOT NULL LOOP
        RAISE NOTICE 'varray_int(%) is %', i, v_int(i);
        i := v_int.next(i);
    END LOOP;
END;
/
call test_varray2(test_varray1());
/

3.1.3.3. 组合类型

一个组合类型表示一行或一个记录的结构，它本质上就是一个域名和它们数据类型的列表。KingbaseES允许把组合类型用在很多能用简单类型的地方。例如，一个表的一列可以被声明为一种组合类型。

3.1.3.3.1. 组合类型的声明

这里有两个定义组合类型的简单例子：

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

该语法堪比CREATE TABLE，不过只能指定域名和类型，当前不能包括约束（例如NOT NULL）。注意AS关键词是必不可少的，如果没有它，系统将认为用户想要的是一种不同类型的CREATE TYPE命令，并且你将得到奇怪的语法错误。

定义了类型之后，我们可以用它们来创建表：

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

or functions:

CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS 'SELECT $1.price * $2' LANGUAGE SQL;

SELECT price_extension(item, 10) FROM on_hand;

只要你创建了一个表，也会自动创建一个组合类型来表示表的行类型，它具有和表一样的名称。例如，如果我们说：

CREATE TABLE inventory_item (
    name            text,
    supplier_id     integer REFERENCES suppliers,
    price           numeric CHECK (price > 0)
);

那么和上面所示相同的inventory_item组合类型将成为一种副产品，并且可以按上面所说的进行使用。不过要注意当前实现的一个重要限制：因为没有约束与一个组合类型相关，显示在表定义中的约束不会应用于表外组合类型的值（要解决这个问题，可以在该组合类型上创建一个域，并且把想要的约束应用为这个域上的CHECK约束）。

3.1.3.3.2. 构造组合值

要把一个组合值写作一个文字常量，将该域值封闭在圆括号中并且用逗号分隔它们。你可以在任何域值周围放上双引号，并且如果该域值包含逗号或圆括号则必须这样做（更多细节见下文）。这样，一个组合常量的一般格式是下面这样的：

'( val1 , val2 , ... )'

一个例子是：

'("fuzzy dice",42,1.99)'

这将是上文定义的inventory_item类型的一个合法值。要让一个域为 NULL，在列表中它的位置上根本不写字符。例如，这个常量指定其第三个域为 NULL：

'("fuzzy dice",42,)'

如果你写一个空字符串而不是 NULL，写上两个引号：

'("",42,)'

这里第一个域是一个非 NULL 空字符串，第三个是 NULL。

（这些常量实际上只是 其他类型的常量 中讨论的一般类型常量的特殊类型。该常量最初被当做一个字符串并且被传递给组合类型输入转换例程。有必要用一次显式类型说明来告知要把该常量转换成何种类型。）。

ROW表达式也能被用来构建组合值。在大部分情况下，比起使用字符串语法，这相当简单易用，因为你不必担心多层引用。我们已经在上文用过这种方法：

ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)

只要在表达式中有多于一个域，ROW 关键词实际上就是可选的，因此这些可以被简化成：

('fuzzy dice', 42, 1.99)
('', 42, NULL)

行构造器 中更加详细地讨论了ROW表达式语法。

3.1.3.3.3. 访问组合类型

要访问一个组合列的一个域，可以写成一个点和域的名称，更像从一个表名中选择一个域。事实上，它太像从一个表名中选择，这样我们不得不使用圆括号来避免让解析器混淆。例如，你可能尝试从例子表on_hand中选取一些子域：

SELECT item.name FROM on_hand WHERE item.price > 9.99;

这不会有用，因为名称item会被当成是一个表名，而不是on_hand的一个列名。你必须写成这样：

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

或者你还需要使用表名（例如在一个多表查询中），像这样：

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

现在加上括号的对象就被正确地解释为对item列的引用，然后可以从中选出子域。

只要你从一个组合值中选择一个域，相似的语法问题就适用。例如，要从一个返回组合值的函数的结果中选取一个域，你需要这样写：

SELECT (my_func(...)).field FROM ...

如果没有额外的圆括号，这将生成一个语法错误。

特殊的域名称*表示“所有的域”， 在查询中使用组合类型 中有进一步的解释。

3.1.3.3.4. 修改组合类型

这里有一些插入和更新组合列的正确语法的例子。首先，插入或者更新一整个列：

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;

第一个例子忽略ROW，第二个例子使用它，我们可以用两者之一完成。

我们能够更新一个组合列的单个子域：

UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;

注意：这里我们不需要（事实上也不能）把圆括号放在正好出现在SET之后的列名周围，但是当在等号右边的表达式中引用同一列时确实需要圆括号。

并且我们也可以指定子域作为INSERT的目标：

INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);

如果我们没有为该列的所有子域提供值，剩下的子域将用空值填充。

3.1.3.3.5. 在查询中使用组合类型

对于查询中的组合类型有各种特殊的语法规则和行为。这些规则提供了有用的捷径，但是如果你不懂背后的逻辑就会被此困扰。

在KingbaseES中，查询中对一个表名（或别名）的引用实际上是对该表的当前行的组合值的引用。例如，如果我们有一个如上 所示的表inventory_item，我们可以写：

SELECT c FROM inventory_item c;

这个查询产生一个单一组合值列，所以我们会得到这样的输出：

           c
------------------------
 ("fuzzy dice",42,1.99)
(1 row)

不过要注意简单的名称会在表名之前先匹配到列名，因此这个例子可行的原因仅仅是因为在该查询的表中没有名为c的列。

普通的限定列名语法``table_name``.``column_name``可以理解为把字段选择应用在该表的当前行的组合值上（由于效率的原因，实际上不是以这种方式实现）。

当我们写

SELECT c.* FROM inventory_item c;

时，根据SQL标准，我们应该得到该表展开成列的内容：

    name    | supplier_id | price
------------+-------------+-------
 fuzzy dice |          42 |  1.99
(1 row)

就好像查询是

SELECT c.name, c.supplier_id, c.price FROM inventory_item c;

尽管如上 所示，KingbaseES将对任何组合值表达式应用这种展开行为，但只要.*所应用的值不是一个简单的表名，你就需要把该值写在圆括号内。例如，如果myfunc()是一个返回组合类型的函数，该组合类型由列a、b和c组成，那么这两个查询有相同的结果：

SELECT (myfunc(x)).* FROM some_table;
SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;

提示：

KingbaseES实际上通过将第一种形式转换为第二种来处理列展开。因此，在这个例子中，用两种语法时对每行都会调用myfunc()三次。如果它是一个开销很大的函数，你可能希望避免这样做，所以可以用一个这样的查询：

SELECT m.* FROM some_table, LATERAL myfunc(x) AS m;

把该函数放在一个LATERAL FROM项中会防止它对每一行被调用超过一次。m.*仍然会被展开为m.a, m.b, m.c，但现在那些变量只是对这个FROM项的输出的引用（这里关键词LATERAL是可选的，但我们在这里写上它是为了说明该函数从some_table中得到x）。

当``composite_value``.*出现在一个`SELECT输出列表 选择列表 的顶层中、INSERT/UPDATE/DELETE中的一个`RETURNING列表中、一个`VALUES子句中或者一个行构造器中时，该语法会导致这种类型的列展开。在所有其他上下文（包括被嵌入在那些结构之一中时）中，把.*附加到一个组合值不会改变该值，因为它表示“所有的列”并且因此同一个组合值会被再次产生。例如，如果somefunc()接受一个组合值参数，这些查询是相同的：

SELECT somefunc(c.*) FROM inventory_item c;
SELECT somefunc(c) FROM inventory_item c;

在两种情况中，inventory_item的当前行被传递给该函数作为一个单一的组合值参数。即使.*在这类情况中什么也不做，使用它也是一种好的风格，因为它说清了一个组合值的目的是什么。特别地，解析器将会认为c.*中的c是引用一个表名或别名，而不是一个列名，这样就不会出现混淆。而如果没有.*，就弄不清楚c到底是表示一个表名还是一个列名，并且在有一个名为c的列时会优先选择按列名来解释。

另一个演示这些概念的例子是下面这些查询，它们表示相同的东西：

SELECT * FROM inventory_item c ORDER BY c;
SELECT * FROM inventory_item c ORDER BY c.*;
SELECT * FROM inventory_item c ORDER BY ROW(c.*);

所有这些ORDER BY子句指定该行的组合值，导致根据 组合类型比较 中介绍的规则对行进行排序。不过，如果inventory_item包含一个名为c的列，第一种情况会不同于其他情况，因为它表示仅按那一列排序。给定之前所示的列名，下面这些查询也等效于上面的那些查询：

SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price);
SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);

（最后一种情况使用了一个省略关键字ROW的行构造器）。

另一种与组合值相关的特殊语法行为是，我们可以使用函数记法来抽取一个组合值的字段。解释这种行为的简单方式是记法field(``table``)和table.``field``是可以互换的。例如，这些查询是等效的：

SELECT c.name FROM inventory_item c WHERE c.price > 1000;
SELECT name(c) FROM inventory_item c WHERE price(c) > 1000;

此外，如果我们有一个函数接受单一的组合类型参数，我们可以以任意一种记法来调用它。这些查询全都是等效的：

SELECT somefunc(c) FROM inventory_item c;
SELECT somefunc(c.*) FROM inventory_item c;
SELECT c.somefunc FROM inventory_item c;

这种函数记法和字段记法之间的等效性使得我们可以在组合类型上使用函数来实现“计算字段”。 一个使用上述最后一种查询的应用不会直接意识到somefunc不是一个真实的表列。

提示：

由于这种行为，让一个接受单一组合类型参数的函数与该组合类型的任意字段具有相同的名称是不明智的。出现歧义时，如果使用了字段名语法，则字段名解释将被选择，而如果使用的是函数调用语法则会选择函数解释。不过，KingbaseES V8.3 版本之前总是选择字段名解释，除非该调用的语法要求它是一个函数调用。在老的版本中强制函数解释的一种方法是用方案限定函数名，也就是写成schema.``func``(``compositevalue``)。

3.1.3.3.6. 组合类型输入和输出语法

一个组合值的外部文本表达由根据域类型的 I/O 转换规则解释的项，外加指示组合结构的装饰组成。装饰由整个值周围的圆括号（(和)），外加相邻项之间的逗号（,）组成。圆括号之外的空格会被忽略，但是在圆括号之内空格会被当成域值的一部分，并且根据域数据类型的输入转换规则可能有意义，也可能没有意义。例如，在

'(  42)'

中，如果域类型是整数则空格会被忽略，而如果是文本则空格不会被忽略。

如前所示，在写一个组合值时，你可以在任意域值周围写上双引号。如果不这样做会让域值迷惑组合值解析器，你就必须这么做。特别地，包含圆括号、逗号、双引号或反斜线的域必须用双引号引用。要把一个双引号或者反斜线放在一个被引用的组合域值中，需要在它前面放上一个反斜线（还有，一个双引号引用的域值中的一对双引号被认为是表示一个双引号字符，这和 SQL 字符串中单引号的规则类似）。另一种办法是，你可以避免引用以及使用反斜线转义来保护所有可能被当作组合语法的数据字符。

一个全空的域值（在逗号或圆括号之间完全没有字符）表示一个 NULL。要写一个空字符串值而不是 NULL，可以写成""。

如果域值是空串或者包含圆括号、逗号、双引号、反斜线或空格，组合输出例程将在域值周围放上双引号（对空格这样处理并不是不可缺少的，但是可以提高可读性）。嵌入在域值中的双引号及反斜线将被双写。

注意：

记住你在一个 SQL 命令中写的东西将首先被解释为一个字符串，然后才会被解释为一个组合。这就让你所需要的反斜线数量翻倍（假定使用了转义字符串语法）。例如，要在组合值中插入一个含有一个双引号和一个反斜线的text域，你需要写成：

INSERT ... VALUES ('("\"\\")');

字符串处理器会移除一层反斜线，这样在组合值解析器那里看到的就会是("\"\\")。接着，字符串被交给text数据类型的输入例程并且变成"\（如果我们使用的数据类型的输入例程也会特别处理反斜线，例如bytea，在命令中我们可能需要八个反斜线用来在组合域中存储一个反斜线）。美元引用（见 美元引用的字符串常量 ）可以被用来避免双写反斜线。

提示：

当在 SQL 命令中书写组合值时，ROW构造器语法通常比组合文字语法更容易使用。在ROW中，单个域值可以按照平时不是组合值成员的写法来写。

3.1.3.4. 对象类型

对象类型和组合类型在定义和使用上都有很多相似性，可以认为对象类型是组合类型的增强。不同于组合类型的是，对象类型除了支持组合类型的行记录结构外，还支持声明和定义一些内部方法，并在创建相应的对象后在具体的对象上使用这些方法。

3.1.3.4.1. 对象类型的声明和定义

详细的创建规则和说明可参见 CREATE TYPE语句 和 CREATE TYPE BODY语句 。

这里有个定义对象类型的简单例子：

CREATE OR REPLACE TYPE objtype FORCE AUTHID CURRENT_USER AS OBJECT(
    f1 int,
    f2 varchar2(10),
    MEMBER FUNCTION memfunc(i int) RETURN INT,
    STATIC PROCEDURE staproc,
    CONSTRUCTOR FUNCTION objtype(self in out objtype) RETURN SELF AS RESULT
);

\set SQLTERM /
CREATE OR REPLACE TYPE BODY objtype AS
    MEMBER FUNCTION memfunc(i int) RETURN INT AS
    BEGIN
      RAISE NOTICE 'self.f1 is %', self.f1;
      RAISE NOTICE 'self.f2 is %', f2;
      RETURN i;
    END;

    STATIC PROCEDURE staproc AS
    BEGIN
      RAISE NOTICE 'this is a static procedure in object type';
    END;

    CONSTRUCTOR FUNCTION objtype(self in out objtype) RETURN SELF AS RESULT AS
    BEGIN
      f1 := 1;
      self.f2 := 'a';
      RETURN ;
    END;
END;
/
\set SQLTERM ;

可以用它作为主体直接创建表：

CREATE TABLE tab2 OF objtype;

INSERT INTO tab2 VALUES (1, 'test');

SELECT * FROM tab2;
 f1 |  f2
----+------
  1 | test
(1 row)

SELECT t.memfunc(1) FROM tab2 t; -- 使用表的成员函数
NOTICE:  self.f1 is 1
NOTICE:  self.f2 is test
 objtype.memfunc
-----------------
               1
(1 row)

CALL objtype.staproc(); -- 使用对象类型的静态存储过程
NOTICE:  this is a static procedure in object type

也可以在PL/SQL块中直接使用:

\set SQLTERM /
DECLARE
  item objtype:= objtype();
  item2 objtype;
BEGIN
  item2 := objtype(2, 'test');
  RAISE NOTICE 'item''s member function return %', item.memfunc(1); -- 使用 PL/SQL中对象的成员函数

  RAISE NOTICE 'item2''s member function return %', item2.memfunc(2); -- 使用PL/SQL中对象的成员函数

  objtype.staproc();  -- 使用对象类型的静态存储过程
END;
/
\set SQLTERM ;

NOTICE:  self.f1 is 1
NOTICE:  self.f2 is a
NOTICE:  item's member function return 1
NOTICE:  self.f1 is 2
NOTICE:  self.f2 is test
NOTICE:  item2's member function return 2
NOTICE:  this is a static procedure in object type

3.1.3.5. 数组

KingbaseES允许一个表中的列定义为变长多维数组。可以创建任何内建或用户定义的基类、枚举类型、组合类型或者域的数组。

3.1.3.5.1. 数组类型的定义

为了展示数组类型的使用，我们创建这样一个表：

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

如上所示，一个数组数据类型可以通过在数组元素的数据类型名称后面加上方括号（[]）来命名。上述命令将创建一个名为sal_emp的表，它有一个类型为text的列（name），一个表示雇员的季度工资的一维integer类型数组（pay_by_quarter），以及一个表示雇员每周日程表的二维text类型数组（schedule）。

CREATE TABLE的语法允许指定数组的确切大小，例如：

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

然而，当前的实现忽略任何提供的数组尺寸限制，即其行为与未指定长度的数组相同。

当前的实现也不会强制所声明的维度数。一个特定元素类型的数组全部被当作是相同的类型，而不论其尺寸或维度数。因此，在CREATE TABLE中声明数组的尺寸或维度数仅仅只是文档而已，它并不影响运行时的行为。

另一种符合SQL标准的语法是使用关键词ARRAY，可以用来定义一维数组。pay_by_quarter可以这样定义：

pay_by_quarter  integer ARRAY[4],

或者，不指定数组尺寸：

pay_by_quarter  integer ARRAY,

但是和前面一样，KingbaseES在任何情况下都不会强制尺寸限制。

3.1.3.5.2. 数组值输入

要把一个数组值写成一个文字常数，将元素值用花括号包围并用逗号分隔（如果你懂C，这和初始化结构的C语法没什么两样）。在任意元素值周围可以使用双引号，并且在元素值包含逗号或花括号时必须这样做（更多细节如下所示）。因此，一个数组常量的一般格式如下：

'{ val1 delim val2 delim ... }'

这里``delim``是类型的定界符，记录在类型的sys_type项中。在KingbaseES发行提供的标准数据类型中，所有的都使用一个逗号（,），除了类型box使用一个分号（;）。每个``val``可以是数组元素类型的一个常量，也可以是一个子数组。一个数组常量的例子是：

'{{1,2,3},{4,5,6},{7,8,9}}'

该常量是一个二维的，3乘3数组，它由3个整数子数组构成。

要设置一个数组常量的一个元素为NULL，在该元素值处写NULL（任何NULL的大写或小写变体都有效）。如果你需要一个真正的字符串值“NULL”，你必须在它两边放上双引号。

（这些种类的数组常数实际是 其他类型的常量 中讨论的一般类型常量的一种特殊形式。常数最初被当做一个字符串，然后被传给数组的输入转换例程。有必要时可能需要一个显式的类型指定。）

现在我们可以展示一些INSERT语句：

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

前两个插入的结果看起来像这样：

SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

多维数组的每一维都必须有相匹配的长度。不匹配会造成错误，例如：

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching
dimensions

ARRAY构造器语法也可以被用于：

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

注意：数组元素是普通SQL常数或表达式，例如，字符串文字使用单引号而不是双引号包围，因为双引号可以出现在一个数组文字中。ARRAY构造器语法的详细讨论请见 数组构造器 。

3.1.3.5.3. 访问数组

现在，我们可以在该表上运行一些查询。首先，我们展示如何访问一个数组中的一个元素。下面的查询检索在第二季度工资发生变化的雇员的名字：

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

数组下标写在方括号内。默认情况下，KingbaseES为数组使用了一种从1开始的编号习惯，即一个具有``n``个元素的数组从array[1]开始，结束于array[n]。

下面的查询检索所有员工第三季度的工资：

SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter
----------------
          10000
          25000
(2 rows)

我们也可以访问一个数组的任意矩形切片或者子数组。一个数组切片可以通过在一个或多个数组维度上指定下界:``上界``来定义例如，下面的查询检索Bill在本周头两天日程中的第一项：

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

如果任何维度被写成一个切片，即包含一个冒号，那么所有的维度都被看成是切片对待。其中任何只有一个数字（无冒号）的维度被视作是从1到指定的数字。例如，下面例子中的[2]被认为是[1:2]：

SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

为了避免和非切片情况搞混，最好在所有的维度上都使用切片语法，例如[1:2][1:1]而不是[2][1:1]。

可以省略一个切片说明符的``lower-bound``或者 ``upper-bound``（亦可两者都省略），缺失的 边界会被数组下标的上下限所替代。例如：

SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{lunch},{presentation}}
(1 row)

SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

如果数组本身为空或者任何一个下标表达式为空，访问数组下标表达式将会返回空值。如果下标超过了数组边界，下标表达式也会返回空值（这种情况不会抛出错误）。例如，如果schedule目前具有的维度是[1:3][1:2]，那么引用schedule[3][3]将得到NULL。相似地，使用错误的下标号引用一个数组会得到空值而不是错误。

如果数组本身或者任何一个下标表达式为空，则一个数组切片表达式也会得到空值。但是，在其他情况例如选择一个完全位于当前数组边界之外的切片时，一个切片表达式会得到一个空（零维）数组而不是空值（由于历史原因，这并不符合非切片行为）。 如果所请求的切片和数组边界重叠，那么它会被缩减为重叠的区域而不是返回空。

任何数组值的当前维度可以使用array_dims函数获得：

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims
------------
 [1:2][1:2]
(1 row)

array_dims产生一个text结果，它便于人类阅读但是不便于程序读取。 Dimensions can also be retrieved with 也可以通过array_upper和array_lower来获得维度，它们将分别返回一个指定数组的上界和下界：

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)

array_length将返回一个指定数组维度的长度：

SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_length
--------------
            2
(1 row)

cardinality返回一个数组中在所有维度上的元素总数。 这实际上是调用unnest将会得到的行数：

SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';

 cardinality
-------------
           4
(1 row)

3.1.3.5.4. 修改数组

一个数组值可以被整个替换：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

或者使用ARRAY表达式语法：

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

一个数组也可以在一个元素上被更新：

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

或者在一个切片上被更新：

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

也可以使用省略``lower-bound``或者 ``upper-bound``的切片语法，但是只能用于 更新一个不是 NULL 或者零维的数组值（否则无法替换现有的下标界线）。

可以通过赋值一个不存在的元素来扩大一个已存储的数组值。任何位于之前已存在的元素和新元素之间的位置都将被空值填充。例如，如果数组myarray目前有4个元素，在对myarray[6]赋值后它将有6个元素，其中myarray[5]为空值。目前，采用这种方式扩大数组只允许使用在一维数组上。

带下标的赋值方式允许创建下标不是从1开始的数组。例如，我们可以为myarray[-2:7]赋值来创建一个下标值从-2到7的数组。

新的数组值也可以通过串接操作符||构建：

SELECT ARRAY[1,2] || ARRAY[3,4];
 ?column?
-----------
 {1,2,3,4}
(1 row)

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
      ?column?
---------------------
 {{5,6},{1,2},{3,4}}
(1 row)

串接操作符允许把一个单独的元素加入到一个一维数组的开头或末尾。它也能接受两个``N``维数组，或者一个``N``维数组和一个``N+1``维数组。

当一个单独的元素被加入到一个一维数组的开头或末尾时，其结果是一个和数组操作数具有相同下界下标的新数组。例如：

SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
 array_dims
------------
 [0:2]
(1 row)

SELECT array_dims(ARRAY[1,2] || 3);
 array_dims
------------
 [1:3]
(1 row)

当两个具有相同维度数的数组被串接时，其结果保留左操作数的外维度的下界下标。结果将是一个数组，它由左操作数的每一个元素以及紧接着的右操作数的每一个元素。例如：

SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
 array_dims
------------
 [1:5]
(1 row)

SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
 array_dims
------------
 [1:5][1:2]
(1 row)

当一个``N``维数组被放在另一个``N+1``维数组的前面或者后面时，结果和上面的例子相似。每一个``N``维子数组实际上是``N+1``维数组外维度的一个元素。例如：

SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
 array_dims
------------
 [1:3][1:2]
(1 row)

一个数组也可以通过使用函数array_prepend、array_append或array_cat构建。前两个函数仅支持一维数组，但array_cat支持多维数组。 一些例子：

SELECT array_prepend(1, ARRAY[2,3]);
 array_prepend
---------------
 {1,2,3}
(1 row)

SELECT array_append(ARRAY[1,2], 3);
 array_append
--------------
 {1,2,3}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
 array_cat
-----------
 {1,2,3,4}
(1 row)

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
      array_cat
---------------------
 {{1,2},{3,4},{5,6}}
(1 row)

SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
      array_cat
---------------------
 {{5,6},{1,2},{3,4}}

在简单的情况中，上面讨论的串接操作符比直接使用这些函数更好。不过，由于 串接操作符需要服务于所有三种情况，所以它的负担比较重，在有些情况下使用 这些函数之一有助于避免混淆。例如：

SELECT ARRAY[1, 2] || '{3, 4}';  -- 没有指定类型的文字被当做一个数组
 ?column?
-----------
 {1,2,3,4}

SELECT ARRAY[1, 2] || '7';                 -- 这个也是
ERROR:  malformed array literal: "7"

SELECT ARRAY[1, 2] || NULL;                -- 未修饰的 NULL 也是如此
 ?column?
----------
 {1,2}
(1 row)

SELECT array_append(ARRAY[1, 2], NULL);    -- 这可能才是想要的意思
 array_append
--------------
 {1,2,NULL}

在上面的例子中，解析器看到在串接操作符的一遍看到了一个整数数组，并且在 另一边看到了一个未确定类型的常量。它用来决定该常量类型的启发式规则是假 定它和该操作符的另一个输入具有相同的类型 — 在这种情况中是整数数 组。因此串接操作符表示array_cat而不是 array_append。如果这样做是错误的选择，它可以通过将该常 量造型成数组的元素类型来修复。但是显式地使用array_append 可能是一种最好的方案。

3.1.3.5.5. 在数组中搜索

要在一个数组中搜索一个值，每一个值都必须被检查。这可以手动完成，但是我们必须知道数组的尺寸。例如：

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

但是这对于大型数组来说太过冗长，且在数组尺寸未知时无法使用。一种可选的方法可见 行和数组比较 。上面的查询可以被替换为：

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

此外，我们还可以查找所有元素值都为10000的数组所在的行：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

另外，generate_subscripts函数也可以用来完成类似的查找。例如：

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

我们也可以使用&&操作符来搜索一个数组，它会检查左操作数是否与右操作数重叠。例如：

SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];

你也可以使用array_position和array_positions在一个 数组中搜索特定值。前者返回值在数组中第一次出现的位置的下标。后者返回一个数组， 其中有该值在数组中的所有出现位置的下标。例如：

SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
 array_positions
-----------------
 2

SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
 array_positions
-----------------
 {1,4,8}

提示：

数组不是集合，在其中搜索指定数组元素可能是数据设计失误的表现。考虑使用一个独立的表来替代，其中每一行都对应于一个数组元素。这将更有利于搜索，并且对于大量元素的可扩展性更好。

3.1.3.5.6. 数组输入和输出语法

一个数组值的外部文本表现由根据数组元素类型的I/O转换规则解释的项构成，并在其上加上修饰用于指示数组结构。修饰包括数组值周围的花括号（{和}）以及相邻项之间的定界字符。定界字符通常是一个逗号（,），但是也可能是别的：它由数组元素类型的typdelim设置决定。在KingbaseES发行版提供的标准数据类型中，除了box类型使用分号（;）之外，其他都是用逗号。在一个多维数组中，每一个维度（行、平面、方体等）都有其自己的花括号层次，且同层的被花括号限定的相邻实体之间也必须有定界符。

如果元素值是空字符串、包含花括号、包含定界字符、包含双引号、包含反斜线、包含空白或者匹配词NULL，数组输出例程将在元素值周围放上双引号。嵌在元素值中的双引号以及反斜线将被反斜线转义。对于数字数据类型可以安全地假设双引号绝不会出现，但是对于文本数据类型我们必须准备好处理可能出现亦可能不出现的引号。

默认情况下，一个数组的一个维度的下界索引值被设置为1。要表示具有其他下界的数组，数组下标的范围应在填充数组内容之前被显式地指定好。这种修饰包括在每个数组维度上下界周围的方括号（[]），以及上下界之间的一个冒号（:）定界符。数组维度修饰后面要跟一个等号（=）。例如：

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;

 e1 | e2
----+----
  1 |  6
(1 row)

只有当数组的维度中有一个或多个的下界不为1时，数组输出例程才会在结果中包括维度。

如果为一个元素给定的值是NULL（或者是任何变体），该元素将被设置为NULL。任何引号或反斜线的存在将阻止这种行为，而允许为元素值输入“NULL”的字面意思。为了向后兼容KingbaseES V8 之前的版本，可将 array_nulls 配置参数设置为off来阻止将NULL识别为NULL。

如前所示，在写一个数组值时我们可以在任何单独数组元素周围使用引号。如果元素值可能混淆数组值分析器时，我们必须这样做。例如，包含花括号、逗号（或者数据类型的定界符）、双引号、反斜线或首尾有空白的元素必须使用双引号。空字符串和匹配单词NULL的字符串也必须使用双引号。要把一个双引号或反斜线放在一个使用了双引号的数组元素值中，需要在它前面放一个反斜线。作为一种选择，我们可以免去使用引号而使用反斜线转义的方式来保护可能被认为是数组语法的所有数据字符。

我们可以在左括号前面或右括号后面增加空白。我们也可以在任何单独的项之前或之后加上空白。在所有这些情况中空白将被忽略。但是，在被使用了双引号的元素中的空白以及周围有其他非空白字符的空白不会被忽略。

提示：

在SQL命令中写数组值时，ARRAY构造器语法常常比数组文字语法要更容易使用。在ARRAY中，单独的元素值可以使用不属于数组成员时的方式来书写。