| PostgreSQL 8.0.1 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 37. PL/Perl - Perl Procedural Language | Fast Forward | Next |
37.2. Database Access from PL/Perl
Access to the database itself from your Perl function can be done via the function spi_exec_query described below, or via an experimental module DBD::PgSPI (also available at CPAN mirror sites). This module makes available a DBI-compliant database-handle named $pg_dbh that can be used to perform queries with normal DBI syntax.
PL/Perl itself presently provides two additional Perl commands:
spi_exec_query(query [, max-rows])spi_exec_query(command)Executes an SQL command. Here is an example of a query (SELECT command) with the optional maximum number of rows:
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:
The total number of rows returned from a SELECT query can be accessed like this:
Here is an example using a different command type:
You can then access the command status (e.g., SPI_OK_INSERT) like this:
To get the number of rows affected, do:
Here is a complete example:
elog(level, msg)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 Section 16.4 for more information.
