## 38.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 provides three additional Perl commands:

``spi_exec_query`(`query` [, `max-rows`])`
``spi_exec_query`(`command`)`
``spi_query`(`command`)`
``spi_fetchrow`(`command`)`

  `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 may 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, "Can't 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);

``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 Chapter 17, Server Configuration for more information.