Passing and returning arrays

If you pass array types as an argument to the PL/Tcl function, they are passed as a string value, along with the brackets and the commas. Let's take a look at an example:

CREATE OR REPLACE FUNCTION tcl_array_test(integer[]) RETURNS int
AS $$
    set length [string length $1]
    return $length
$$ LANGUAGE pltcl;

testdb=# select tcl_array_test(ARRAY[1,2,3]);
 tcl_array_test 
----------------
       7
    (1 row)

You are probably surprised at the return value of the preceding function. You passed an integer array to the function that is converted to a string value {1,2,3}, the length of which is indeed 7. If you want to process array values independently, you need a bit of string manipulation to extract the list out of the string, do the manipulation, and convert it back to the string format that you received it in.

Let's take a look at an example PL/Tcl function that will reverse an integer array and return the reversed integer array:

CREATE OR REPLACE FUNCTION tcl_reverse_array(integer[]) RETURNS integer[]
AS $$
  set lst [regexp -all -inline {[0-9]} $1]
  set lst [join [lreverse $lst] ","]
  set lst  "{$lst}"
  return $lst
$$ LANGUAGE pltcl;
postgres=# select tcl_reverse_array(ARRAY[1,2,3]);
 tcl_reverse_array 
-------------------
 {3,2,1}
(1 row)

The preceding function does the following:

  1. The tcl_reverse_array function cleans up the input parameter by creating a list out of the string and removing the { and , characters. This is done using a regular expression and by only extracting numeric values out of the string.
  2. It uses the lreverse function to reverse the contents of the list and then join the elements of the list back as an array, using the join function and using , as the join character.
  3. Then, it adds brackets to the string before returning it. The return string is converted to an integer array as it is returned by the function.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.116.64.221