File: index.html

package info (click to toggle)
postgresql-pllua 1%3A1.0-5
links: PTS, VCS
area: main
in suites: jessie, jessie-kfreebsd
size: 364 kB
ctags: 324
sloc: ansic: 1,919; sql: 292; makefile: 29; sh: 8
file content (907 lines) | stat: -rw-r--r-- 32,312 bytes
parent folder | download | duplicates (2)
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1 //EN"
		"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">

<!-- PL/Lua home -->
<!-- $Id: index.html,v 1.4 2008-03-30 03:45:23 carvalho Exp $ -->

<head>
	<title>PL/Lua documentation</title>
	<meta name="description" content="PL/Lua" />
	<meta name="keywords" content="postgresql,lua,procedural language" />
	<meta name="author" content="Luis Carvalho" />
	<meta http-equiv="content-language" content="en" />
	<meta http-equiv="content-type" content="application/xhtml+xml; charset=utf-8" />
	<meta http-equiv="content-style-type" content="text/css" />
  <link rel="stylesheet" type="text/css"
    href="style.css" media="screen" />
</head>

<body>
<div id="page">
	
<div id="header">
	<h1>PL/Lua</h1>
  Procedural Lua for PostgreSQL
</div>

<div id="horizbar">
	<a href="#introduction" accesskey="1">Introduction</a>&nbsp; :: &nbsp;
	<a href="#languages">Languages</a>&nbsp; :: &nbsp; 
	<a href="#types">Types</a>&nbsp; :: &nbsp; 
	<a href="#functions">Functions</a>&nbsp; :: &nbsp; 
	<a href="#database">Database Access</a>&nbsp; :: &nbsp; 
	<a href="#triggers">Triggers</a>&nbsp; :: &nbsp; 
	<a href="#installation">Installation</a>
</div>

<div class="content">

<h2 id="introduction">Introduction</h2>
<small>What PL/Lua is</small>
<p><strong>PL/Lua</strong> is an implementation of
<a href="http://www.lua.org">Lua</a> as a loadable procedural language for
<a href="http://www.postgresql.org">PostgreSQL</a>: with PL/Lua you can use
PostgreSQL functions and triggers written in the Lua programming language.</p>

<p>Procedural languages offer many extra capabilities to PostgreSQL, similar
to C language extensions: control structures, more complex computations than
allowed by SQL, access to user-defined types and database functions and
operators, and restriction to trusted execution.</p>

<p>PL/Lua brings the power and simplicity of Lua to PostgreSQL, including:
small memory footprint, simple syntax, lexical scoping, functions as
first-class values, and coroutines for non-preemptive threading. As a simple
example, consider the following hello function:</p>

<pre>
# CREATE FUNCTION hello(name text) RETURNS text AS $$
  return string.format("Hello, %s!", name) 
$$ LANGUAGE pllua;
CREATE FUNCTION
# SELECT hello('PostgreSQL');
       hello        
--------------------
 Hello, PostgreSQL!
(1 row)
</pre>

<p>The next sections present more examples where other features are used. In
the <a href="#languages">Languages</a> section the two flavors of PL/Lua are
described; the <a href="#functions">Functions</a> section details how
functions are registered in PL/Lua and how arguments are treated;
<a href="#database">Database access</a> presents the
<acronym title="Server Programming Interface">SPI</acronym> interface
to PL/Lua; and <a href="triggers">Triggers</a> shows how triggers can be
declared.</p>

<p>PL/Lua is licensed under the
<a href="http://www.lua.org/license.html">same license as Lua</a> -- the
<a href="http://www.opensource.org/licenses/mit-license.php">MIT license</a>
-- and so can be freely used for academic and commercial purposes. Please
refer to the <a href="#installation">Installation</a> section for more
details.</p>


<h2 id="languages">Languages</h2>
<small>Trusted and Untrusted PL/Lua</small>
<p>PL/Lua is available as either a <em>trusted</em> (<code>pllua</code>) or an
<em>untrusted</em> (<code>plluau</code>) language. In <code>plluau</code> the
user has access to a full-blown Lua environment, similar to the regular
interpreter: all libraries are loaded, the user can access the global table
freely, and modules can be loaded. Only database superusers are allowed to
create functions using this untrusted version of PL/Lua.</p>

