16hot 的博客

Today was the third meeting of the OrlandoPg Meetup group that I host. A member Steven wanted to talk about how he could interact with PostgreSQL using Lua. I informed the group about PostgreSQL’s architecture that allows stored procedures to be written in nearly any programming language. We also talked about how pretty much every language I can think of has a database interaction library of some sort.

That led to some quick googling and we spent the rest of the meeting in a coding dojo like environment to see if we could set up and work with PL/Lua and luasql-postgres. This post will detail how to set up the environment, create a simple stored procedure in PL/Lua, and write a program using the luasql-postgres package or “rock”.

The set up: I’m using a mac for development. OS 10.6.6 to be exact.

To install the Lua programming language I used Mac-Ports.
“sudo port install luarocks +curl openssl”

is the command I used. I actually already had lua installed, so I just installed the luarocks package manager. If you do not have lua installed, this command will install it as well.

Next I needed to get the lua package (rock) luasql-postgres.
“sudo luarocks install –from=http://luarocks.org/repositories/rocks-cvs/ luasql-postgres POSTGRES_DIR=/usr/local/pgsql”

makes that happen. This assumes you already have PostgreSQL installed. If you don’t use macports to install that and adjust your POSTGRES_DIR accordingly. I always install PostgreSQL from source myself.

Next we need to get the PL/Lua extension for PostgreSQL. It can be downloaded from: PgFoundry Once you have it un-tar it and edit the Makefile. Adjust the lines:

LUAINC = -I/opt/local/include

LUALIB = -L/opt/local/lib -llua

Save the file the run
“sudo make && make install”

You now need to install the language into your database. Head over to your contrib directory, mine is:

“/usr/local/pgsql/share/contrib/”
and run

“psql -f pllua.sql DATABASENAME”.

This registers the language handler within your database.

Now you can create a stored procedure using Lua. Example:

create or replace function hello(_name text) returns text as
$$
return string.format(“Hello, %s!”, _name)
$$ Language pllua;

Now you can create a simple lua script to call this function. Example:

require “luasql.postgres”

env = assert ( luasql.postgres() )

con = assert ( env:connect( “DBNAME”,”USERNAME”,”PASS”,”DBHOST” ) )

cur = assert ( con:execute( “SELECT * FROM hello(‘World!’);” ) )

row = assert ( cur:fetch() )

print(row)

One non-standard thing I had to do to get this script to “see” the luasql.postgres extension was to make a symlink:

“sudo ln -s /opt/local/var/luarocks/lib/lua/5.1/luasql/postgres.so /opt/local/lib/lua/5.1/luasql.so”

This is necessary because MacPorts and LuaRocks are not really working together.

You can execute the lua script with the command “”lua SCRIPTNAME” and get the expected output of:

References:

http://www.nessie.de/mroth/lunit/

http://www.lua.org/manual/5.1/manual.html

http://pllua.projects.postgresql.org/

http://www.keplerproject.org/luasql/index.html

现在在Mac 系统下发的博客。早就心仪Mac OS X系统了，漂亮程度用华丽两字来形容，一点也不过分。不过，Mac OS X系统，对硬件是有要求的。当然是苹果推出的系列产品兼容最好，但是价格比较高。虽然Intel平台，有些也能运行，但是可能遇到各种各样的意外。

我用的苹果电脑？没有，现在是在thinkpad 笔记本里，FreeBSD ＋ VirtualBox ＋ Mac OS X的组合。下个目标想换成苹果电脑，先用这个组合熟悉熟悉Mac系统，以后换成苹果电脑后，好快速上手。