<!-- doc/src/sgml/plpgsql.sgml -->

<chapter id="plpgsql">
  <title><application>PL/pgSQL</application> &mdash; <acronym>SQL</acronym> Procedural Language</title>

 <indexterm zone="plpgsql">
  <primary>PL/pgSQL</primary>
 </indexterm>

 <sect1 id="plpgsql-overview">
  <title>Overview</title>

 <para>
  <application>PL/pgSQL</application> is a loadable procedural
  language for the <productname>PostgreSQL</productname> database
  system.  The design goals of <application>PL/pgSQL</application> were to create
  a loadable procedural language that

    <itemizedlist>
     <listitem>
      <para>
       can be used to create functions, procedures, and triggers,
      </para>
     </listitem>
     <listitem>
      <para>
       adds control structures to the <acronym>SQL</acronym> language,
      </para>
     </listitem>
     <listitem>
      <para>
       can perform complex computations,
      </para>
     </listitem>
     <listitem>
      <para>
       inherits all user-defined types, functions, procedures, and operators,
      </para>
     </listitem>
     <listitem>
      <para>
       can be defined to be trusted by the server,
      </para>
     </listitem>
     <listitem>
      <para>
       is easy to use.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    Functions created with <application>PL/pgSQL</application> can be
    used anywhere that built-in functions could be used.
    For example, it is possible to
    create complex conditional computation functions and later use
    them to define operators or use them in index expressions.
   </para>

   <para>
    In <productname>PostgreSQL</productname> 9.0 and later,
    <application>PL/pgSQL</application> is installed by default.
    However it is still a loadable module, so especially security-conscious
    administrators could choose to remove it.
   </para>

  <sect2 id="plpgsql-advantages">
   <title>Advantages of Using <application>PL/pgSQL</application></title>

    <para>
     <acronym>SQL</acronym> is the language <productname>PostgreSQL</productname>
     and most other relational databases use as query language. It's
     portable and easy to learn. But every <acronym>SQL</acronym>
     statement must be executed individually by the database server.
    </para>

    <para>
     That means that your client application must send each query to
     the database server, wait for it to be processed, receive and
     process the results, do some computation, then send further
     queries to the server.  All this incurs interprocess
     communication and will also incur network overhead if your client
     is on a different machine than the database server.
    </para>

    <para>
     With <application>PL/pgSQL</application> you can group a block of
     computation and a series of queries <emphasis>inside</emphasis>
     the database server, thus having the power of a procedural
     language and the ease of use of SQL, but with considerable
     savings of client/server communication overhead.
    </para>
    <itemizedlist>

     <listitem><para> Extra round trips between
     client and server are eliminated </para></listitem>

     <listitem><para> Intermediate results that the client does not
     need do not have to be marshaled or transferred between server
     and client </para></listitem>

     <listitem><para> Multiple rounds of query
     parsing can be avoided </para></listitem>

    </itemizedlist>
    <para> This can result in a considerable performance increase as
    compared to an application that does not use stored functions.
    </para>

    <para>
     Also, with <application>PL/pgSQL</application> you can use all
     the data types, operators and functions of SQL.
    </para>
  </sect2>

  <sect2 id="plpgsql-args-results">
   <title>Supported Argument and Result Data Types</title>

    <para>
     Functions written in <application>PL/pgSQL</application> can accept
     as arguments any scalar or array data type supported