更新时间:2024-05-07 GMT+08:00

集合支持的函数

集合操作符

  • =

    参数:nesttable类型

    返回值:true or false,bool类型

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

    示例:

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

    参数:nesttable类型

    返回值:true or false,bool类型

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

    示例:

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

集合MULTISET函数

  • MULTISET UNION [ALL | DISTINCT]

    参数:nesttable类型

    返回值:nesttable类型

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

    示例:

    gaussdb=# declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1,2);
    gaussdb-#     b nest := nest(2,3);
    gaussdb-# begin
    gaussdb$#     a := a MULTISET UNION ALL b;
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {1,2,2,3}
    ANONYMOUS BLOCK EXECUTE
     
    gaussdb=# declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1,2);
    gaussdb-#     b nest := nest(2,3);
    gaussdb-# begin
    gaussdb$#     a := a MULTISET UNION DISTINCT b;
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {1,2,3}
    ANONYMOUS BLOCK EXECUTE
  • MULTISET EXCEPT [ALL | DISTINCT]

    参数:nesttable类型

    返回值:nesttable类型

    功能描述:两个集合变量的差集。如A MULTISET EXCEPT B:ALL表示去除A中与B重复的元素;DISTINCT表示先对A进行去重操作,然后去除与B中有重复的元素。

    示例:

    gaussdb=# declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1,2,2);
    gaussdbs-#     b nest := nest(2,3);
    gaussdb-# begin
    gaussdb$#     a := a MULTISET EXCEPT ALL b;
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {1,2}
    ANONYMOUS BLOCK EXECUTE
     
    gaussdb=# declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1,2,2);
    gaussdb-#     b nest := nest(2,3);
    gaussdb-# begin
    gaussdb$#     a := a MULTISET EXCEPT DISTINCT b;
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {1}
    ANONYMOUS BLOCK EXECUTE
  • MULTISET INTERSECT [ALL | DISTINCT]

    参数:nesttable类型

    返回值:nesttable类型

    功能描述:两个集合变量的交集。如 A MULTISET INTERSECT B:ALL表是取A与B所有重复的元素;DISTINCT表示取A与B中重复元素,且去除重复元素。

    示例:

    gaussdb=#  declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1,2,2);
    gaussdbs-#     b nest := nest(2,2,3);
    gaussdb-# begin
    gaussdb$#     a := a MULTISET INTERSECT ALL b;
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {2,2}
    ANONYMOUS BLOCK EXECUTE
     
    gaussdb=# declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1,2,2);
    gaussdb-#     b nest := nest(2,2,3);
    gaussdb-# begin
    gaussdb$#     a := a MULTISET INTERSECT DISTINCT b;
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {2}
    ANONYMOUS BLOCK EXECUTE

集合类型函数