<p>Unprivileged users can only create functions using the trusted version of
PL/Lua, <code>pllua</code>. The environment in <code>pllua</code> is more
restricted: only <code>table</code>, <code>string</code>, and
<code>math</code> libraries are fully loaded, the <code>os</code> library is
restricted, the <code>package</code> library is not available, that is, there
is no module system (including <code>require</code>), and the global table is
restricted for writing. The following table summarizes the differences:</p>

<table><tbody>
    <tr>
      <td><strong>Table</strong></td>
      <td><strong><code>plluau</code></strong></td>
      <td><strong><code>pllua</code></strong></td>
    </tr>
    <tr>
      <td><code>table, string, math</code></td>
      <td>All functions</td>
      <td>All functions</td>
    </tr>
    <tr>
      <td><code>os</code></td>
      <td>All functions</td>
      <td><code>date, clock, time, difftime</code></td>
    </tr>
    <tr>
      <td><code>package</code> (module system)</td>
      <td>All functions</td>
      <td>None</td>
    </tr>
    <tr>
      <td><code>_G</code> (global environment)</td>
      <td>Free access</td>
      <td>Writing is restricted</td>
    </tr>
</tbody></table>

<p>Even though the module system is absent in <code>pllua</code>, PL/Lua
allows for modules to be automatically loaded after creating the environment:
all entries in table <code><em>pllua.init</em></code> are
<code>require</code>'d at startup.</p>

<p>To facilitate the use of PL/Lua and following the tradition of other PLs,
the global table is aliased to <code>shared</code>. Moreover, write access to
the global table in <code>pllua</code> is restricted to avoid pollution;
global variables should then be created with
<a href="#setshared"><code>setshared</code></a>.</p>

<p>Finally, errors in PL/Lua are propagated to the calling query and the
transaction is aborted if the error is not caught. Messages can be emitted by
<a href="#log"><code>log</code></a>, <code>info</code>, <code>notice</code>,
and <code>warning</code> at log levels LOG, INFO, NOTICE, and WARNING
respectively. In particular, <code>print</code> emits log messages of level
INFO.</p>

<h4 id="log"><code>log(msg)</code></h4>
<p>Emits message <code>msg</code> at log level LOG. Similar functions
<code>info</code>, <code>notice</code>, and <code>warning</code> have the same
signature but emit <code>msg</code> at their respective log levels.</p>

<h4 id="setshared"><code>setshared(varname [, value])</code></h4>
<p>Sets global <code>varname</code> to <code>value</code>, which defaults to
<code>true</code>. It is semantically equivalent to
<code>shared[varname] = value</code>.</p>


<h2 id="types">Types</h2>
<small>Data values in PL/Lua</small>
<p>PL/Lua makes no conversion of function arguments to string/text form
between Lua and PostgreSQL. Basic data types are natively supported, that is,
converted directly, by value, to a Lua equivalent. The following table shows
type equivalences:</p>

<table><tbody>
    <tr>
      <td><strong>PostgreSQL type</strong></td>
      <td><strong>Lua type</strong></td>
    </tr>
    <tr>
      <td><code>bool</code></td>
      <td><code>boolean</code></td>
    </tr>
    <tr>
      <td><code>float4, float8, int2, int4</code></td>
      <td><code>number</code></td>
    </tr>
    <tr>
      <td><code>text, char, varchar</code></td>
      <td><code>string</code></td>
    </tr>
    <tr>
      <td>Base, domain</td>
      <td><code>userdata</code></td>
    </tr>
    <tr>
      <td>Arrays, composite</td>
      <td><code>table</code></td>
    </tr>
</tbody></table>

<p>Base and domain types other than the ones in the first three rows in the
table are converted to a <em>raw datum</em> userdata in Lua with a suitable
<code>__tostring</code> metamethod based on the type's output function.
Conversely, <code>fromstring</code> takes a type name and a string and returns
a raw datum from the provided type's input function. Arrays are converted to
Lua tables with integer indices, while composite types become tables with keys
corresponding to attribute names.</p>

<h4 id="fromstring"><code>fromstring(tname, s)</code></h4>
<p>Returns a raw datum userdata for <code>s</code> of type <code>tname</code>
using <code>tname</code>'s input function to convert <code>s</code>.</p>

