The argument values supplied to a PL/Perl function's code are simply the input arguments converted to text form (just as if they had been displayed by a SELECT statement). Conversely, the return command will accept any string that is acceptable input format for the function's declared return type. So, the PL/Perl programmer can manipulate data values as if they were just text.
PL/Perl can also return row sets and composite types, and row sets of composite types. Here is an example of a PL/Perl function returning a row set of a row type. Note that a composite type is always represented as a hash reference.
CREATE TABLE test ( i int, v varchar ); INSERT INTO test (i, v) VALUES (1, 'first line'); INSERT INTO test (i, v) VALUES (2, 'second line'); INSERT INTO test (i, v) VALUES (3, 'third line'); INSERT INTO test (i, v) VALUES (4, 'immortal'); CREATE FUNCTION test_munge() RETURNS SETOF test AS $$ my $res = []; my $rv = spi_exec_query('select i, v from test;'); my $status = $rv->{status}; my $nrows = $rv->{processed}; foreach my $rn (0 .. $nrows - 1) { my $row = $rv->{rows}[$rn]; $row->{i} += 200 if defined($row->{i}); $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v})); push @$res, $row; } return $res; $$ LANGUAGE plperl; SELECT * FROM test_munge();
Here is an example of a PL/Perl function returning a composite type:
CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text); CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$ return {f2 => 'hello', f1 => 1, f3 => 'world'}; $$ LANGUAGE plperl;
Here is an example of a PL/Perl function returning a row set of a composite type. Since a row set is always a reference to an array and a composite type is always a reference to a hash, a rowset of a composite type is a reference to an array of hash references.
CREATE TYPE testsetperl AS (f1 integer, f2 text, f3 text); CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testsetperl AS $$ return [ { f1 => 1, f2 => 'Hello', f3 => 'World' }, { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } ]; $$ LANGUAGE plperl;