文档地图

集合支持的函数

集合操作符

  • =

    参数：nesttable类型

    返回值：true or false，bool类型

    功能描述：两个集合类型是否相等。

    示例：

    openGauss=# declare
openGauss-#     type nest is table of int;
openGauss-#     a nest := nest(1,2);
openGauss-#     b nest := nest(1,2);
openGauss-#     flag bool; 
openGauss-# begin
openGauss$#     flag := a = b;
openGauss$#     raise info '%', flag;
openGauss$# end;
openGauss$# /
INFO:  t
ANONYMOUS BLOCK EXECUTE

  • <>

    参数：nesttable类型

    返回值：true or false，bool类型

    功能描述：两个集合类型是否不相等。

    示例：

    openGauss=# declare
openGauss-#     type nest is table of int;
openGauss-#     a nest := nest(1,2);
openGauss-#     b nest := nest(1,2);
openGauss-#     flag bool; 
openGauss-# begin
openGauss$#     flag := a <> b;
openGauss$#     raise info '%', flag;
openGauss$# end;
openGauss$# /
INFO:  f
ANONYMOUS BLOCK EXECUTE

集合MULTISET函数

  • MULTISET UNION [ALL | DISTINCT]

    参数：nesttable类型

    返回值：nesttable类型

    功能描述：两个集合变量的并集，ALL不去除重复元素，DISTINCT去除重复元素。

    示例：

    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 ALL b;
openGauss$#     raise info '%', a;
openGauss$# end;
openGauss$# /
INFO:  {1,2,2,3}
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中有重复的元素。

    示例：

    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 ALL b;
openGauss$#     raise info '%', a;
openGauss$# end;
openGauss$# /
INFO:  {1,2}
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中重复元素，且去除重复元素。

    示例：

    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 ALL b;
openGauss$#     raise info '%', a;
openGauss$# end;
openGauss$# /
INFO:  {2,2}
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类型

    功能描述：查找指定位置是否存在有效元素。

    示例：

    openGauss=# declare
openGauss-#     type nest is table of varchar2;
openGauss-#     a nest := nest('happy','?');
openGauss-#     flag bool;
openGauss-# begin
openGauss$#     flag := a.exists(1);
openGauss$#     raise info '%', flag;
openGauss$#     flag := a.exists(10);
openGauss$#     raise info '%', flag;
openGauss$# end;
openGauss$# /
INFO:  t
INFO:  f
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()。

    示例：

    openGauss=# declare
openGauss-#     type nest is table of int;
openGauss-#     a nest := nest(1);
openGauss-# begin
openGauss$#     raise info '%', a;
openGauss$#     a.extend;
openGauss$#     raise info '%', a;
openGauss$# end;
openGauss$# /
INFO:  {1}
INFO:  {1,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);
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()。

    示例：

    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;
openGauss$#     raise info '%', a;
openGauss$# end;
openGauss$# /
INFO:  {1,2,3,4,5}
INFO:  {}
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()。

    示例：

    openGauss=# declare
openGauss-# type nest is table of int;
openGauss-# aa nest:=nest(11,22,33,44,55);
openGauss-# begin
openGauss$# raise info 'aa:%' ,aa;
openGauss$# aa.trim;
openGauss$# raise info 'aa:%' ,aa;
openGauss$# aa.trim(2);
openGauss$# raise info 'aa:%' ,aa;
openGauss$# end;
openGauss$# /
INFO:  aa:{11,22,33,44,55}
INFO:  aa:{11,22,33,44}
INFO:  aa:{11,22}
ANONYMOUS BLOCK EXECUTE

  • count

    参数：无

    返回值：int类型

    功能描述：返回集合中存在有效元素的个数。

    示例：

    openGauss=# declare
openGauss-# type nest is table of int;
openGauss-# aa nest:=nest(11,22,33,44,55);
openGauss-# begin
openGauss$# raise info 'count:%' ,aa.count;
openGauss$# end;
openGauss$# /
INFO:  count:5
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类型

    功能描述：返回集合中第一个有效元素的下标。

    示例：

    openGauss=# declare
openGauss-# type nest is table of int;
openGauss-# aa nest:=nest(11,22,33,44,55);
openGauss-# begin
openGauss$# raise info 'first:%' ,aa.first;
openGauss$# end;
openGauss$# /
INFO:  first:1
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类型

    功能描述：返回集合中最后一个有效元素的下标。

    示例：

    openGauss=# declare
openGauss-# type nest is table of int;
openGauss-# aa nest:=nest(11,22,33,44,55);
openGauss-# begin
openGauss$# raise info 'last:%' ,aa.last;
openGauss$# end;
openGauss$# /
INFO:  last:5
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类型

    功能描述：返回集合中当前下标的前一个有效元素下标。

    示例：

    openGauss=# declare
openGauss-# type nest is table of int;
openGauss-# aa nest:=nest(11,22,33,44,55);
openGauss-# begin
openGauss$# raise info 'prior:%' ,aa.prior(3);
openGauss$# end;
openGauss$# /
INFO:  prior:2
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类型

    功能描述：返回集合中当前下标的后一个有效元素下标。

    示例：

    openGauss=# declare
openGauss-# type nest is table of int;
openGauss-# aa nest:=nest(11,22,33,44,55);
openGauss-# begin
openGauss$# raise info 'next:%' ,aa.next(3);
openGauss$# end;
openGauss$# /
INFO:  next:4
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返回空。

    示例：

    openGauss=# declare
openGauss-# type nest is table of int;
openGauss-# aa nest:=nest(11,22,33,44,55);
openGauss-# begin
openGauss$# raise info 'limit:%' ,aa.limit;
openGauss$# end;
openGauss$# /
INFO:  limit:<NULL>
ANONYMOUS BLOCK EXECUTE

集合相关函数

  • unnest_table(anynesttable)

    描述：返回nesttable中的元素集合。

    返回类型：setof anyelement

    约束：不支持tableof类型嵌套tableof类型或者tableof嵌套其他类型再嵌套tableof类型的情况。

    示例：

    create or replace procedure f1()
as
    type t1 is table of int;
    v2 t1 := t1(null, 2, 3, 4, null);
    tmp int;
    cursor c1 is select * from unnest_table(v2);
begin
open c1;
for i in 1 .. v2.count loop
    fetch c1 into tmp;
    if tmp is null then
        dbe_output.print_line(i || ': is null');
    else
        dbe_output.print_line(i || ': ' || tmp);
    end if;
end loop;
close c1;
end;
/

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类型。

    示例：

    create or replace procedure f1()
as
    type t1 is table of int index by int;
    v2 t1 := t1(1=>1, -10=>(-10), 6=>6, 4=>null);
    tmp int;
    cursor c1 is select * from unnest_table(v2);
begin
open c1;
for i in 1 .. v2.count loop
    fetch c1 into tmp;
    if tmp is null then
        dbe_output.print_line(i || ': is null');
    else
        dbe_output.print_line(i || ': ' || tmp);
    end if;
end loop;
close c1;
end;
/

openGauss=# call f1();
1: -10
2: 1
3: is null
4: 6
 f1 
----

(1 row)
意见反馈
编组 3备份

您对 openGauss文档 的整体满意度如何？

0
0-不满意10-非常满意

0/500

感谢您的反馈，如需帮助可联系contact@opengauss.org

问题反馈

contact@opengauss.org

    openGauss 2024-04-27 00:42:13
    取消