<h2 id="functions">Functions</h2>
<small>Functions in PL/Lua</small>
<p>PL/Lua functions are created according to the following prototype:</p>

<pre>
CREATE FUNCTION <strong>func</strong>(<em>args</em>) RETURNS <em>rettype</em> AS $$
  <em>-- Lua function body</em>
$$ LANGUAGE [pllua | plluau];
</pre>

<p>where <code>args</code> are usually <em>named</em> arguments. The value
returned by <code>func</code> is converted to a datum of type
<code>rettype</code> unless <code>rettype</code> is <code>void</code>.</p>

<p>The function body is composed as below to become a typical Lua chunk:</p>

<pre>
local <em>_U</em>, <strong>func</strong> -- _U is upvalue
<strong>func</strong> = function(<em>argnames</em>)
  <em>-- Lua function body</em>
end
return <strong>func</strong>
</pre>

<p>Note the <em>upvalue</em> <code>_U</code> that can be later declared in the
function body (see examples below.)</p>

<p>If any of the arguments provided to <code>create function</code> is not
named then <code>argnames</code> gets substituted to <code>...</code>, that
is, <code>func</code> becomes <em>vararg</em>.</p>

<p>The resulting chunk is then compiled and stored in the registry of the
PL/Lua state as a function with the same name. It is important to have the
above structure in mind when writing PL/Lua functions. As an example, consider
the following function:</p>

<pre>
CREATE FUNCTION max(a integer, b integer) RETURNS integer AS $$
  if a == nil then return b end <em>-- first arg is NULL?</em>
  if b == nil then return a end <em>-- second arg is NULL?</em>
  return a &gt; b and a or b <em>-- return max(a, b)</em>
$$ LANGUAGE pllua;
</pre>

<p>Note that <code>max</code> is not strict and returns <code>NULL</code> when
both <code>a</code> and <code>b</code> are <code>NULL</code>.</p>

<p>Since functions in PL/Lua are stored with their declared names, they can be
recursive:</p>

<pre>
CREATE FUNCTION fib(n int) RETURNS int as $$
  if n &lt; 3 then
    return n
  else
    return fib(n - 1) + fib(n - 2)
  end
$$ LANGUAGE pllua;
</pre>

<p>Moreover, as can be seen in the composition of <code>func</code> above,
PL/Lua functions are actually <em>closures</em> on the upvalue
<code><em>_U</em></code>. The user can think of <code>_U</code> as
local cache to <code>func</code> that could &mdash; and should! &mdash; be
used instead of the global state to store values. Quick example:</p> 

<pre>
CREATE FUNCTION counter() RETURNS int AS $$
  while true do
    _U = _U + 1
    coroutine.yield(_U)
  end
end
do
  _U = 0 <em>-- counter</em>
  counter = coroutine.wrap(counter)
$$ LANGUAGE pllua;
</pre>

<p>Function <code>counter</code> is similar to an iterator, returning
consecutive integers every time it is called, starting at one. Note that we
need to add <code>end</code> to finish the function definition body and
<code>do</code> to start a new block since the process of function composition
always appends an <code>end</code>. It is important to observe that what
actually gets defined as <code>counter</code> is a wrapper around a
coroutine.</p>

<p>From <a href="#types">Types</a> we know that composite types can be
accessed as tables with keys corresponding to attribute names:</p>

<pre>
CREATE TYPE greeting AS (how text, who text);

CREATE FUNCTION makegreeting (g greeting, f text) RETURNS text AS $$
  return string.format(f, g.how, g.who)
$$ LANGUAGE pllua;
</pre>

<p>Set-returning functions (SRFs) are implemented in PL/Lua using coroutines.
When a SRF <code>func</code> is first called a new Lua thread is created
and <code>func</code> is pushed along with its arguments onto the new thread's
stack. A new result is then returned whenever <code>func</code> yields and
<code>func</code> is done when the coroutine suspends or finishes. Using our
composite type from above, we can define</p>

<pre>
CREATE FUNCTION greetingset (how text, who text[])
    RETURNS SETOF greeting AS $$
  for _, name in ipairs(who) do
    coroutine.yield{how=how, who=name}
  end
$$ LANGUAGE pllua;
</pre>

<p>with this usage example:</p>

