Sophie: postgresql-docs-8.0.11-0.1.20060mdk x86

postgresql-docs-8.0.11-0.1.20060mdk.x86_64.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Database Access from PL/Perl</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.0.11 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="PL/Perl - Perl Procedural Language"
HREF="plperl.html"><LINK
REL="PREVIOUS"
TITLE="PL/Perl - Perl Procedural Language"
HREF="plperl.html"><LINK
REL="NEXT"
TITLE="Data Values in PL/Perl"
HREF="plperl-data.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2007-02-02T03:57:22"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.0.11 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="plperl.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="plperl.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 37. PL/Perl - Perl Procedural Language</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="plperl.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="plperl-data.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="PLPERL-DATABASE"
>37.2. Database Access from PL/Perl</A
></H1
><P
>   Access to the database itself from your Perl function can be done
   via the function <CODE
CLASS="FUNCTION"
>spi_exec_query</CODE
> described
   below, or via an experimental module <A
HREF="http://www.cpan.org/modules/by-module/DBD/APILOS/"
TARGET="_top"
><TT
CLASS="LITERAL"
>DBD::PgSPI</TT
></A
>
   (also available at <A
HREF="http://www.cpan.org/SITES.html"
TARGET="_top"
><ACRONYM
CLASS="ACRONYM"
>CPAN</ACRONYM
> mirror
   sites</A
>).  This module makes available a
   <ACRONYM
CLASS="ACRONYM"
>DBI</ACRONYM
>-compliant database-handle named
   <TT
CLASS="VARNAME"
>$pg_dbh</TT
> that can be used to perform queries with
   normal <ACRONYM
CLASS="ACRONYM"
>DBI</ACRONYM
>
   syntax.<A
NAME="AEN34199"
></A
>
  </P
><P
>   PL/Perl itself presently provides two additional Perl commands:

   <P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>spi_exec_query</CODE
>(<TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
> [, <TT
CLASS="REPLACEABLE"
><I
>max-rows</I
></TT
>])</TT
><BR><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>spi_exec_query</CODE
>(<TT
CLASS="REPLACEABLE"
><I
>command</I
></TT
>)</TT
></DT
><DD
><P
>       Executes an SQL command.  Here is an example of a query
       (<TT
CLASS="COMMAND"
>SELECT</TT
> command) with the optional maximum
       number of rows:
</P><PRE
CLASS="PROGRAMLISTING"
>$rv = spi_exec_query('SELECT * FROM my_table', 5);</PRE
><P>
        This returns up to 5 rows from the table
        <TT
CLASS="LITERAL"
>my_table</TT
>.  If <TT
CLASS="LITERAL"
>my_table</TT
>
        has a column <TT
CLASS="LITERAL"
>my_column</TT
>, you can get that
        value from row <TT
CLASS="LITERAL"
>$i</TT
> of the result like this:
</P><PRE
CLASS="PROGRAMLISTING"
>$foo = $rv-&gt;{rows}[$i]-&gt;{my_column};</PRE
><P>
       The total number of rows returned from a <TT
CLASS="COMMAND"
>SELECT</TT
>
       query can be accessed like this:
</P><PRE
CLASS="PROGRAMLISTING"
>$nrows = $rv-&gt;{processed}</PRE
><P>
      </P
><P
>       Here is an example using a different command type:
</P><PRE
CLASS="PROGRAMLISTING"
>$query = "INSERT INTO my_table VALUES (1, 'test')";
$rv = spi_exec_query($query);</PRE
><P>
       You can then access the command status (e.g.,
       <TT
CLASS="LITERAL"
>SPI_OK_INSERT</TT
>) like this:
</P><PRE
CLASS="PROGRAMLISTING"
>$res = $rv-&gt;{status};</PRE
><P>
       To get the number of rows affected, do:
</P><PRE
CLASS="PROGRAMLISTING"
>$nrows = $rv-&gt;{processed};</PRE
><P>
      </P
><P
>       Here is a complete example:
</P><PRE
CLASS="PROGRAMLISTING"
>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-&gt;{status};
    my $nrows = $rv-&gt;{processed};
    foreach my $rn (0 .. $nrows - 1) {
        my $row = $rv-&gt;{rows}[$rn];
        $row-&gt;{i} += 200 if defined($row-&gt;{i});
        $row-&gt;{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row-&gt;{v}));
        push @$res, $row;
    }
    return $res;
$$ LANGUAGE plperl;

SELECT * FROM test_munge();</PRE
><P>
      </P
></DD
><DT
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>elog</CODE
>(<TT
CLASS="REPLACEABLE"
><I
>level</I
></TT
>, <TT
CLASS="REPLACEABLE"
><I
>msg</I
></TT
>)</TT
></DT
><DD
><P
>       Emit a log or error message. Possible levels are
       <TT
CLASS="LITERAL"
>DEBUG</TT
>, <TT
CLASS="LITERAL"
>LOG</TT
>, <TT
CLASS="LITERAL"
>INFO</TT
>,
       <TT
CLASS="LITERAL"
>NOTICE</TT
>, <TT
CLASS="LITERAL"
>WARNING</TT
>, and <TT
CLASS="LITERAL"
>ERROR</TT
>.
       <TT
CLASS="LITERAL"
>ERROR</TT
>
        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 <TT
CLASS="LITERAL"
>die</TT
> 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
        <A
HREF="runtime-config.html#GUC-LOG-MIN-MESSAGES"
>log_min_messages</A
> and
        <A
HREF="runtime-config.html#GUC-CLIENT-MIN-MESSAGES"
>client_min_messages</A
> configuration
        variables. See <A
HREF="runtime-config.html"
>Section 16.4</A
> for more
        information.
      </P
></DD
></DL
></DIV
><P>
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="plperl.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="plperl-data.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>PL/Perl - Perl Procedural Language</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="plperl.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Data Values in PL/Perl</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>