Postgresql client

Library to interface with a Postgresql database. The library supports versions 12 and 14 Provides two submodules, Connection and Statement. Connection module is useful to acquire connections and to prepare statements, while the stament module is useful to execute the prepared statements and fetch results

Library implements a latent connection pool, within which connections are maintained.
When "new" is invoked, it checks if there already a connection in the pool, if so the same is returned else a new connection is established and returned.
Upon closing the connection or upon garbage collection by lua, the connection handle if still live is added back to the pool.

The library is inspired by the design in the library luadbi Two major changes are done to the design

• Implementation of asynchronous Postgresql interface (with support from the libpq async functions)
• Support for major Postgresql schema datatypes, short, int, long, binary, date, string etc...

Synopsis

local ffi = require("ffi");
local pg_lib = package.loadlib('libevpostgres.so','luaopen_evrdbms_postgres')();
ffi.cdef[[
void * pin_loaded_so(const char * );
]]
local flg, lib = pcall(ffi.C.pin_loaded_so, ffi.cast("const char*", "libevpostgres.so"));
if (not flg) then
    error("Could not load library [libevpostgres.so] : "..lib);
end

The library is maintained per instance of evlua/evluaserver and will not get reinitialized if already initialized once (ffi is the foreign function interface to lua) The function pin_loaded_so ensures that the loaded dll remains in the process and will not get removed once the dlclose function is called, this is necessary to retain some of the static variables initialized in libevpoolrepos.so.

Connection Establishment

pg_lib.new(host, port, dbname, user, password);

Opens a new database connection to the server using the parameters
local connection = pg_lib.new(host, port, dbname, user, password);

Parameters:
    host: string
    port: string
    dbname: string
    user: string
    password: string
Return:
    connection: userdata, connection handle, nil is returned in case of error

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

Connection Operations

connection.prepare(connection, sql_stmt_source, sql_\stmt);

Prepares a sql statement to be executed in the database

Parameters:
    connection: userdata
	sql_stmt_srouce: string, used as key to cache prepared statements
	sql_stmt: string, SQL text
Return:
    statement: userdata, handle to a statement ,nil is returned in case of error

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

connection.reset_connection_error(connection);

Resets the connection state as not containing any errors in the session (during transaction)

Parameters:
    connection: userdata
Return:
    none

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

connection.ping(connection);

Checks if the connection is live

Parameters:
    connection: userdata
Return:
    flg: boolean, true if live

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

connection.quote(connection, sql_stmt);

Escapes the certain characters like single-quote, back-slash etc... in order to make
an SQL statement proper for execution.

Parameters:
    connection: userdata
    sql_stmt: string
Return:
    none

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

connection.close(connection);

Closes the database connection

Parameters:
    connection: userdata
Return:
    none

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

Statement Operations

statement.execute(statement);

Executes a prepared statement
In case of error, the connection state is marked as having error

Parameters:
    statement, userdata, handle to statement object
Return:
    status: boolean, true - successful, false - otherwise
	[msg]: error message in case of unsuccessful execution

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

statement.rowcount(statement);

Returns the number of rows retrieved after the execution of a select operation.

Parameters:
    statement, userdata, handle to statement object
Return:
    count: integer

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

statement.rows(statement, columns);

Iterate over returned data returns the data as a numerically indexed table

for row in sth:rows() do
    print(row[1])
    print(row[2])
end

Parameters:
    statement, userdata, handle to statement object
Return:
    row: table, result row columns numerically indexed in the table
         nil at the end of rows

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

statement.affected(statement);

Returns the number of rows affected by an insert, update or delete operation.  
Does not return the number of rows retrieved by a select.

Parameters:
    statement, userdata, handle to statement object
Return:
    row: table, result row columns numerically indexed in the table

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

statement.columns(statement);

Returns an ordered table of column names from the result set.

Parameters:
    statement, userdata, handle to statement object
Return:
    column_names: table

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

statement.fetch(statement);

Returns the next single row (as a numerically indexed table) in the result set of
a select operation.

Parameters:
    statement, userdata, handle to statement object
Return:
    row: table (numerically indexed), nil in case there are no more rows

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall

statement.close(statement);

Closes the statement handle.  Once a statement has been closed any further operations
on that object will produce an error.

Parameters:
    statement, userdata, handle to statement object
Return:
    none

ERROR:
    Exceptions are thrown upon error, which can be caught via mechanism of pcall/xpcall