Accessing PostgreSQL from Lua

The core of our products is written in C and PostgreSQL is the database of choice. Most extensions to the software, and more recently complete applications, are written in the Lua language. These extensions or applications access internal state of the C core using Lua bindings. While we used Lua to access PostgreSQL since many years already, the Lua interface to PostgreSQL was relatively simple and offered only a subset of libpq's functionality. In preparation of a talk for PGConf.EU 2012, I decided some time ago to make the Lua PostgreSQL binding functionally equivalent to libpq. The old interface lacked a lot of support functions, asynchronous connection and command execution, support for asynchronous notifications etc.

Over the last weeks (or maybe months) I continously extended the Lua PostgreSQL interface, which can be found at https://github.com/mbalmer/luapgsql. The function names are more or less the same as in libpq, minus the PQ prefix. And the C structs PGconn, PGresult etc. are wrapped as Lua "objects" and can use the Lua object syntax object:method():

require 'os'

require 'pgsql'



conn = pgsql.connectdb('dbname=test')

if conn:status() ~= pgsql.CONNECTION_OK then

    print('connection failed')

    os.exit(1)

end



res = conn:exec('select * from mydata')

for n = 1, res:ntuples() do

    print(res:getvalue(n, 1))

end

conn:finish()

Besides the pgsql.connectb() function there is also support for asynchronous connection. And for queries prepared statements can be used as well; all queries can either be executed asynchronous or synchronous.

I apologize that there is no proper english documentation yet, for now you will have to look at luapgsql.c, towards the end of the file, for a list of all functions provided. I hope to have proper documentation ready later this year.