If you pass array types as arguments to the PL/Perl function, they are passed as the blessed ostgreSQL::InServer::ARRAY objects. In Perl, bless associates an object with a class. This object can be treated as an array reference or as a string. If you have to return an array type, you must return an array by reference. Let's take a look at the following example:

CREATE OR REPLACE FUNCTION reverse(int[]) RETURNS int[]
AS $$
  my $arg = shift; # get the reference of the argument
  my @rev = reverse @{$arg}; # reverse the array
  return @rev # return the array reference
$$ LANGUAGE plperl;

testdb=# select reverse(ARRAY[1,2,3,4]); 
  reverse  
-----------
 {4,3,2,1}
(1 row)

The preceding function reverses an integer array passed to the function using the reverse function of Perl. You can take a look at the comments in the code to understand it. First, we get the reference of the passed argument. We then reverse the array using a reference notation, @{$arg}, and store the result in an array called @rev. In the end, we return the reference of the array using the backslash. If you try to return the array directly, you will get an error.

Let's take a look at another function that concatenates two arrays and then reverses their order:

CREATE OR REPLACE FUNCTION concat_reverse_arrays(int[], int[]) RETURNS int[] 
AS $$
  my $arr1 = $_[0];
  my $arr2 = $_[1];

  push(@{$arr1}, @{$arr2});

  my @reverse = reverse @{$arr1};
  return @reverse;
$$ LANGUAGE plperl;

testdb=# select concat_reverse_arrays(ARRAY[1,2,3],ARRAY[4,5,6]); 
 concat_reverse_arrays
-----------------------
    {6,5,4,3,2,1}

Again, the code is quite simple. It takes two integer arrays as parameters, concatenates them into arr1, and reverses the order using standard Perl functions.

PL/Perl can take arguments of composite types and can also return composite types. The composite types are passed as a reference to hashes, and the keys of the hash are simply attribute names of the passed complex type. Let's take a look at an example:

CREATE TABLE item (item_name varchar, item_price int, discount int);

INSERT INTO item(item_name, item_price) VALUES('macbook',1200);
INSERT INTO item(item_name, item_price) VALUES('pen',5);
INSERT INTO item(item_name, item_price) VALUES('fridge',1000);


CREATE OR REPLACE FUNCTION add_discount(item) RETURNS item
AS $$
  my $item = shift;
  if ($item->{item_price} >= 1000) {
  $item->{discount} = 10; 
  } else {
  $item->{discount} = 5; 
  }

  return $item;
$$ LANGUAGE plperl;

testdb=# select add_discount(item.*) from item;
   add_discount    
-------------------
 (macbook,1200,10)
 (pen,5,5)
 (fridge,1000,10)
(3 rows)

The preceding function is probably not the best use case for a complex type example, but it demonstrates the concepts sufficiently. First, we create a table and fill it up with some data, except the discount field. We then create a function that is passed an item type, and it fills up the discount field based on the price. You can see that since the complex types are passed as hash references, we need to use the $item->{discount} notation to access the keys, where each key corresponds to a field in the type.

You can also return the SETOF primitive and complex types from a PL/Perl function. In the next example, we will try to return a SETOF item type and also demonstrate how to run an SQL query in a PL/Perl function:

CREATE OR REPLACE FUNCTION set_dicounts() RETURNS SETOF item
AS $$
   my $rv = spi_exec_query('select * from item;'),
   my $nrows = $rv->{processed};
    foreach my $rn (0 .. $nrows - 1) {
        my $item = $rv->{rows}[$rn];
          if ($item->{item_price} >= 1000) {
            $item->{discount} = 10; 
          } else {
            $item->{discount} = 5; 
          }
        return_next($item);
    }
    return undef;
$$ LANGUAGE plperl;

postgres=# select * from set_dicounts();
-[ RECORD 1 ]-------
item_name  | macbook
item_price | 1200
discount   | 10
-[ RECORD 2 ]-------
item_name  | pen
item_price | 5
discount   | 5
-[ RECORD 3 ]-------
item_name  | fridge
item_price | 1000
discount   | 10

The first thing you notice about this function, is that it returns a SETOF item type. It uses return_next to build up the result set as it processes rows from the item table. The function is then terminated with a final return undef (you can also use just return). spi_exec_query executes SQL and returns the complete result set as a reference to an array of hash references. If you are dealing with a large number of rows, you may not want to use spi_exec_query as it returns all the rows at once, but you'd rather use the spi_query and spi_fetch_row functions, which allow you to iterate over the data like a cursor.