<pre>
# SELECT makegreeting(greetingset, '%s, %s!') FROM
  (SELECT greetingset('Hello', ARRAY['foo', 'bar', 'psql'])) AS q;
 makegreeting 
--------------
 Hello, foo!
 Hello, bar!
 Hello, psql!
(3 rows)
</pre>

<p>Now, to further illustrate the use of arrays in PL/Lua, we adapt an
<a href="http://www.lua.org/pil/9.3.html">example</a>
from <a href="http://www.lua.org/pil"><em>Programming in Lua</em></a>:</p>

<pre>
CREATE FUNCTION perm (a text[]) RETURNS SETOF text[] AS $$
  _U(a, #a)
end
do
  _U = function (a, n) <em>-- permgen in PiL</em>
    if n == 0 then
      coroutine.yield(a) <em>-- return next SRF row</em>
    else
      for i = 1, n do
        a[n], a[i] = a[i], a[n] <em>-- i-th element as last one</em>
        _U(a, n - 1) <em>-- recurse on head</em>
        a[n], a[i] = a[i], a[n] <em>-- restore i-th element</em>
      end
    end
  end
$$ LANGUAGE pllua;
</pre>

<p>As stated in <a href="#languages">Languages</a>, it is possible to access
the global table of PL/Lua's state. However, as noted before, since PL/Lua
functions are closures, creating global variables should be restricted to
cases where data is to be shared between different functions. The following
simple example defines a getter-setter pair to access a shared variable
<code>counter</code>:</p>

<pre>
CREATE FUNCTION getcounter() RETURNS integer AS $$
  if shared.counter == nil then <em>-- not cached?</em>
    setshared("counter", 0)
  end
  return counter <em>-- _G.counter == shared.counter</em>
$$ LANGUAGE pllua;

CREATE FUNCTION setcounter(c integer) RETURNS void AS $$
  if shared.counter == nil then <em>-- not cached?</em>
    setshared("counter", c)
  else
    counter = c <em>-- _G.counter == shared.counter</em>
  end
$$ LANGUAGE pllua;
</pre>


<h3>Examples</h3>
<p>Let's revisit our (rather inefficient) recursive Fibonacci function
<code>fib</code>. A better version uses <em>tail recursion</em>:</p>

<pre>
CREATE FUNCTION fibt(n integer) RETURNS integer AS $$
  return _U(n, 0, 1)
end
_U = function(n, a, b) -- tail recursive
  if n &lt; 1 then
    return b
  else
    return _U(n - 1, b, a + b)
  end
$$ LANGUAGE pllua;
</pre>

<p>We can also use the upvalue <code>_U</code> as a cache to store previous
elements in the sequence and obtain a <em>memoized</em> version:</p>

<pre>
CREATE FUNCTION fibm(n integer) RETURNS integer AS $$
  if n &lt; 3 then
    return n
  else
    local v = _U[n]
    if not v then
      v = fibm(n - 1) + fibm(n - 2)
      _U[n] = v
    end
    return v
  end
end
do _U = {} -- memoize
$$ LANGUAGE pllua;
</pre>

<p>Finally, we can implement an iterator similar to <code>counter</code>:</p>

<pre>
CREATE FUNCTION fibi() RETURNS integer AS $$
  while true do
    _U.curr, _U.next = _U.next, _U.curr + _U.next
    coroutine.yield(_U.curr)
  end
end
do
  _U = {curr = 0, next = 1}
  fibi = coroutine.wrap(fibi)
$$ LANGUAGE pllua;
</pre>

<h3>Anonymous blocks</h3>
<p>Anonymous code blocks are also supported in PL/Lua. The following
prototype</p>

<pre>
DO $$
  <em>-- Lua chunk</em>
$$ LANGUAGE [pllua | plluau];
</pre>

<p>compiles and executes the Lua chunk. Here are some examples:</p>

<pre>
DO $$ print(_VERSION) $$ LANGUAGE pllua;

DO $$
  local ffi = assert(require("ffi")); -- LuaJIT
  ffi.cdef[[ double lgamma (double); ]]
  mathx = ffi.load("m")
$$ LANGUAGE plluau; -- note: untrusted due to "require"
CREATE FUNCTION lfactorial (n int) RETURNS double precision AS $$
  return mathx.lgamma(n + 1)
$$ LANGUAGE plluau;
</pre>


<h2 id="database">Database Access</h2>
<small>Server interface in PL/Lua</small>

<p>The server interface in PL/Lua comprises the methods in table
<code>server</code> and userdata <code>plan</code>, <code>cursor</code>,
<code>tuple</code>, and <code>tupletable</code>. The entry point to the
<acronym title="Server Programming Interface">SPI</acronym> is the table
<code>server</code>: <code>server.execute</code> executes a SQL command,
<code>server.find</code> retrieves a <a href="#cursors">cursor</a>, and
<code>server.prepare</code> prepares, but does not execute, a SQL command into
a <a href="#plans">plan</a>.</p>

<p>A <em>tuple</em> represents a composite type, record, or row. It can be
accessed similarly to a Lua table, by simply indexing fields in the composite
type as keys. A tuple can be used as a return value, just like a table, for
functions that return a complex type. Tuple sets, like the ones returned by
<code>server.execute</code>, <code>plan:execute</code>, and
<code>cursor:fetch</code>, are stored in a <em>tupletable</em>. A tupletable
is similar to an integer-keyed Lua table.</p>

<h4 id="server_execute">
  <code>server.execute(cmd, readonly [, count])</code>
</h4>
<p>Executes the SQL statement <code>cmd</code> for <code>count</code> rows. If
<code>readonly</code> is <code>true</code>, the command is assumed to be
read-only and execution overhead is reduced. If <code>count</code> is zero
then the command is executed for all rows that it applies to; otherwise at
most <code>count</code> rows are returned. <code>count</code> defaults to
zero. <code>server.execute</code> returns a <em>tupletable</em>.</p>

<h4 id="server_rows"><code>server.rows(cmd)</code></h4>
<p>Returns a function so that the construction</p>

<pre>
for row in server.rows(cmd) do
  <em>-- body</em>
end
</pre>

<p>iterates over the <em>tuples</em> in the <em>read-only</em> SQL statement
<code>cmd</code>.</p>

<h4 id="server_prepare"><code>server.prepare(cmd, argtypes)</code></h4>
<p>Prepares and returns a plan from SQL statement <code>cmd</code>. If
<code>cmd</code> specifies input parameters, their types should be specified
in table <code>argtypes</code>. The plan can be executed with
<a href="#plan_execute"><code>plan:execute</code></a>. The returned plan
should not be used outside the current invocation of
<code>server.prepare</code> since it is freed by <code>SPI_finish</code>. Use
<a href="#plan_save"><code>plan:save</code></a> if you wish to store the plan
for latter application.</p>

<h4 id="server_find"><code>server.find(name)</code></h4>
<p>Finds an existing cursor with name <code>name</code> and returns a cursor
userdatum or <code>nil</code> if the cursor cannot be found.</p>

<h3 id="plans">Plans</h3>
<p><em>Plans</em> are used when a command is to be executed repeatedly,
possibly with different arguments. In this case, we can prepare a plan with
<code>server.prepare</code> and execute it later with
<code>plan:execute</code> (or using a cursor). It is also possible to save a
plan with <code>plan:save</code> if we want to keep the plan for longer than
the current transaction.</p>

<h4 id="plan_execute">
  <code>plan:execute(args, readonly [, count])</code>
</h4>
<p>Executes a previously prepared plan with parameters in table
<code>args</code>. <code>readonly</code> and <code>count</code> have the same
meaning as in <a href="#server_execute">server.execute</a>.</p>

<h4 id="plan_getcursor">
  <code>plan:getcursor(args, readonly [, name])</code>
</h4>
<p>Sets up a cursor with name <code>name</code> from a prepared plan. If
<code>name</code> is not a string a random name is selected by the system.
<code>readonly</code> has the same meaning as in
<a href="#server_execute">server.execute</a>.</p>

<h4 id="plan_rows"><code>plan:rows(args)</code></h4>
<p>Returns a function so that the construction</p>

<pre>
for row in plan:rows(args) do
  <em>-- body</em>
end
</pre>

<p>iterates over the <em>tuples</em> in the execution of a previously prepared
<em>read-only</em> plan with parameters in table <code>args</code>. It is
semantically equivalent to:</p>

<pre>
function plan:rows (cmd)
  local c = self:getcursor(nil, true) -- read-only
  return function()
    local r = c:fetch(1)
    if r == nil then
      c:close()
      return nil
    else
      return r[1]
    end
  end
end
</pre>

<h4 id="plan_issaved"><code>plan:issaved()</code></h4>
<p>Returns <code>true</code> if plan is saved and <code>false</code>
otherwise.</p>

<h4 id="plan_save"><code>plan:save()</code></h4>
<p>Saves a prepared plan for subsequent invocations in the current
session.</p>

<h3 id="cursors">Cursors</h3>
<p><em>Cursors</em> execute previously prepared plans. Cursors provide a more
powerful abstraction than simply executing a plan, since we can fetch results
and move in a query both forward and backward. Moreover, we can limit the
number of rows to be retrieved, and so avoid memory overruns for large queries
in contrast to direct plan execution. Another advantage is that cursors can
outlive the current procedure, living to the end of the current
transaction.</p>

<h4 id="cursor_fetch"><code>cursor:fetch([count])</code></h4>
<p>Fetches at most <code>count</code> rows from a cursor. If
<code>count</code> is <code>nil</code> or zero then all rows are fetched. If
<code>count</code> is negative the fetching runs backward.</p>

<h4 id="cursor_move"><code>cursor:move([count])</code></h4>
<p>Skips <code>count</code> rows in a cursor, where <code>count</code>
defaults to zero. If <code>count</code> is negative the moving runs
backward.</p>

<h4 id="cursor_close"><code>cursor:close()</code></h4>
<p>Closes a cursor.</p>


<h3>Examples</h3>
<p>Let's start with a simple example using cursors:</p>

<pre>
CREATE TABLE sometable ( sid int, sname text, sdata text);

CREATE FUNCTION get_rows (i_name text) RETURNS SETOF sometable AS $$
  if _U == nil then -- plan not cached?
    local cmd = "SELECT sid, sname, sdata FROM sometable WHERE sname=$1"
    _U = server.prepare(cmd, {"text"}):save()
  end
  local c = _U:getcursor({i_name}, true) -- read-only
  while true do
    local r = c:fetch(1)
    if r == nil then break end
    r = r[1]
    coroutine.yield{sid=r.sid, sname=r.sname, sdata=r.sdata}
  end
  c:close()
$$ LANGUAGE pllua;
</pre>

<p>This SRF works as a pipeline: it uses <code>_U</code> to store a saved
plan, while local variable <code>c</code> is a cursor that we use to fetch, at
each loop iteration, a row from <code>_U</code> and then yield a new row.
Note that local <code>r</code> is a tupletable and we need to access
<code>r[1]</code>.</p>

<p>A more concise version uses <code>plan:rows()</code>:</p>

<pre>
CREATE FUNCTION get_rows (i_name text) RETURNS SETOF sometable AS $$
  if _U == nil then -- plan not cached?
    local cmd = "SELECT sid, sname, sdata FROM sometable WHERE sname=$1"
    _U = server.prepare(cmd, {"text"}):save()
  end
  for r in _U:rows{i_name} do
    coroutine.yield(r) -- yield tuple
  end
$$ LANGUAGE pllua;
</pre>

<p>Now, for a more elaborate example, let's store a binary tree:</p>

<pre>
CREATE TABLE tree (id int PRIMARY KEY, lchild int, rchild int);
</pre>

<p>which we can fill using:</p>

<pre>
CREATE FUNCTION filltree (t text, n int) RETURNS void AS $$
  local p = server.prepare("insert into " .. t .. " values($1, $2, $3)",
    {"int4", "int4", "int4"})
  for i = 1, n do
    local lchild, rchild = 2 * i, 2 * i + 1 <em>-- siblings</em>
    p:execute{i, lchild, rchild} <em>-- insert values</em>
  end
$$ LANGUAGE pllua;
</pre>

<p>Local variable <code>p</code> stores a prepared plan for insertion with
three parameters as values, while the actual insertion is executed in the
loop.</p>

<p>We can perform a preorder traversal of the tree with:</p>

<pre>
CREATE FUNCTION preorder (t text, s int) RETURNS SETOF int AS $$
  coroutine.yield(s)
  local q = server.execute("select * from " .. t .. " where id=" .. s,
      true, 1) <em>-- read-only, only 1 result</em>
  if q ~= nil then
    local lchild, rchild = q[1].lchild, q[1].rchild <em>-- store before next query</em>
    if lchild ~= nil then preorder(t, lchild) end
    if rchild ~= nil then preorder(t, rchild) end
  end
$$ LANGUAGE pllua;
</pre>

<p>The traversal is recursive and we simply execute a query in every call and
store its result in tupletable <code>q</code>. It is important to store the
fields in <code>q[1]</code> in locals before next query, since <code>q</code>
gets updated in the next query.</p>

<p>In <code>preorder</code> we executed a query many times. For our postorder
traversal below we prepare a plan, save it, and cache in a
<code>_U</code> table. Instead of executing the plan, we get a cursor
from it and fetch only one row, as before.</p>

<pre>
CREATE FUNCTION postorder (t text, s int) RETURNS SETOF int AS $$
  local p = _U[t]
  if p == nil then <em>-- plan not cached?</em>
    p = server.prepare("select * from " .. t .. " where id=$1", {"int4"})
    _U[t] = p:save()
  end
  local c = p:getcursor({s}, true) <em>-- read-only</em>
  local q = c:fetch(1) <em>-- one row</em>
  if q ~= nil then
    local lchild, rchild = q[1].lchild, q[1].rchild <em>-- store before next query</em>
    c:close()
    if lchild ~= nil then postorder(t, lchild) end
    if rchild ~= nil then postorder(t, rchild) end
  end
  coroutine.yield(s)
end
do _U = {} <em>-- plan cache</em>
$$ LANGUAGE pllua;
</pre>


<h2 id="triggers">Triggers</h2>
<small>Triggers in PL/Lua</small>
<p>Triggers can be defined in PL/Lua as usual by just creating a function
returning <code>trigger</code>. When a function returns a trigger, PL/Lua
creates a (global) table <em><code>trigger</code></em> containing all
necessary information. The <code>trigger</code> table is described below.</p>

<table><tbody>
    <tr>
      <td><strong>Key</strong></td>
      <td><strong>Value</strong></td>
    </tr>
    <tr>
      <td><code>name</code></td>
      <td>trigger name</td>
    </tr>
    <tr>
      <td><code>when</code></td>
      <td>
        <code>"before"</code> if trigger fired before or
        <code>"after"</code> if trigger fired after
      </td>
    </tr>
    <tr>
      <td><code>level</code></td>
      <td>
        <code>"row"</code> if row-level trigger or
        <code>"statement"</code> if statement-level trigger
      </td>
    </tr>
    <tr>
      <td><code>operation</code></td>
      <td>
        <code>"insert"</code>, <code>"update"</code>, <code>"delete"</code>,
        or <code>"truncate"</code> depending on<br /> trigger operation
      </td>
    </tr>
    <tr>
      <td><code>relation</code></td>
      <td>
        Lua table describing the relation with keys: <code>name</code> is
        relation name (string)<br /> and <code>attributes</code> is a table with
        relation attributes as string keys
      </td>
    </tr>
    <tr>
      <td><code>row</code></td>
      <td>
        Tuple representing the row-level trigger's target: in update
        operations<br /> holds the <em>new</em> row, otherwise holds the
        <em>old</em> row. <code>row</code> is <code>nil</code> in<br />
        statement-level triggers.
      </td>
    </tr>
    <tr>
      <td><code>old</code></td>
      <td>
        Tuple representing the old row in an update before row-level
        operation.
      </td>
    </tr>
</tbody></table>

<p>Trigger functions in PL/Lua don't return; instead, only for
row-level-before operations, the tuple in <code>trigger.row</code> is read for
the actual returned value. The returned tuple has then the same effect for
general triggers: if <code>nil</code> the operation for the current row is
skipped, a modified tuple will be inserted or updated for insert and update
operations, and <code>trigger.row</code> should not be modified if none of the
two previous outcomes is expected.</p>

<h3>Example</h3>
<p>Let's restrict row operations in our previous binary tree example: updates
are not allowed, deletions are only possible on leaf parents, and insertions
should not introduce cycles and occur only at leaves. We store closures in
<code>_U</code> that have prepared plans as upvalues.</p>

<pre>
create function treetrigger() returns trigger as $$
  local row, operation = trigger.row, trigger.operation
  if operation == "update" then
    trigger.row = nil <em>-- updates not allowed</em>
  elseif operation == "insert" then
    local id, lchild, rchild = row.id, row.lchild, row.rchild
    if lchild == rchild or id == lchild or id == rchild <em>-- avoid loops</em>
        or (lchild ~= nil and _U.intree(lchild)) <em>-- avoid cycles</em>
        or (rchild ~= nil and _U.intree(rchild))
        or (_U.nonemptytree() and not _U.isleaf(id)) <em>-- not leaf?</em>
        then
      trigger.row = nil <em>-- skip operation</em>
    end
  else <em>-- operation == "delete"</em>
    if not _U.isleafparent(row.id) then <em>-- not both leaf parent?</em>
      trigger.row = nil
    end
  end
end
do
  local getter = function(cmd, ...)
    local plan = server.prepare(cmd, {...}):save()
    return function(...)
      return plan:execute({...}, true) ~= nil
    end
  end
  _U = { <em>-- plan closures</em>
    nonemptytree = getter("select * from tree"),
    intree = getter("select node from (select id as node from tree "
      .. "union select lchild from tree union select rchild from tree) as q "
      .. "where node=$1", "int4"),
    isleaf = getter("select leaf from (select lchild as leaf from tree "
      .. "union select rchild from tree except select id from tree) as q "
      .. "where leaf=$1", "int4"),
    isleafparent = getter("select lp from (select id as lp from tree "
      .. "except select ti.id from tree ti join tree tl on ti.lchild=tl.id "
      .. "join tree tr on ti.rchild=tr.id) as q where lp=$1", "int4")
  }
$$ language pllua;
</pre>

<p>Finally, we set the trigger on table <code>tree</code>:</p>

<pre>
create trigger tree_trigger before insert or update or delete on tree
  for each row execute procedure treetrigger();
</pre>


<h2 id="installation">Installation</h2>
<small>How to obtain and install PL/Lua</small>
<p>PL/Lua is distributed as a source package and can be obtained at
<a href="http://pgfoundry.org/projects/pllua">PgFoundry</a>. Depending on how
Lua is installed in your system you might have to edit the Makefile. After
that the source package is installed like any regular PostgreSQL module, that
is, after downloading and unpacking, just run:</p>

<pre>
$ make &amp;&amp; sudo make install
$ psql -c "CREATE EXTENSION pllua" <em>mydb</em>
</pre>

<p>The <code>pllua</code> extension installs both trusted and untrusted
flavors of PL/Lua and creates the module table <code>pllua.init</code>.
Alternatively, a systemwide installation though the PL template facility can
be achieved with:</p>

<pre>
INSERT INTO pg_catalog.pg_pltemplate
  VALUES ('pllua', true, 'pllua_call_handler', 'pllua_validator', '$libdir/pllua', NULL);

INSERT INTO pg_catalog.pg_pltemplate
  VALUES ('plluau', false, 'plluau_call_handler', 'plluau_validator', '$libdir/pllua', NULL);
</pre>


<h3>License</h3>
<p>Copyright (c) 2008 Luis Carvalho</p>

<p>Permission is hereby granted, free of charge, to any person
obtaining a copy of this software and associated documentation files
(the "Software"), to deal in the Software without restriction,
including without limitation the rights to use, copy, modify,
merge, publish, distribute, sublicense, and/or sell copies of the
Software, and to permit persons to whom the Software is furnished
to do so, subject to the following conditions:</p>

<p>The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.</p>

<p>THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS
BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN
ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.</p>

</div><!-- end content -->

<div id="footer">
<p>
<a href="http://gforge.org/">
  <img alt="Powered by GForge" src="http://gforge.org/images/pow-gforge.png" />
</a>
<br />
<a href="http://validator.w3.org/check/referer">Valid XHTML 1.1</a>
&nbsp; :: &nbsp;
<a href="http://jigsaw.w3.org/css-validator/check/referer">Valid CSS</a>
<br />
Last modified: $Date: 2008-03-30 03:45:23 $, $Author: carvalho $.
</p>
</div>

</div><!-- end page -->
</body>
</html>

<!-- vi:set sw=2 ts=2 noai: -->