## 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:

      $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};

``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 simply report the message to the system log and/or client.