PostgreSQL 8.0.0rc2 Documentation
Prev | Fast Backward | Chapter 38. PL/Perl - Perl Procedural Language | Fast Forward | Next

# 38.3. Data Values in PL/Perl

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 row set 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;

Prev | Home | Next
Database Access from PL/Perl | Up | Global Values in PL/Perl