Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ Stored Procedures/ Arrays, Collections, and Records/ Collections/ Collection Functions
Updated on 2025-05-29 GMT+08:00
View PDF
Share

Collection Functions

Collection Operators

  • =

    Parameter: nesttable

    Return value: TRUE or FALSE, of the Boolean type.

    Description: Checks whether two collections are equal.

    Example:

    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:  t
ANONYMOUS BLOCK EXECUTE
  • <>

    Parameter: nesttable

    Return value: TRUE or FALSE, of the Boolean type.

    Description: Checks whether two collections are not equal.

    Example:

    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
  • IS [NOT] NULL

    Parameter: nesttable

    Return value: TRUE or FALSE, of the Boolean type.

    Description: Determines whether a collection is NULL.

    Example:

    gaussdb=# declare
gaussdb=#     type nest is table of int;
gaussdb=#     a nest;
gaussdb=#     b nest := NULL;
gaussdb=#     c nest := nest(1,2);
gaussdb=#     flag bool; 
gaussdb=# begin
gaussdb=#     flag := a IS NULL;
gaussdb=#     raise info '%', flag;
gaussdb=#     flag := b IS NULL;
gaussdb=#     raise info '%', flag;
gaussdb=#     flag := c IS NULL;
gaussdb=#     raise info '%', flag;
gaussdb=# end;
gaussdb=# /
INFO:  t
INFO:  t
INFO:  f
ANONYMOUS BLOCK EXECUTE
  • ^=

    Parameter: nesttable

    Return value: TRUE or FALSE, of the Boolean type.

    Description: Checks whether two collections are not equal.

    Example:

    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
  • [NOT] IN

    Parameter: nesttable

    Return value: TRUE or FALSE, of the Boolean type.

    Description: Determines whether a collection is in the collection list.

    Example:

    gaussdb=# declare
gaussdb=#     type tab is table of varchar(10);
gaussdb=#     tmp1 tab := tab('a', 'b');
gaussdb=#     tmp2 tab := tab('a', 'b');
gaussdb=#     flag bool; 
gaussdb=# begin
gaussdb=#     flag := tmp2 in (tmp1);
gaussdb=#     raise info '%', flag;
gaussdb=# end;
gaussdb=# /
INFO:  t
ANONYMOUS BLOCK EXECUTE

MULTISET

  • MULTISET UNION [ALL | DISTINCT]

    Parameter: nesttable

    Return value: nesttable

    Description: Union of two collection variables. ALL indicates that duplicate elements are not removed, and DISTINCT indicates that duplicate elements are removed.

    Example 1: Calculate the union of two collection variables and do not remove duplicate elements. That is, MULTISET UNION ALL.

    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

    Example 2: Calculate the union of two collection variables and remove duplicate elements. That is, MULTISET UNION 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 DISTINCT b;
gaussdb$#     raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO:  {1,2,3}
ANONYMOUS BLOCK EXECUTE
  • MULTISET EXCEPT [ALL | DISTINCT]

    Parameter: nesttable

    Return value: nesttable

    Description: Difference of two collection variables. Take A MULTISET EXCEPT B as an example. ALL indicates that elements that both A and B have in A are removed and returned. The number of elements to be removed is calculated. For a specific element, if A appears m times and B appears n times (m > n), the number of elements removed from A is m minus n. If m ≤ n, m elements are removed from A. DISTINCT indicates that elements that both A and B have in A are removed and returned. The number of elements to be removed is not calculated. If specific elements appear in A and B, all such elements in A are removed. ALL is the default value.

    Example 1: Calculate a difference set of two collection variables, remove elements that both A and B have in A, and return result. The number of elements to be removed is calculated. That is, MULTISET EXCEPT ALL.

    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 ALL b;
gaussdb$#     raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO:  {1,2}
ANONYMOUS BLOCK EXECUTE

    Example 2: Calculate a difference set of two collection variables, remove elements that both A and B have in A, and return result. The number of elements to be removed is not calculated. That is, MULTISET EXCEPT DISTINCT.

    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]

    Parameter: nesttable

    Return value: nesttable

    Description: Intersection of two collection variables. For example, A MULTISET INTERSECT B: ALL indicates that all duplicate elements (including elements that are repeated for multiple times) are retained. DISTINCT indicates that the intersection of unique values is returned and duplicate elements are removed. ALL is the default value.

    Example 1: Calculate the intersection of two collection variables and do not remove duplicate elements. That is, MULTISET INTERSECT ALL.

    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 ALL b;
gaussdb$#     raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO:  {2,2}
ANONYMOUS BLOCK EXECUTE

    Example 2: Calculate the intersection of two collection variables and remove duplicate elements. That is, MULTISET INTERSECT DISTINCT.

    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

