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

<chapter id="plpython">
 <title>PL/Python &mdash; Python Procedural Language</title>

 <indexterm zone="plpython"><primary>PL/Python</primary></indexterm>
 <indexterm zone="plpython"><primary>Python</primary></indexterm>

 <para>
  The <application>PL/Python</application> procedural language allows
  <productname>PostgreSQL</productname> functions and procedures to be written in the
  <ulink url="https://www.python.org">Python language</ulink>.
 </para>

 <para>
  To install PL/Python in a particular database, use
  <literal>CREATE EXTENSION plpython3u</literal>.
 </para>

  <tip>
   <para>
    If a language is installed into <literal>template1</literal>, all subsequently
    created databases will have the language installed automatically.
   </para>
  </tip>

 <para>
  PL/Python is only available as an <quote>untrusted</quote> language, meaning
  it does not offer any way of restricting what users can do in it and
  is therefore named <literal>plpython3u</literal>.  A trusted
  variant <literal>plpython</literal> might become available in the future
  if a secure execution mechanism is developed in Python.  The
  writer of a function in untrusted PL/Python must take care that the
  function cannot be used to do anything unwanted, since it will be
  able to do anything that could be done by a user logged in as the
  database administrator.  Only superusers can create functions in
  untrusted languages such as <literal>plpython3u</literal>.
 </para>

 <note>
  <para>
   Users of source packages must specially enable the build of
   PL/Python during the installation process.  (Refer to the
   installation instructions for more information.)  Users of binary
   packages might find PL/Python in a separate subpackage.
  </para>
 </note>

 <sect1 id="plpython-funcs">
  <title>PL/Python Functions</title>

  <para>
   Functions in PL/Python are declared via the
   standard <xref linkend="sql-createfunction"/> syntax:

<programlisting>
CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-list</replaceable>)
  RETURNS <replaceable>return-type</replaceable>
AS $$
  # PL/Python function body
$$ LANGUAGE plpython3u;
</programlisting>
  </para>

  <para>
   The body of a function is simply a Python script. When the function
   is called, its arguments are passed as elements of the list
   <varname>args</varname>; named arguments are also passed as
   ordinary variables to the Python script.  Use of named arguments is
   usually more readable.  The result is returned from the Python code
   in the usual way, with <literal>return</literal> or
   <literal>yield</literal> (in case of a result-set statement).  If
   you do not provide a return value, Python returns the default
   <symbol>None</symbol>. <application>PL/Python</application> translates
   Python's <symbol>None</symbol> into the SQL null value.  In a procedure,
   the result from the Python code must be <symbol>None</symbol> (typically
   achieved by ending the procedure without a <literal>return</literal>
   statement or by using a <literal>return</literal> statement without
   argument); otherwise, an error will be raised.
  </para>

  <para>
   For example, a function to return the greater of two integers can be
   defined as:

<programlisting>
CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$
  if a &gt; b:
    return a
  return b
$$ LANGUAGE plpython3u;
</programlisting>

   The Python code that is given as the body of the function definition
   is transformed into a Python function. For example, the above results in:

<programlisting>
def __plpython_procedure_pymax_23456():
  if a &gt; b:
    return a
  return b
</programlisting>

   assuming that 23456 is the OID assigned to the function by
   <productname>PostgreSQL</productname>.
  </para>

  <para>
   The arguments are set as global variables.  Because of the scoping
   rules of Python, this has the subtle consequence that an argument
   variable