在内层表达式中,不支持通过嵌套的方式调用集合类型函数。

  • exists(idx)

    参数:idx为int4类型或varchar类型,

    返回值:true or false,bool类型

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

    示例:

    gaussdb=# declare
    gaussdb-#     type nest is table of varchar2;
    gaussdb-#     a nest := nest('happy','?');
    gaussdb-#     flag bool;
    gaussdb-# begin
    gaussdb$#     flag := a.exists(1);
    gaussdb$#     raise info '%', flag;
    gaussdb$#     flag := a.exists(10);
    gaussdb$#     raise info '%', flag;
    gaussdb$# end;
    gaussdb$# /
    INFO:  t
    INFO:  f
    ANONYMOUS BLOCK EXECUTE
     
    gaussdb=# declare
    gaussdb-#     type nest is table of varchar2 index by varchar2;
    gaussdb-#     a nest;
    gaussdb-#     flag bool;
    gaussdb-# begin
    gaussdb$#     a('1') := 'Be';
    gaussdb$#     a('2') := 'happy';
    gaussdb$#     a('3') := '.';
    gaussdb$#     flag := a.exists('1');
    gaussdb$#     raise info '%', flag;
    gaussdb$#     flag := a.exists('ddd');
    gaussdb$#     raise info '%', flag;
    gaussdb$# end;
    gaussdb$# /
    INFO:  t
    INFO:  f
    ANONYMOUS BLOCK EXECUTE
  • extend[(count[, idx])]

    参数:idx和count为int4类型

    返回值:无返回值

    功能描述:仅支持nesttable类型。在nesttable变量末尾拓展1个或count个元素。存在idx下标元素时,拷贝count个idx下元素到变量末尾。

    示例:

    gaussdb=# declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1);
    gaussdb-# begin
    gaussdb$#     raise info '%', a;
    gaussdb$#     a.extend;
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {1}
    INFO:  {1,NULL}
    ANONYMOUS BLOCK EXECUTE
     
    gaussdb=# declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1);
    gaussdb-# begin
    gaussdb$#     raise info '%', a;
    gaussdb$#     a.extend(2);
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {1}
    INFO:  {1,NULL,NULL}
    ANONYMOUS BLOCK EXECUTE
     
    gaussdb=# declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1);
    gaussdb-# begin
    gaussdb$#     raise info '%', a;
    gaussdb$#     a.extend(2,1);
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {1}
    INFO:  {1,1,1}
    ANONYMOUS BLOCK EXECUTE
  • delete[(idx1[, idx2])]

    参数:idx1和idx2为int4类型或varchar2类型

    返回值:无返回值

    功能描述:无参数时,(nesttable)删除集合类型的所有元素和空间,后续使用需要重新extend,(indexbytable)删除所有元素内容,一个参数删除指定位置元素(不删除空间),两个参数删除小标区间内的元素(不删除空间)。

    示例:

    gaussdb=# declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1,2,3,4,5);
    gaussdb-# begin
    gaussdb$#     raise info '%', a;
    gaussdb$#     a.delete;
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {1,2,3,4,5}
    INFO:  {}
    ANONYMOUS BLOCK EXECUTE
     
    gaussdb=# declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1,2,3,4,5);
    gaussdb-# begin
    gaussdb$#     raise info '%', a;
    gaussdb$#     a.delete(3);
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {1,2,3,4,5}
    INFO:  {1,2,4,5}
    ANONYMOUS BLOCK EXECUTE
     
    gaussdb=# declare
    gaussdb-#     type nest is table of int;
    gaussdb-#     a nest := nest(1,2,3,4,5);
    gaussdb-# begin
    gaussdb$#     raise info '%', a;
    gaussdb$#     a.delete(2,4);
    gaussdb$#     raise info '%', a;
    gaussdb$# end;
    gaussdb$# /
    INFO:  {1,2,3,4,5}
    INFO:  {1,5}
    ANONYMOUS BLOCK EXECUTE
  • trim[(n)]

    参数:n为int4类型

    返回值:无返回值

    功能描述:仅支持nesttable类型,无参数时,删除末尾一个元素空间,输入参数合法时,删除末尾指定数量元素空间。

    示例:

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

    参数:无

    返回值:int类型

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

    示例:

    gaussdb=# declare
    gaussdb-# type nest is table of int;
    gaussdb-# aa nest:=nest(11,22,33,44,55);
    gaussdb-# begin
    gaussdb$# raise info 'count:%' ,aa.count;
    gaussdb$# end;
    gaussdb$# /
    INFO:  count:5
    ANONYMOUS BLOCK EXECUTE
    
    gaussdb=# declare
    gaussdb-# type nest is table of int index by varchar;
    gaussdb-# aa nest;
    gaussdb-# begin
    gaussdb$# aa('aaa') := 111;
    gaussdb$# aa('bbb') := 222;
    gaussdb$# aa('ccc') := 333;
    gaussdb$# raise info 'count:%' ,aa.count;
    gaussdb$# end;
    gaussdb$# /
    INFO:  count:3
    ANONYMOUS BLOCK EXECUTE
  • first

    参数:无

    返回值:int类型或varchar类型

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

    示例:

    gaussdb=# declare
    gaussdb-# type nest is table of int;
    gaussdb-# aa nest:=nest(11,22,33,44,55);
    gaussdb-# begin
    gaussdb$# raise info 'first:%' ,aa.first;
    gaussdb$# end;
    gaussdb$# /
    INFO:  first:1
    ANONYMOUS BLOCK EXECUTE
    
    gaussdb=# declare
    gaussdb-# type nest is table of int index by varchar;
    gaussdb-# aa nest;
    gaussdb-# begin
    gaussdb$# aa('aaa') := 111;
    gaussdb$# aa('bbb') := 222;
    gaussdb$# aa('ccc') := 333;
    gaussdb$# raise info 'first:%' ,aa.first;
    gaussdb$# end;
    gaussdb$# /
    INFO:  first:aaa
    ANONYMOUS BLOCK EXECUTE
  • last

    参数:无

    返回值:int类型或varchar类型

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

    示例:

    gaussdb=# declare
    gaussdb-# type nest is table of int;
    gaussdb-# aa nest:=nest(11,22,33,44,55);
    gaussdb-# begin
    gaussdb$# raise info 'last:%' ,aa.last;
    gaussdb$# end;
    gaussdb$# /
    INFO:  last:5
    ANONYMOUS BLOCK EXECUTE
    
    gaussdb=# declare
    gaussdb-# type nest is table of int index by varchar;
    gaussdb-# aa nest;
    gaussdb-# begin
    gaussdb$# aa('aaa') := 111;
    gaussdb$# aa('bbb') := 222;
    gaussdb$# aa('ccc') := 333;
    gaussdb$# raise info 'last:%' ,aa.last;
    gaussdb$# end;
    gaussdb$# /
    INFO:  last:ccc
    ANONYMOUS BLOCK EXECUTE
  • prior(idx)

    参数:idx为int类型或varchar类型

    返回值:int类型或varchar类型

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

    示例:

    gaussdb=# declare
    gaussdb-# type nest is table of int;
    gaussdb-# aa nest:=nest(11,22,33,44,55);
    gaussdb-# begin
    gaussdb$# raise info 'prior:%' ,aa.prior(3);
    gaussdb$# end;
    gaussdb$# /
    INFO:  prior:2
    ANONYMOUS BLOCK EXECUTE
    
    gaussdb=# declare
    gaussdb-# type nest is table of int index by varchar;
    gaussdb-# aa nest;
    gaussdb-# begin
    gaussdb$# aa('aaa') := 111;
    gaussdb$# aa('bbb') := 222;
    gaussdb$# aa('ccc') := 333;
    gaussdb$# raise info 'prior:%' ,aa.prior('bbb');
    gaussdb$# end;
    gaussdb$# /
    INFO:  prior:aaa
    ANONYMOUS BLOCK EXECUTE
  • next(idx)

    参数:idx为int类型或varchar类型

    返回值:int类型或varchar类型

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

    示例:

    gaussdb=# declare
    gaussdb-# type nest is table of int;
    gaussdb-# aa nest:=nest(11,22,33,44,55);
    gaussdb-# begin
    gaussdb$# raise info 'next:%' ,aa.next(3);
    gaussdb$# end;
    gaussdb$# /
    INFO:  next:4
    ANONYMOUS BLOCK EXECUTE
    
    gaussdb=#  declare
    gaussdb-# type nest is table of int index by varchar;
    gaussdb-# aa nest;
    gaussdb-# begin
    gaussdb$# aa('aaa') := 111;
    gaussdb$# aa('bbb') := 222;
    gaussdb$# aa('ccc') := 333;
    gaussdb$# raise info 'next:%' ,aa.next('bbb');
    gaussdb$# end;
    gaussdb$# /
    INFO:  next:ccc
    ANONYMOUS BLOCK EXECUTE
  • limit

    参数:无

    返回值:null

    功能描述:用于nesttable类型,返回集合中最大可以储存的元素个数,只适用于array类型,nesttable返回空。

    示例:

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