Functions of the Collection Type

  • In the following function definition description, the content in [] is optional. For example, count[()] can be written as count or count().
  • In an inner expression, functions of the collection type cannot be called in nesting mode.
  • exists(idx)

    Parameter: idx is of the INT4 or VARCHAR type.

    Return value: TRUE or FALSE, of the Boolean type.

    Description: Checks whether a valid element exists in a specified position.

    Example:

    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])]

    Parameters: idx and count are of the INT4 type.

    Return value: No value is returned.

    Description: Supports only variables of the nest-table type. One or count elements are extended at the end of the nest-table variable. If index set element idx exists, count index elements are copied to the end of the variable.

    Example 1: A nest-table variable extends 1 element and the extended element value is NULL.

    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$#     a.extend;
gaussdb$#     raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO:  {1}
INFO:  {1,NULL}
INFO:  {1,NULL,NULL}
ANONYMOUS BLOCK EXECUTE

    Example 2: A nest-table variable extends n elements and the extended element value is NULL.

    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

    Example 3: A nest-table variable extends n elements and the extended element value is the value of the specified index element.

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

    Parameter: idx1 and idx2 are of the INT4 or VARCHAR2 type.

    Return value: No value is returned.

    Description: Deletes all elements and releases corresponding storage space in a nest-table collection (to use this collection, extend must be executed again), or deletes all elements (including index set elements) in an index-by table collection but does not release corresponding storage space.

    Restriction: For the nest-table collection type, if an element is deleted but it is not removed from the storage space, the space is invalid and can be assigned a value. Note: When a collection is used as the return value of a function or an input parameter of an autonomous transaction, invalid members will be deleted from the storage space, and the index sets of all members change accordingly.

    Example 1: Delete all elements and spaces of the collection type from the variable of the nest-table collection type.

    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

    Example 2: Delete an element at a specified position from the variable of the nest-table collection type.

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

    Example 3: Delete elements in a specified interval from the variable of the nest-table collection type.

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

    Example 4: Delete all elements and spaces of the collection type from the variable of the index-by table collection type.

    gaussdb=# declare
gaussdb-#     type t1 is table of int index by varchar;
gaussdb-#     v t1 := t1('a' => 1, 'b' => 2, 'c' => 3, 'd' => 4);
gaussdb-# begin
gaussdb$#     v.delete();
gaussdb$#     raise info '%', v.count();
gaussdb$# end;
gaussdb$# /
INFO:  0
ANONYMOUS BLOCK EXECUTE

    Example 5: Delete an element at a specified position from the variable of the index-by table collection type.

    gaussdb=# declare
gaussdb-#     type t1 is table of int index by varchar;
gaussdb-#     v t1 := t1('a' => 1, 'b' => 2, 'c' => 3, 'd' => 4);
gaussdb-# begin
gaussdb$#     raise info '%', v('a');
gaussdb$#     v.delete('a');
gaussdb$#     raise info '%', v('a');
gaussdb$# end;
gaussdb$# /
INFO:  1
ERROR:  no data found
CONTEXT:  PL/pgSQL function inline_code_block line 6 at RAISE

    Example 6: Delete elements in a specified interval from the variable of the index-by table collection type.

    gaussdb=# declare
gaussdb-#     type t1 is table of int index by varchar;
gaussdb-#     v t1 := t1('a' => 1, 'b' => 2, 'c' => 3, 'd' => 4);
gaussdb-# begin
gaussdb$#     raise info '%', v('b');
gaussdb$#     v.delete('a', 'c');
gaussdb$#     raise info '%', v('b');
gaussdb$# end;
gaussdb$# /
INFO:  2
ERROR:  no data found
CONTEXT:  PL/pgSQL function inline_code_block line 6 at RAISE
  • trim[(n)]

    Parameter: n is of the INT4 type.

    Return value: No value is returned.

    Description: Supports only variables of the nest-table type. Only the nest-table collection type is supported.

    Example:

    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;  -- No parameter
gaussdb$#     raise info 'aa:%' ,aa;
gaussdb$#     aa.trim();  -- No parameter
gaussdb$#     raise info 'aa:%' ,aa;
gaussdb$#     aa.trim(2);  -- Valid parameter
gaussdb$#     raise info 'aa:%' ,aa;
gaussdb$#     aa.trim(2);  -- The collection element space is less than 2. An error is reported when the parameter is invalid.
gaussdb$# end;
gaussdb$# /
INFO:  aa:{11,22,33,44,55}
INFO:  aa:{11,22,33,44}
INFO:  aa:{11,22,33}
INFO:  aa:{11}
ERROR:  Subscript beyond count
CONTEXT:  PL/pgSQL function inline_code_block line 11 at assignment
  • count[()]

    Parameter: none

    Return value: INT type

    Description: Returns the number of valid elements in a collection.

    Example 1: Use the count function for the variable of the nest-table collection type.

    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$# aa.delete(3);  -- Delete an element. The element whose index is 3 is invalid.
