<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <HTML ><HEAD ><TITLE >PL/Perl - Perl Procedural Language</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 7.4.1 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Server Programming" HREF="server-programming.html"><LINK REL="PREVIOUS" TITLE="Tcl Procedure Names" HREF="pltcl-procnames.html"><LINK REL="NEXT" TITLE="Data Values in PL/Perl" HREF="plperl-data.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META NAME="creation" CONTENT="2003-12-22T03:48:47"></HEAD ><BODY CLASS="CHAPTER" ><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 7.4.1 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="pltcl-procnames.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="pltcl.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="plpython.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="CHAPTER" ><H1 ><A NAME="PLPERL" ></A >Chapter 39. PL/Perl - Perl Procedural Language</H1 ><DIV CLASS="TOC" ><DL ><DT ><B >Table of Contents</B ></DT ><DT >39.1. <A HREF="plperl.html#PLPERL-FUNCS" >PL/Perl Functions and Arguments</A ></DT ><DT >39.2. <A HREF="plperl-data.html" >Data Values in PL/Perl</A ></DT ><DT >39.3. <A HREF="plperl-database.html" >Database Access from PL/Perl</A ></DT ><DT >39.4. <A HREF="plperl-trusted.html" >Trusted and Untrusted PL/Perl</A ></DT ><DT >39.5. <A HREF="plperl-missing.html" >Missing Features</A ></DT ></DL ></DIV ><A NAME="AEN32885" ></A ><A NAME="AEN32887" ></A ><P > PL/Perl is a loadable procedural language that enables you to write <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > functions in the <A HREF="http://www.perl.com" TARGET="_top" >Perl</A > programming language. </P ><P > To install PL/Perl in a particular database, use <TT CLASS="LITERAL" >createlang plperl <VAR CLASS="REPLACEABLE" >dbname</VAR ></TT >. </P ><DIV CLASS="TIP" ><BLOCKQUOTE CLASS="TIP" ><P ><B >Tip: </B > If a language is installed into <TT CLASS="LITERAL" >template1</TT >, all subsequently created databases will have the language installed automatically. </P ></BLOCKQUOTE ></DIV ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > Users of source packages must specially enable the build of PL/Perl during the installation process. (Refer to the installation instructions for more information.) Users of binary packages might find PL/Perl in a separate subpackage. </P ></BLOCKQUOTE ></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="PLPERL-FUNCS" >39.1. PL/Perl Functions and Arguments</A ></H1 ><P > To create a function in the PL/Perl language, use the standard syntax: </P><PRE CLASS="PROGRAMLISTING" >CREATE FUNCTION <VAR CLASS="REPLACEABLE" >funcname</VAR > (<VAR CLASS="REPLACEABLE" >argument-types</VAR >) RETURNS <VAR CLASS="REPLACEABLE" >return-type</VAR > AS ' # PL/Perl function body ' LANGUAGE plperl;</PRE ><P> The body of the function is ordinary Perl code. </P ><P > Arguments and results are handled as in any other Perl subroutine: Arguments are passed in <VAR CLASS="VARNAME" >@_</VAR >, and a result value is returned with <TT CLASS="LITERAL" >return</TT > or as the last expression evaluated in the function. </P ><P > For example, a function returning the greater of two integer values could be defined as: </P><PRE CLASS="PROGRAMLISTING" >CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS ' if ($_[0] > $_[1]) { return $_[0]; } return $_[1]; ' LANGUAGE plperl;</PRE ><P> </P ><P > If an SQL null value<A NAME="AEN32913" ></A > is passed to a function, the argument value will appear as <SPAN CLASS="QUOTE" >"undefined"</SPAN > in Perl. The above function definition will not behave very nicely with null inputs (in fact, it will act as though they are zeroes). We could add <TT CLASS="LITERAL" >STRICT</TT > to the function definition to make <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > do something more reasonable: if a null value is passed, the function will not be called at all, but will just return a null result automatically. Alternatively, we could check for undefined inputs in the function body. For example, suppose that we wanted <CODE CLASS="FUNCTION" >perl_max</CODE > with one null and one non-null argument to return the non-null argument, rather than a null value: </P><PRE CLASS="PROGRAMLISTING" >CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS ' my ($a,$b) = @_; if (! defined $a) { if (! defined $b) { return undef; } return $b; } if (! defined $b) { return $a; } if ($a > $b) { return $a; } return $b; ' LANGUAGE plperl;</PRE ><P> </P ><P > As shown above, to return an SQL null value from a PL/Perl function, return an undefined value. This can be done whether the function is strict or not. </P ><P > Composite-type arguments are passed to the function as references to hashes. The keys of the hash are the attribute names of the composite type. Here is an example: </P><PRE CLASS="PROGRAMLISTING" >CREATE TABLE employee ( name text, basesalary integer, bonus integer ); CREATE FUNCTION empcomp(employee) RETURNS integer AS ' my ($emp) = @_; return $emp->{''basesalary''} + $emp->{''bonus''}; ' LANGUAGE plperl; SELECT name, empcomp(employee) FROM employee;</PRE ><P> </P ><P > There is currently no support for returning a composite-type result value. </P ><DIV CLASS="TIP" ><BLOCKQUOTE CLASS="TIP" ><P ><B >Tip: </B > Because the function body is passed as an SQL string literal to <TT CLASS="COMMAND" >CREATE FUNCTION</TT >, you have to escape single quotes and backslashes within your Perl source, typically by doubling them as shown in the above example. Another possible approach is to avoid writing single quotes by using Perl's extended quoting operators (<TT CLASS="LITERAL" >q[]</TT >, <TT CLASS="LITERAL" >qq[]</TT >, <TT CLASS="LITERAL" >qw[]</TT >). </P ></BLOCKQUOTE ></DIV ></DIV ></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="pltcl-procnames.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" >Tcl Procedure Names</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="server-programming.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Data Values in PL/Perl</TD ></TR ></TABLE ></DIV ></BODY ></HTML >