集合相关函数

  • unnest_table(anynesttable)或unnest(anynesttable)

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

    返回类型:setof anyelement

    示例:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    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;
    /
    
    gaussdb=# call f1();
    1: is null
    2: 2
    3: 3
    4: 4
    5: is null
     f1 
    ----
    
    (1 row)
    
    -- nesttable嵌套record类型示例
    create or replace procedure p1() is
      type rec is record(c1 int, c2 int);
      type t1 is table of rec;
    
      v t1 := t1(rec(1, 1), rec(2, null), rec(null, null), null);
      v2 t1 := t1();
      cursor cur is select * from unnest(v);
    begin
      v2.extend(v.count);
      open cur;
      for i in 1 .. v.count loop
        fetch cur into v2(i);
        raise info '%', v2(i);
      end loop;
      close cur;
    end;
    /
    
    gaussdb=# call p1();
    INFO:  (1,1)
    INFO:  (2,)
    INFO:  (,)
    INFO:  (,)
     p1 
    ----
    
    (1 row)
    

    当集合的元素类型为record类型且元素为NULL时,会返回一个所有列为NULL的record,具体可参考示例。

  • unnest_table(anyindexbytable)或unnest(anyindexbytable)

    描述:返回table of index by类型根据index排序后的元素集合。

    返回类型:setof anyelement

    约束:只支持index by int类型,不支持index by varchar类型。

    示例:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    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;
    /
    
    gaussdb=# call f1();
    1: -10
    2: 1
    3: is null
    4: 6
     f1 
    ----
    
    (1 row)
    
    -- index by table嵌套record类型示例
    create or replace procedure p1() is
      type rec is record(c1 int, c2 int);
      type t1 is table of rec index by int;
    
      v t1 := t1(1 => rec(1, 1), 2 => rec(2, null), 3 => rec(null, null), 4 => null);
      v2 t1 := t1();
      cursor cur is select * from unnest(v);
    begin
      open cur;
      for i in 1 .. v.count loop
        fetch cur into v2(i);
        raise info '%', v2(i);
      end loop;
      close cur;
    end;
    /
    
    gaussdb=# call p1();
    INFO:  (1,1)
    INFO:  (2,)
    INFO:  (,)
    INFO:  (,)
     p1 
    ----
    
    (1 row)
    

    当集合的元素类型为record类型且元素为NULL时,会返回一个所有列为NULL的record,具体可参考示例。