gaussdb$# raise info 'count:%' ,aa.count();
gaussdb$# end;
gaussdb$# /
INFO:  count:5
INFO:  count:4
ANONYMOUS BLOCK EXECUTE

    Example 2: Use the count function for the variable of the index-by table collection type.

    gaussdb=# declare
gaussdb-# type t1 is table of int index by int;
gaussdb-# aa t1;
gaussdb-# begin
gaussdb$# aa(1) := 111;
gaussdb$# aa(2) := 222;
gaussdb$# aa(3) := 333;
gaussdb$# raise info 'count:%' ,aa.count();
gaussdb$# end;
gaussdb$# /
INFO:  count:3
ANONYMOUS BLOCK EXECUTE

gaussdb=# declare
gaussdb-# type t1 is table of int index by varchar;
gaussdb-# aa t1;
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[()]

    Parameter: none

    Return value: INT or VARCHAR

    Description: Returns the index of the first valid element in a collection.

    Example 1: Use the first function for the variable of the nest-table collection type.

    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$# aa.delete(1);
gaussdb$# raise info 'first:%' ,aa.first;  -- The element whose index is 1 is invalid. The index of the first valid element is 2.
gaussdb$# end;
gaussdb$# /
INFO:  first:1
INFO:  first:2
ANONYMOUS BLOCK EXECUTE

    Example 2: Use the first function for the variable of the index-by table collection type.

    gaussdb=# declare
gaussdb-# type t1 is table of int index by int;
gaussdb-# aa t1;
gaussdb-# begin
gaussdb$# aa(3) := 111;
gaussdb$# aa(2) := 222;
gaussdb$# aa(1) := 333;
gaussdb$# raise info 'first:%' ,aa.first;
gaussdb$# end;
gaussdb$# /
INFO:  first:1
ANONYMOUS BLOCK EXECUTE

gaussdb=# declare
gaussdb-# type t1 is table of int index by varchar;
gaussdb-# aa t1;
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[()]

    Parameter: none

    Return value: INT or VARCHAR

    Description: Returns the index of the last valid element in a collection.

    Example 1: Use the last function for the variable of the nest-table collection type.

    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$# aa.delete(5);
gaussdb$# raise info 'last:%' ,aa.last();  -- The element whose index is 5 is invalid. The index of the last valid element is 4.
gaussdb$# end;
gaussdb$# /
INFO:  last:5
INFO:  last:4
ANONYMOUS BLOCK EXECUTE

    Example 2: Use the last function for the variable of the index-by table collection type.

    gaussdb=# declare
gaussdb-# type t1 is table of int index by varchar;
gaussdb-# aa t1;
gaussdb-# begin
gaussdb$# aa(3) := 111;
gaussdb$# aa(2) := 222;
gaussdb$# aa(1) := 333;
gaussdb$# raise info 'last:%' ,aa.last();
gaussdb$# end;
gaussdb$# /
INFO:  last:3
ANONYMOUS BLOCK EXECUTE

gaussdb=# declare
gaussdb-# type t1 is table of int index by varchar;
gaussdb-# aa t1;
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)

    Parameter: idx is of the int or varchar type.

    Return value: int or varchar type

    Description: Returns the index of a valid element before the current index in a collection.

    Example 1: Use the prior function for the variable of the nest-table collection type.

    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

    Example 2: Use the prior function for the variable of the index-by table collection type.

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

    Parameter: idx is of the INT or VARCHAR type.

    Return value: INT or VARCHAR

    Description: Returns the index of a valid element following the given index in a collection.

    Example 1: Use the next function for the variable of the nest-table collection type.

    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

    Example 2: Use the next function for the variable of the index-by table collection type.

    gaussdb=# declare
gaussdb-# type t1 is table of int index by int;
gaussdb-# aa t1;
gaussdb-# begin
gaussdb$# aa(3) := 111;
gaussdb$# aa(2) := 222;
gaussdb$# aa(1) := 333;
gaussdb$# raise info 'next:%' ,aa.next(2);
gaussdb$# raise info 'next:%' ,aa.next(-999);
gaussdb$# end;
gaussdb$# /
INFO:  next:3
INFO:  next:1
ANONYMOUS BLOCK EXECUTE
  • limit

    Parameter: none

    Return value: NULL.

    Description: Returns the maximum number of elements that can be stored in a nest-table collection. This function applies only to the array type. The return value is NULL.

    Example:

    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

