Now, let's write our first simple Perl function to make sure that PL/Perl is installed correctly. We will use a sample function from the Perl FAQs, at http://perldoc.perl.org/perlfaq5.html#How-can-I-output-my-numbers-with-commas-added%3f, to write a PL/Perl function that adds commas to a number:

CREATE OR REPLACE FUNCTION commafy (integer) RETURNS text 
AS $$
  local $_  = shift;
  1 while s/^([-+]?d+)(d{3})/$1,$2/;
  return $_;
$$ LANGUAGE plperl;

This function uses a smartly written regex to add commas to your numbers. Let's try and run it:

testdb=# SELECT commafy(1000000);
 commafy  
-----------
 1,000,000
(1 row)

The code works and the function looks similar to other functions we have been writing in PL/pgSQL and PL/Python. The CREATE FUNCTION statement creates a function. It needs a name, function argument type list (you have to use parentheses, even if there are no arguments), a result type, and a language.

The function body is just an anonymous Perl subroutine. PostgreSQL passes this on to a Perl interpreter, in order to run this subroutine and return the results. Each function is compiled once per session. PL/Perl function arguments are stored in @_, just as in normal Perl subroutines and your code can handle them the same way.

The arguments passed to the PL/Perl function are converted to UTF-8 from the database encoding, and the return value is converted from UTF-8 back to the database encoding.

PL/Perl functions run in a scalar context and cannot return non-scalar types such as lists. You can return the non-scalar types, such as arrays, records, and sets, by returning a reference.