Collection Functions
Collection Operators
- =
Return value: true or false, 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; gaussdbs$# end; gaussdb$# / INFO: t ANONYMOUS BLOCK EXECUTE - <>
Return value: true or false, 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
MULTISET
- MULTISET UNION [ALL | DISTINCT]
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:
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]
Return value: nesttable
Description: Difference of two collection variables. Taking A MULTISET EXCEPT B as an example, ALL indicates that elements that are the same as those in B are removed from A. DISTINCT indicates that duplicate elements are removed from A first and then elements that are the same as those in B are removed from A.
Example:
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]
Return value: nesttable
Description: Intersection of two collection variables. Taking A MULTISET INTERSECT B as an example, ALL indicates that all duplicate elements in A and B are obtained, and DISTINCT indicates that duplicate elements in A and B are obtained and then duplicate elements in this intersection are removed.
Example:
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
Functions of the Collection Type
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, 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: Extends one or count elements at the end of the nest-table variable. Only the nest-table collection type is supported. If index set element idx exists, count index elements are copied to the end of the variable.
Example:
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])]
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.
Example:
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)]
Parameter: n is of the int4 type.
Return value: No value is returned.
Description: Deletes one or n elements and corresponding storage space from a nest-table collection. 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; 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
Return value: int type
Description: Returns the number of valid elements in a collection.
Example:
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
Return value: int or varchar
Description: Returns the index of the first valid element in a collection.
Example:
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
Return value: int or varchar
Description: Returns the index of the last valid element in a collection.
Example:
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)
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:
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)
Parameter: idx is of the int or varchar type.
Return value: int or varchar type
Description: Returns the index of a valid element following the current index in a collection.
Example:
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
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)
Description: Returns a collection of elements in a nest-table.
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
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) -- Example: nested record types in a nest-table 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)
If the collection of elements are of record type and the elements are NULL, a record whose all columns are NULL is returned. For details, see the example.
- unnest_table (anyindexbytable) or unnest (anyindexbytable)
Description: Returns the collection of elements in an index-by table sorted by index.
Return type: setof anyelement
Constraint: Only the index-by int type is supported. The index-by varchar type is not 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
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) -- Example: nested record types in an index-by table 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)
If the collection of elements are of record type and the elements are NULL, a record whose all columns are NULL is returned. For details, see the example.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