Collection-related Functions

  • unnest_table (anynesttable) or unnest (anynesttable)

    Parameter: any nest-table collection type.

    Description: Returns the result set of all valid elements in a given nest-table. Multiple rows of data are returned.

    Return type: setof anyelement.

    Example:

     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
60
61
62
63
    		 
    -- If the element type of the collection is record and any element is NULL, the element is not returned. Instead, a non-null record value is returned. All columns of the record are NULL.
gaussdb=# create or replace procedure p1()
gaussdb-# as
gaussdb$#     type t1 is table of int;
gaussdb$#     v2 t1 := t1(null, 2, 3, 4, null);
gaussdb$#     tmp int;
gaussdb$#     cursor c1 is select * from unnest_table(v2);
gaussdb$# begin
gaussdb$# open c1;
gaussdb$# for i in 1 .. v2.count loop
gaussdb$#     fetch c1 into tmp;
gaussdb$#     if tmp is null then
gaussdb$#         dbe_output.print_line(i || ': is null');
gaussdb$#     else
gaussdb$#         dbe_output.print_line(i || ': ' || tmp);
gaussdb$#     end if;
gaussdb$# end loop;
gaussdb$# close c1;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# call p1();
1: is null
2: 2
3: 3
4: 4
5: is null
 p1 
----

(1 row)
gaussdb=# drop procedure if exists p1();
DROP PROCEDURE

-- Example: nested record types in a nest-table
gaussdb=# create or replace procedure p1() is
gaussdb$#   type rec is record(c1 int, c2 int);
gaussdb$#   type t1 is table of rec;
gaussdb$#   v t1 := t1(rec(1, 1), rec(2, null), rec(null, null), null);
gaussdb$#   v2 t1 := t1();
gaussdb$#   cursor cur is select * from unnest(v);
gaussdb$# begin
gaussdb$#   v2.extend(v.count);
gaussdb$#   open cur;
gaussdb$#   for i in 1 .. v.count loop
gaussdb$#     fetch cur into v2(i);
gaussdb$#     raise info '%', v2(i);
gaussdb$#   end loop;
gaussdb$#   close cur;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# call p1();
INFO:  (1,1)
INFO:  (2,)
INFO:  (,)
INFO:  (,)
 p1 
----

(1 row)
gaussdb=# drop procedure if exists p1();
DROP PROCEDURE
  • unnest_table (anyindexbytable) or unnest (anyindexbytable)

    Parameter: any index-by table collection type.

    Description: Returns the result set of all elements sorted by index in a given index-by table. Multiple rows of data are returned.

    Return type: setof anyelement

    Constraint: Only the INDEX BY INT type is supported.

    Example:

     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
60
61
    		 
    -- If the element type of the collection is record and any element is NULL, the element is not returned. Instead, a non-null record value is returned. All columns of the record are NULL.
gaussdb=# create or replace procedure p1()
gaussdb-# as
gaussdb$#     type t1 is table of int index by int;
gaussdb$#     v2 t1 := t1(1=>1, -10=>(-10), 6=>6, 4=>null);
gaussdb$#     tmp int;
gaussdb$#     cursor c1 is select * from unnest_table(v2);
gaussdb$# begin
gaussdb$# open c1;
gaussdb$# for i in 1 .. v2.count loop
gaussdb$#     fetch c1 into tmp;
gaussdb$#     if tmp is null then
gaussdb$#         dbe_output.print_line(i || ': is null');
gaussdb$#     else
gaussdb$#         dbe_output.print_line(i || ': ' || tmp);
gaussdb$#     end if;
gaussdb$# end loop;
gaussdb$# close c1;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# call p1();
1: -10
2: 1
3: is null
4: 6
 p1 
----

(1 row)
gaussdb=# drop procedure if exists p1();
DROP PROCEDURE

-- Example: nested record types in an index-by table
gaussdb=# create or replace procedure p1() is
gaussdb$#   type rec is record(c1 int, c2 int);
gaussdb$#   type t1 is table of rec index by int;
gaussdb$#   v t1 := t1(1 => rec(1, 1), 2 => rec(2, null), 3 => rec(null, null), 4 => null);
gaussdb$#   v2 t1 := t1();
gaussdb$#   cursor cur is select * from unnest(v);
gaussdb$# begin
gaussdb$#   open cur;
gaussdb$#   for i in 1 .. v.count loop
gaussdb$#     fetch cur into v2(i);
gaussdb$#     raise info '%', v2(i);
gaussdb$#   end loop;
gaussdb$#   close cur;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# call p1();
INFO:  (1,1)
INFO:  (2,)
INFO:  (,)
INFO:  (,)
 p1 
----

(1 row)
gaussdb=# drop procedure if exists p1();
DROP PROCEDURE
Parent topic: Collections