codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>46.1. PL/Python Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpython.html" title="Chapter 46. PL/Python — Python Procedural Language" /><link rel="next" href="plpython-data.html" title="46.2. Data Values" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">46.1. PL/Python Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpython.html" title="Chapter 46. PL/Python — Python Procedural Language">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpython.html" title="Chapter 46. PL/Python — Python Procedural Language">Up</a></td><th width="60%" align="center">Chapter 46. PL/Python — Python Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpython-data.html" title="46.2. Data Values">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPYTHON-FUNCS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">46.1. PL/Python Functions <a href="#PLPYTHON-FUNCS" class="id_link">#</a></h2></div></div></div><p>3   Functions in PL/Python are declared via the4   standard <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a> syntax:5 6</p><pre class="programlisting">7CREATE FUNCTION <em class="replaceable"><code>funcname</code></em> (<em class="replaceable"><code>argument-list</code></em>)8  RETURNS <em class="replaceable"><code>return-type</code></em>9AS $$10  # PL/Python function body11$$ LANGUAGE plpython3u;12</pre><p>13  </p><p>14   The body of a function is simply a Python script. When the function15   is called, its arguments are passed as elements of the list16   <code class="varname">args</code>; named arguments are also passed as17   ordinary variables to the Python script.  Use of named arguments is18   usually more readable.  The result is returned from the Python code19   in the usual way, with <code class="literal">return</code> or20   <code class="literal">yield</code> (in case of a result-set statement).  If21   you do not provide a return value, Python returns the default22   <code class="symbol">None</code>. <span class="application">PL/Python</span> translates23   Python's <code class="symbol">None</code> into the SQL null value.  In a procedure,24   the result from the Python code must be <code class="symbol">None</code> (typically25   achieved by ending the procedure without a <code class="literal">return</code>26   statement or by using a <code class="literal">return</code> statement without27   argument); otherwise, an error will be raised.28  </p><p>29   For example, a function to return the greater of two integers can be30   defined as:31 32</p><pre class="programlisting">33CREATE FUNCTION pymax (a integer, b integer)34  RETURNS integer35AS $$36  if a &gt; b:37    return a38  return b39$$ LANGUAGE plpython3u;40</pre><p>41 42   The Python code that is given as the body of the function definition43   is transformed into a Python function. For example, the above results in:44 45</p><pre class="programlisting">46def __plpython_procedure_pymax_23456():47  if a &gt; b:48    return a49  return b50</pre><p>51 52   assuming that 23456 is the OID assigned to the function by53   <span class="productname">PostgreSQL</span>.54  </p><p>55   The arguments are set as global variables.  Because of the scoping56   rules of Python, this has the subtle consequence that an argument57   variable cannot be reassigned inside the function to the value of58   an expression that involves the variable name itself, unless the59   variable is redeclared as global in the block.  For example, the60   following won't work:61</p><pre class="programlisting">62CREATE FUNCTION pystrip(x text)63  RETURNS text64AS $$65  x = x.strip()  # error66  return x67$$ LANGUAGE plpython3u;68</pre><p>69   because assigning to <code class="varname">x</code>70   makes <code class="varname">x</code> a local variable for the entire block,71   and so the <code class="varname">x</code> on the right-hand side of the72   assignment refers to a not-yet-assigned local73   variable <code class="varname">x</code>, not the PL/Python function74   parameter.  Using the <code class="literal">global</code> statement, this can75   be made to work:76</p><pre class="programlisting">77CREATE FUNCTION pystrip(x text)78  RETURNS text79AS $$80  global x81  x = x.strip()  # ok now82  return x83$$ LANGUAGE plpython3u;84</pre><p>85   But it is advisable not to rely on this implementation detail of86   PL/Python.  It is better to treat the function parameters as87   read-only.88  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpython.html" title="Chapter 46. PL/Python — Python Procedural Language">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpython.html" title="Chapter 46. PL/Python — Python Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpython-data.html" title="46.2. Data Values">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 46. PL/Python — Python Procedural Language </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 46.2. Data Values</td></tr></table></div></body></html>