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
最新评论