Collection Functions
Collection Operators
- =
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
- <>
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
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
- ^=
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
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]
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]
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]
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[()]
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[()]
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[()]
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
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
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