Get Postgres Tips and Tricks
Subscribe to get advanced Postgres how-tos.
![]() |
![]() |
![]() |
3 Stored Procedure Language : 3.11 Collection Methods
3.11 Collection MethodsCollection methods are functions and procedures that provide useful information about a collection that can aid in the processing of data in the collection. The following sections discuss the collection methods supported by Advanced Server.3.11.1 COUNTCOUNT is a method that returns the number of elements in a collection. The syntax for using COUNT is as follows:collection.COUNTcollection is the name of a collection.The following example shows that an associative array can be sparsely populated (i.e., there are “gaps” in the sequence of assigned elements). COUNT includes only the elements that have been assigned a value.3.11.2 DELETEThe DELETE method deletes entries from a collection. You can call the DELETE method in three different ways.Use the first form of the DELETE method to remove all entries from a collection:collection.DELETEUse the second form of the DELETE method to remove the specified entry from a collection:Use the third form of the DELETE method to remove the entries that are within the range specified by first_subscript and last_subscript (including the entries for the first_subscript and the last_subscript) from a collection.If first_subscript and last_subscript refer to non-existent elements, elements that are in the range between the specified subscripts are deleted. If first_subscript is greater than last_subscript, or if you specify a value of NULL for one of the arguments, DELETE has no effect.Note that when you delete an entry, the subscript remains in the collection; you can re-use the subscript with an alternate entry. If you specify a subscript that does not exist in the call to the DELETE method, DELETE does not raise an exception.The following example demonstrates using the DELETE method to remove the element with subscript 0 from the collection:COUNT indicates that before the DELETE method, there were 5 elements in the collection; after the DELETE method was invoked, the collection contains 4 elements.3.11.3 EXISTSThe EXISTS method verifies that a subscript exists within a collection. EXISTS returns TRUE if the subscript exists; if the subscript does not exist, EXISTS returns FALSE. The method takes a single argument; the subscript that you are testing for. The syntax is:collection is the name of the collection.subscript is the value that you are testing for. If you specify a value of NULL, EXISTS returns false.The following example verifies that subscript number 10 exists within the associative array:Some collection methods raise an exception if you call them with a subscript that does not exist within the specified collection. Rather than raising an error, the EXISTS method returns a value of FALSE.3.11.4 EXTENDThe EXTEND method increases the size of a collection. There are three variations of the EXTEND method. The first variation appends a single NULL element to a collection; the syntax for the first variation is:collection.EXTENDcollection is the name of a collection.The following example demonstrates using the EXTEND method to append a single, null element to a collection:COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 6 elements.The second variation of the EXTEND method appends a specified number of elements to the end of a collection.collection is the name of a collection.count is the number of null elements added to the end of the collection.The following example demonstrates using the EXTEND method to append multiple null elements to a collection:COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 8 elements.The third variation of the EXTEND method appends a specified number of copies of a particular element to the end of a collection.collection is the name of a collection.count is the number of elements added to the end of the collection.index_number is the subscript of the element that is being copied to the collection.The following example demonstrates using the EXTEND method to append multiple copies of the second element to the collection:COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 8 elements.Note: The EXTEND method cannot be used on a null or empty collection.3.11.5 FIRSTFIRST is a method that returns the subscript of the first element in a collection. The syntax for using FIRST is as follows:collection.FIRSTcollection is the name of a collection.3.11.6 LASTLAST is a method that returns the subscript of the last element in a collection. The syntax for using LAST is as follows:collection.LASTcollection is the name of a collection.3.11.7 LIMITLIMIT is a method that returns the maximum number of elements permitted in a collection. LIMIT is applicable only to varrays. The syntax for using LIMIT is as follows:collection.LIMITcollection is the name of a collection.For an initialized varray, LIMIT returns the maximum size limit determined by the varray type definition. If the varray is uninitialized (that is, it is a null varray), an exception is thrown.For an associative array or an initialized nested table, LIMIT returns NULL. If the nested table is uninitialized (that is, it is a null nested table), an exception is thrown.3.11.8 NEXTNEXT is a method that returns the subscript that follows a specified subscript. The method takes a single argument; the subscript that you are testing for.collection.NEXT(subscript)collection is the name of the collection.If the specified subscript is less than the first subscript in the collection, the function returns the first subscript. If the subscript does not have a successor, NEXT returns NULL. If you specify a NULL subscript, PRIOR does not return a value.The following example demonstrates using NEXT to return the subscript that follows subscript 10 in the associative array, sparse_arr:3.11.9 PRIORThe PRIOR method returns the subscript that precedes a specified subscript in a collection. The method takes a single argument; the subscript that you are testing for. The syntax is:collection is the name of the collection.If the subscript specified does not have a predecessor, PRIOR returns NULL. If the specified subscript is greater than the last subscript in the collection, the method returns the last subscript. If you specify a NULL subscript, PRIOR does not return a value.The following example returns the subscript that precedes subscript 100 in the associative array, sparse_arr:3.11.10 TRIMThe TRIM method removes an element or elements from the end of a collection. The syntax for the TRIM method is:collection is the name of a collection.count is the number of elements removed from the end of the collection. Advanced Server will return an error if count is less than 0 or greater than the number of elements in the collection.The following example demonstrates using the TRIM method to remove an element from the end of a collection:COUNT indicates that before the TRIM method, there were 5 elements in the collection; after the TRIM method was invoked, the collection contains 4 elements.You can also specify the number of elements to remove from the end of the collection with the TRIM method:COUNT indicates that before the TRIM method, there were 5 elements in the collection; after the TRIM method was invoked, the collection contains 3 elements.
3 Stored Procedure Language : 3.11 Collection Methods
![]() |
![]() |
![]() |