40.3. Built-in Functions

spi_exec_query executes an SQL command and returns the entire row set as a reference to an array of hash references. You should only use this command when you know that the result set will be relatively small. Here is an example of a query (SELECT command) with the optional maximum number of rows:

$rv = spi_exec_query('SELECT * FROM my_table', 5);

This returns up to 5 rows from the table my_table. If my_table has a column my_column, you can get that value from row $i of the result like this:

$foo = $rv->{rows}[$i]->{my_column};

The total number of rows returned from a SELECT query can be accessed like this:

$nrows = $rv->{processed}

Here is an example using a different command type:

$query = "INSERT INTO my_table VALUES (1, 'test')";
$rv = spi_exec_query($query);

You can then access the command status (e.g., SPI_OK_INSERT) like this:

$res = $rv->{status};

To get the number of rows affected, do:

$nrows = $rv->{processed};

Here is a complete example:

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 OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
    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}));
        return_next($row);
    }
    return undef;
$$ LANGUAGE plperl;

SELECT * FROM test_munge();

spi_query and spi_fetchrow work together as a pair for row sets which might be large, or for cases where you wish to return rows as they arrive. spi_fetchrow works only with spi_query. The following example illustrates how you use them together:

CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);

CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
    use Digest::MD5 qw(md5_hex);
    my $file = '/usr/share/dict/words';
    my $t = localtime;
    elog(NOTICE, "opening file $file at $t" );
    open my $fh, '<', $file # ooh, it's a file access!
        or elog(ERROR, "cannot open $file for reading: $!");
    my @words = <$fh>;
    close $fh;
    $t = localtime;
    elog(NOTICE, "closed file $file at $t");
    chomp(@words);
    my $row;
    my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
    while (defined ($row = spi_fetchrow($sth))) {
        return_next({
            the_num => $row->{a},
            the_text => md5_hex($words[rand @words])
        });
    }
    return;
$$ LANGUAGE plperlu;

SELECT * from lotsa_md5(500);

Normally, spi_fetchrow should be repeated until it returns undef, indicating that there are no more rows to read. The cursor returned by spi_query is automatically freed when spi_fetchrow returns undef. If you do not wish to read all the rows, instead call spi_cursor_close to free the cursor. Failure to do so will result in memory leaks.

PREPARE is not supported in the current release of Postgres-XC. This may be supported in the future releases.

Emit a log or error message. Possible levels are DEBUG, LOG, INFO, NOTICE, WARNING, and ERROR. ERROR raises an error condition; if this is not trapped by the surrounding Perl code, the error propagates out to the calling query, causing the current transaction or subtransaction to be aborted. This is effectively the same as the Perl die command. The other levels only generate messages of different priority levels. Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 17 for more information.

Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns undef on undef input; if the argument might be undef, quote_nullable is often more suitable.

Return the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is undef, return the unquoted string "NULL". Embedded single-quotes and backslashes are properly doubled.

Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled.

Return the unescaped binary data represented by the contents of the given string, which should be bytea encoded.

Return the bytea encoded form of the binary data contents of the given string.

Returns the contents of the referenced array as a string in array literal format (see Section 8.14.2). Returns the argument value unaltered if it's not a reference to an array. The delimiter used between elements of the array literal defaults to ", " if a delimiter is not specified or is undef.

Converts a Perl variable to the value of the data type passed as a second argument and returns a string representation of this value. Correctly handles nested arrays and values of composite types.

Returns the contents of the referenced array as a string in array constructor format (see Section 4.2.11). Individual values are quoted using quote_nullable. Returns the argument value, quoted using quote_nullable, if it's not a reference to an array.

Returns a true value if the content of the given string looks like a number, according to Perl, returns false otherwise. Returns undef if the argument is undef. Leading and trailing space is ignored. Inf and Infinity are regarded as numbers.

Returns a true value if the given argument may be treated as an array reference, that is, if ref of the argument is ARRAY or PostgreSQL::InServer::ARRAY. Returns false otherwise.