我的书签
添加书签
移除书签集合支持的函数
集合操作符
- \=
参数：nesttable类型
返回值：true or false，bool类型
功能描述：两个集合类型是否相等。
示例：
1. openGauss=# declare
2. openGauss-# type nest is table of int;
3. openGauss-# a nest := nest(1,2);
4. openGauss-# b nest := nest(1,2);
5. openGauss-# flag bool;
6. openGauss-# begin
7. openGauss$# flag := a = b;
8. openGauss$# raise info '%', flag;
9. openGauss$# end;
10. openGauss$# /
11. INFO: t
12. ANONYMOUS BLOCK EXECUTE
- <>
参数：nesttable类型
返回值：true or false，bool类型
功能描述：两个集合类型是否不相等。
示例：
1. openGauss=# declare
2. openGauss-# type nest is table of int;
3. openGauss-# a nest := nest(1,2);
4. openGauss-# b nest := nest(1,2);
5. openGauss-# flag bool;
6. openGauss-# begin
7. openGauss$# flag := a <> b;
8. openGauss$# raise info '%', flag;
9. openGauss$# end;
10. openGauss$# /
11. INFO: f
12. ANONYMOUS BLOCK EXECUTE
集合MULTISET函数
- MULTISET UNION [ALL | DISTINCT]
参数：nesttable类型
返回值：nesttable类型
功能描述：两个集合变量的并集，ALL不去除重复元素，DISTINCT去除重复元素。
示例：
``` 1. openGauss=# declare 2. openGauss-# type nest is table of int; 3. openGauss-# a nest := nest(1,2); 4. openGauss-# b nest := nest(2,3); 5. openGauss-# begin 6. openGauss$# a := a MULTISET UNION ALL b; 7. openGauss$# raise info '%', a; 8. openGauss$# end; 9. openGauss$# / 10. INFO: {1,2,2,3} 11. ANONYMOUS BLOCK EXECUTE
- openGauss=# declare
- openGauss-# type nest is table of int;
- openGauss-# a nest := nest(1,2);
- openGauss-# b nest := nest(2,3);
- openGauss-# begin
- openGauss$# a := a MULTISET UNION DISTINCT b;
- openGauss$# raise info '%', a;
- openGauss$# end;
- openGauss$# /
- INFO: {1,2,3}
- ANONYMOUS BLOCK EXECUTE ```
- MULTISET EXCEPT [ALL | DISTINCT]
参数：nesttable类型
返回值：nesttable类型
功能描述：两个集合变量的差集。如A MULTISET EXCEPT B：ALL表示去除A中与B重复的元素；DISTINCT表示先对A进行去重操作，然后去除与B中有重复的元素。
示例：
``` 1. openGauss=# declare 2. openGauss-# type nest is table of int; 3. openGauss-# a nest := nest(1,2,2); 4. openGauss-# b nest := nest(2,3); 5. openGauss-# begin 6. openGauss$# a := a MULTISET EXCEPT ALL b; 7. openGauss$# raise info '%', a; 8. openGauss$# end; 9. openGauss$# / 10. INFO: {1,2} 11. ANONYMOUS BLOCK EXECUTE
- openGauss=# declare
- openGauss-# type nest is table of int;
- openGauss-# a nest := nest(1,2,2);
- openGauss-# b nest := nest(2,3);
- openGauss-# begin
- openGauss$# a := a MULTISET EXCEPT DISTINCT b;
- openGauss$# raise info '%', a;
- openGauss$# end;
- openGauss$# /
- INFO: {1}
- ANONYMOUS BLOCK EXECUTE ```
- MULTISET INTERSECT [ALL | DISTINCT]
参数：nesttable类型
返回值：nesttable类型
功能描述：两个集合变量的交集。如 A MULTISET INTERSECT B：ALL表是取A与B所有重复的元素；DISTINCT表示取A与B中重复元素，且去除重复元素。
示例：
``` 1. openGauss=# declare 2. openGauss-# type nest is table of int; 3. openGauss-# a nest := nest(1,2,2); 4. openGauss-# b nest := nest(2,2,3); 5. openGauss-# begin 6. openGauss$# a := a MULTISET INTERSECT ALL b; 7. openGauss$# raise info '%', a; 8. openGauss$# end; 9. openGauss$# / 10. INFO: {2,2} 11. ANONYMOUS BLOCK EXECUTE
- openGauss=# declare
- openGauss-# type nest is table of int;
- openGauss-# a nest := nest(1,2,2);
- openGauss-# b nest := nest(2,2,3);
- openGauss-# begin
- openGauss$# a := a MULTISET INTERSECT DISTINCT b;
- openGauss$# raise info '%', a;
- openGauss$# end;
- openGauss$# /
- INFO: {2}
- ANONYMOUS BLOCK EXECUTE ```
集合类型函数
- exists(idx)
参数：idx为int4类型或varchar类型，
返回值：true or false，bool类型
功能描述：查找指定位置是否存在有效元素。
示例：
``` 1. openGauss=# declare 2. openGauss-# type nest is table of varchar2; 3. openGauss-# a nest := nest('happy','?'); 4. openGauss-# flag bool; 5. openGauss-# begin 6. openGauss$# flag := a.exists(1); 7. openGauss$# raise info '%', flag; 8. openGauss$# flag := a.exists(10); 9. openGauss$# raise info '%', flag; 10. openGauss$# end; 11. openGauss$# / 12. INFO: t 13. INFO: f 14. ANONYMOUS BLOCK EXECUTE
- openGauss=# declare
- openGauss-# type nest is table of varchar2 index by varchar2;
- openGauss-# a nest;
- openGauss-# flag bool;
- openGauss-# begin
- openGauss$# a('1') := 'Be';
- openGauss$# a('2') := 'happy';
- openGauss$# a('3') := '.';
- openGauss$# flag := a.exists('1');
- openGauss$# raise info '%', flag;
- openGauss$# flag := a.exists('ddd');
- openGauss$# raise info '%', flag;
- openGauss$# end;
- openGauss$# /
- INFO: t
- INFO: f
- ANONYMOUS BLOCK EXECUTE ```
- extend[(count[, idx])]
参数：idx和count为int4类型
返回值：无返回值
功能描述：仅支持nesttable类型。在nesttable变量末尾拓展1个或count个元素。存在idx下标元素时，拷贝count个idx下元素到变量末尾。
约束：嵌套场景不支持extend()。
示例：
``` 1. openGauss=# declare 2. openGauss-# type nest is table of int; 3. openGauss-# a nest := nest(1); 4. openGauss-# begin 5. openGauss$# raise info '%', a; 6. openGauss$# a.extend; 7. openGauss$# raise info '%', a; 8. openGauss$# end; 9. openGauss$# / 10. INFO: {1} 11. INFO: {1,NULL} 12. ANONYMOUS BLOCK EXECUTE
- openGauss=# declare
- openGauss-# type nest is table of int;
- openGauss-# a nest := nest(1);
- openGauss-# begin
- openGauss$# raise info '%', a;
- openGauss$# a.extend(2);
- openGauss$# raise info '%', a;
- openGauss$# end;
- openGauss$# /
- INFO: {1}
- INFO: {1,NULL,NULL}
-
ANONYMOUS BLOCK EXECUTE
-
openGauss=# declare
- openGauss-# type nest is table of int;
- openGauss-# a nest := nest(1);
- openGauss-# begin
- openGauss$# raise info '%', a;
- openGauss$# a.extend(2,1);
- openGauss$# raise info '%', a;
- openGauss$# end;
- openGauss$# /
- INFO: {1}
- INFO: {1,1,1}
- ANONYMOUS BLOCK EXECUTE ```
- delete[(idx1[, idx2])]
参数：idx1和idx2为int4类型或varchar2类型
返回值：无返回值
功能描述：无参数时，（nesttable）删除集合类型的所有元素和空间，后续使用需要重新extend，（indexbytable）删除所有元素内容，一个参数删除指定位置元素（不删除空间），两个参数删除小标区间内的元素（不删除空间）。
约束：嵌套场景不支持delete()。
示例：
``` 1. openGauss=# declare 2. openGauss-# type nest is table of int; 3. openGauss-# a nest := nest(1,2,3,4,5); 4. openGauss-# begin 5. openGauss$# raise info '%', a; 6. openGauss$# a.delete; 7. openGauss$# raise info '%', a; 8. openGauss$# end; 9. openGauss$# / 10. INFO: {1,2,3,4,5} 11. INFO: {} 12. ANONYMOUS BLOCK EXECUTE
- openGauss=# declare
- openGauss-# type nest is table of int;
- openGauss-# a nest := nest(1,2,3,4,5);
- openGauss-# begin
- openGauss$# raise info '%', a;
- openGauss$# a.delete(3);
- openGauss$# raise info '%', a;
- openGauss$# end;
- openGauss$# /
- INFO: {1,2,3,4,5}
- INFO: {1,2,4,5}
-
ANONYMOUS BLOCK EXECUTE
-
openGauss=# declare
- openGauss-# type nest is table of int;
- openGauss-# a nest := nest(1,2,3,4,5);
- openGauss-# begin
- openGauss$# raise info '%', a;
- openGauss$# a.delete(2,4);
- openGauss$# raise info '%', a;
- openGauss$# end;
- openGauss$# /
- INFO: {1,2,3,4,5}
- INFO: {1,5}
- ANONYMOUS BLOCK EXECUTE ```
- trim[(n)]
参数：n为int4类型
返回值：无返回值
功能描述：仅支持nesttable类型，无参数时，删除末尾一个元素空间，输入参数合法时，删除末尾指定数量元素空间。
约束：嵌套场景不支持trim()。
示例：
1. openGauss=# declare
2. openGauss-# type nest is table of int;
3. openGauss-# aa nest:=nest(11,22,33,44,55);
4. openGauss-# begin
5. openGauss$# raise info 'aa:%' ,aa;
6. openGauss$# aa.trim;
7. openGauss$# raise info 'aa:%' ,aa;
8. openGauss$# aa.trim(2);
9. openGauss$# raise info 'aa:%' ,aa;
10. openGauss$# end;
11. openGauss$# /
12. INFO: aa:{11,22,33,44,55}
13. INFO: aa:{11,22,33,44}
14. INFO: aa:{11,22}
15. ANONYMOUS BLOCK EXECUTE
- count
参数：无
返回值：int类型
功能描述：返回集合中存在有效元素的个数。
示例：
``` 1. openGauss=# declare 2. openGauss-# type nest is table of int; 3. openGauss-# aa nest:=nest(11,22,33,44,55); 4. openGauss-# begin 5. openGauss$# raise info 'count:%' ,aa.count; 6. openGauss$# end; 7. openGauss$# / 8. INFO: count:5 9. ANONYMOUS BLOCK EXECUTE
- openGauss=# declare
- openGauss-# type nest is table of int index by varchar;
- openGauss-# aa nest;
- openGauss-# begin
- openGauss$# aa('aaa') := 111;
- openGauss$# aa('bbb') := 222;
- openGauss$# aa('ccc') := 333;
- openGauss$# raise info 'count:%' ,aa.count;
- openGauss$# end;
- openGauss$# /
- INFO: count:3
- ANONYMOUS BLOCK EXECUTE ```
- first
参数：无
返回值：int类型或varchar类型
功能描述：返回集合中第一个有效元素的下标。
示例：
``` 1. openGauss=# declare 2. openGauss-# type nest is table of int; 3. openGauss-# aa nest:=nest(11,22,33,44,55); 4. openGauss-# begin 5. openGauss$# raise info 'first:%' ,aa.first; 6. openGauss$# end; 7. openGauss$# / 8. INFO: first:1 9. ANONYMOUS BLOCK EXECUTE
- openGauss=# declare
- openGauss-# type nest is table of int index by varchar;
- openGauss-# aa nest;
- openGauss-# begin
- openGauss$# aa('aaa') := 111;
- openGauss$# aa('bbb') := 222;
- openGauss$# aa('ccc') := 333;
- openGauss$# raise info 'first:%' ,aa.first;
- openGauss$# end;
- openGauss$# /
- INFO: first:aaa
- ANONYMOUS BLOCK EXECUTE ```
- last
参数：无
返回值：int类型或varchar类型
功能描述：返回集合中最后一个有效元素的下标。
示例：
``` 1. openGauss=# declare 2. openGauss-# type nest is table of int; 3. openGauss-# aa nest:=nest(11,22,33,44,55); 4. openGauss-# begin 5. openGauss$# raise info 'last:%' ,aa.last; 6. openGauss$# end; 7. openGauss$# / 8. INFO: last:5 9. ANONYMOUS BLOCK EXECUTE
- openGauss=# declare
- openGauss-# type nest is table of int index by varchar;
- openGauss-# aa nest;
- openGauss-# begin
- openGauss$# aa('aaa') := 111;
- openGauss$# aa('bbb') := 222;
- openGauss$# aa('ccc') := 333;
- openGauss$# raise info 'last:%' ,aa.last;
- openGauss$# end;
- openGauss$# /
- INFO: last:ccc
- ANONYMOUS BLOCK EXECUTE ```
- prior(idx)
参数：idx为int类型或varchar类型
返回值：int类型或varchar类型
功能描述：返回集合中当前下标的前一个有效元素下标。
示例：
``` 1. openGauss=# declare 2. openGauss-# type nest is table of int; 3. openGauss-# aa nest:=nest(11,22,33,44,55); 4. openGauss-# begin 5. openGauss$# raise info 'prior:%' ,aa.prior(3); 6. openGauss$# end; 7. openGauss$# / 8. INFO: prior:2 9. ANONYMOUS BLOCK EXECUTE
- openGauss=# declare
- openGauss-# type nest is table of int index by varchar;
- openGauss-# aa nest;
- openGauss-# begin
- openGauss$# aa('aaa') := 111;
- openGauss$# aa('bbb') := 222;
- openGauss$# aa('ccc') := 333;
- openGauss$# raise info 'prior:%' ,aa.prior('bbb');
- openGauss$# end;
- openGauss$# /
- INFO: prior:aaa
- ANONYMOUS BLOCK EXECUTE ```
- next(idx)
参数：idx为int类型或varchar类型
返回值：int类型或varchar类型
功能描述：返回集合中当前下标的后一个有效元素下标。
示例：
``` 1. openGauss=# declare 2. openGauss-# type nest is table of int; 3. openGauss-# aa nest:=nest(11,22,33,44,55); 4. openGauss-# begin 5. openGauss$# raise info 'next:%' ,aa.next(3); 6. openGauss$# end; 7. openGauss$# / 8. INFO: next:4 9. ANONYMOUS BLOCK EXECUTE
- openGauss=# declare
- openGauss-# type nest is table of int index by varchar;
- openGauss-# aa nest;
- openGauss-# begin
- openGauss$# aa('aaa') := 111;
- openGauss$# aa('bbb') := 222;
- openGauss$# aa('ccc') := 333;
- openGauss$# raise info 'next:%' ,aa.next('bbb');
- openGauss$# end;
- openGauss$# /
- INFO: next:ccc
- ANONYMOUS BLOCK EXECUTE ```
- limit
参数：无
返回值：null
功能描述：用于nesttable类型，返回集合中最大可以储存的元素个数，只适用于array类型，nesttable返回空。
示例：
1. openGauss=# declare
2. openGauss-# type nest is table of int;
3. openGauss-# aa nest:=nest(11,22,33,44,55);
4. openGauss-# begin
5. openGauss$# raise info 'limit:%' ,aa.limit;
6. openGauss$# end;
7. openGauss$# /
8. INFO: limit:<NULL>
9. ANONYMOUS BLOCK EXECUTE
集合相关函数
- unnest_table(anynesttable)
描述：返回nesttable中的元素集合。
返回类型：setof anyelement
约束：不支持tableof类型嵌套tableof类型或者tableof嵌套其他类型再嵌套tableof类型的情况。
示例：
``` 1. create or replace procedure f1() 2. as 3. type t1 is table of int; 4. v2 t1 := t1(null, 2, 3, 4, null); 5. tmp int; 6. cursor c1 is select * from unnest_table(v2); 7. begin 8. open c1; 9. for i in 1 .. v2.count loop 10. fetch c1 into tmp; 11. if tmp is null then 12. dbe_output.print_line(i || ': is null'); 13. else 14. dbe_output.print_line(i || ': ' || tmp); 15. end if; 16. end loop; 17. close c1; 18. end; 19. /
- openGauss=# call f1();
- 1: is null
- 2: 2
- 3: 3
- 4: 4
- 5: is null
- f1
-
-
(1 row) ```
- unnest_table(anyindexbytable)
描述：返回table of index by类型根据index排序后的元素集合。
返回类型：setof anyelement
约束：不支持tableof类型嵌套tableof类型或者tableof嵌套其他类型再嵌套tableof类型的情况。只支持index by int类型，不支持index by varchar类型。
示例：
``` 1. create or replace procedure f1() 2. as 3. type t1 is table of int index by int; 4. v2 t1 := t1(1=>1, -10=>(-10), 6=>6, 4=>null); 5. tmp int; 6. cursor c1 is select * from unnest_table(v2); 7. begin 8. open c1; 9. for i in 1 .. v2.count loop 10. fetch c1 into tmp; 11. if tmp is null then 12. dbe_output.print_line(i || ': is null'); 13. else 14. dbe_output.print_line(i || ': ' || tmp); 15. end if; 16. end loop; 17. close c1; 18. end; 19. /
- openGauss=# call f1();
- 1: -10
- 2: 1
- 3: is null
- 4: 6
- f1
-
-
(1 row) ```
