lua-pgsql: a Postgresql driver for lua

(c) 2009-19 Alacner zhang <alacner@gmail.com>
* This content is released under the MIT License.

Table of Contents

Introduction

This is a driver for lua to use Postgresql 8.1.x or newer. You can download lua-pgsql from gitand report bug for me and i'll fix it as quickly as i can.
after download,you can install like this. 
tar xzvf lua-pgsql.*
cd lua-pgsql.*
make
make install

Functions

require "pgsql"

Public objects

create the pgsql connect link handle.

pgsql.version()

pgsql.version()
return the lua-pgsql version infojust like: 
luapgsql (1.0.0) - PostgreSQL driver
(c) 2009-19 Alacner zhang <alacner@gmail.com>
This content is released under the MIT License.

pgsql.conncet(connection_string)

opens a connection to a PostgreSQL database specified by the connection_string . 
local dberr = pgsql.connect('host=localhost dbname=test user=postgres') -- if db == nil then err is the erroror db is the pgsql link handle
The connection_string can be empty to use all default parametersor it can contain one or more parameter settings separated by whitespace. Each parameter setting is in the form keyword = value. Spaces around the equal sign are optional. To write an empty value or a value containing spacessurround it with single quotese.g.keyword = 'a value'. Single quotes and backslashes within the value must be escaped with a backslashi.e.\' and \\. The currently recognized parameter keywords are: host hostaddr port dbname user password connect_timeout options tty (ignored)sslmode requiressl (deprecated in favor of sslmode )and service . Which of these arguments exist depends on your PostgreSQL version.

Link objects

the methods to contol the pgsql link handle

db:port()

returns the port number that the given PostgreSQL connection resource is connected to.

db:dbname()

returns the name of the database that the given PostgreSQL connection resource.

db:tty()

returns the TTY name that server side debugging output is sent to on the given PostgreSQL connection resource.

db:get_pid()

gets the backend's (database server process) PID. The PID is useful to determine whether or not a NOTIFY message received via db:get_notify() is sent from another process or not.

db:get_field_name(field_number)

returns the name of the field occupying the given field_number in the given PostgreSQL result resource. Field numbering starts from 0.
field_number(int) : Field number, starting from 0.

db:get_field_table(field_number)

returns the name of the table that field belongs to, or the table's oid if oid_only is TRUE.
field_number(int) : Field number, starting from 0.

db:ping()

pings a database connection and tries to reconnect it if it is broken.

db:version()

returns an array with the client, protocol and server version. Protocol and server versions are only available if LUA was compiled with PostgreSQL 7.4 or later. For more detailed server information, use db:parameter_status().

db:connection_status()

returns the status of the specified connection .
Return Values : PGSQL_CONNECTION_OK or PGSQL_CONNECTION_BAD.

connection_busy()

determines whether or not a connection is busy. If it is busy, a previous query is still executing. If db:get_result() is used on the connection, it will be blocked.
Return Values: Returns TRUE if the connection is busy, FALSE otherwise.

connection_reset()

resets the connection. It is useful for error recovery.

db:transaction_status()

Returns the current in-transaction status of the server.
Return Values: The status can be PGSQL_TRANSACTION_IDLE (currently idle), PGSQL_TRANSACTION_ACTIVE (a command is in progress), PGSQL_TRANSACTION_INTRANS (idle, in a valid transaction block), or PGSQL_TRANSACTION_INERROR (idle, in a failed transaction block). PGSQL_TRANSACTION_UNKNOWN is reported if the connection is bad. PGSQL_TRANSACTION_ACTIVE is reported only when a query has been sent to the server and not yet completed.

db:options()

will return a string containing the options specified on the given PostgreSQL connection resource.

db:parameter_status(param_name)

Looks up a current parameter setting of the server.
param_name(string): Possible param_name values include server_version, server_encoding, client_encoding, is_superuser, session_authorization, DateStyle, TimeZone, and integer_datetimes.

db:last_error()

returns the last error message for a given connection .

db:escape_bytea(data)

escapes string for bytea datatype. It returns escaped string.
data(string): A string containing text or binary data to be inserted into a bytea column.

db:unescape_bytea(data)

unescapes PostgreSQL bytea data values. It returns the unescaped string, possibly containing binary data.
data(string): A string containing PostgreSQL bytea data to be converted into a PHP binary string.

db:escape_string(data)

escapes a string for insertion into the database. It returns an escaped string in the PostgreSQL format. If the type of the column is bytea, db:escape_bytea() must be used instead.

db:cancel_query()

cancels an asynchronous query sent with db:send_query(), db:send_query_params() or db:send_execute(). You cannot cancel a query executed using db:query().

db:trace(pathname, mode='w')

enables tracing of the PostgreSQL frontend/backend communication to a file. To fully understand the results, one needs to be familiar with the internals of PostgreSQL communication protocol. For those who are not, it can still be useful for tracing errors in queries sent to the server, you could do for example grep '^To backend' trace.log and see what queries actually were sent to the PostgreSQL server. For more information, refer to the ยป PostgreSQL Documentation.
pathname(string): The full path and file name of the file in which to write the trace log. Same as in fopen(). mode(string): An optional file access mode, same as for fopen(). Defaults to "w".

db:untrace()

Stop tracing started by db:trace().

db:client_encoding()

PostgreSQL supports automatic character set conversion between server and client for certain character sets. db:client_encoding() returns the client encoding as a string. The returned string will be one of the standard PostgreSQL encoding identifiers.
Return Values: The client encoding, or FALSE on error.

db:set_client_encoding(encoding)

sets the client encoding and returns 0 if success or -1 if error. PostgreSQL will automatically convert data in the backend database encoding into the frontend encoding.

db:set_error_verbosity()

sets the verbosity mode, returning the connection's previous setting. In PGSQL_ERRORS_TERSE mode, returned messages include severity, primary text, and position only; this will normally fit on a single line. The default mode (PGSQL_ERRORS_DEFAULT) produces messages that include the above plus any detail, hint, or context fields (these may span multiple lines). The PGSQL_ERRORS_VERBOSE mode includes all available fields. Changing the verbosity does not affect the messages available from already-existing result objects, only subsequently-created ones.
verbosity(string): The required verbosity: PGSQL_ERRORS_TERSE, PGSQL_ERRORS_DEFAULT or PGSQL_ERRORS_VERBOSE.

db:query(query)

executes the query on the specified database connection . If an error occurs, and FALSE is returned, details of the error can be retrieved using the db:last_error() function if the connection is valid.
query The SQL statement or statements to be executed. When multiple statements are passed to the function, they are automatically executed as one transaction, unless there are explicit BEGIN/COMMIT commands included in the query string. However, using multiple transactions in one function call is not recommended. Data inside the query should be properly escaped.

db:query_params(query, params)

is like db:query(), but offers additional functionality: parameter values can be specified separately from the command string proper. db:query_params() is supported only against PostgreSQL 7.4 or higher connections; it will fail when using earlier versions. If parameters are used, they are referred to in the query string as $1, $2, etc. params specifies the actual values of the parameters. A NULL value in this array means the corresponding parameter is SQL NULL. The primary advantage of db:query_params() over db:query() is that parameter values may be separated from the query string, thus avoiding the need for tedious and error-prone quoting and escaping. Unlike db:query(), db:query_params() allows at most one SQL command in the given string. (There can be semicolons in it, but not more than one nonempty command.)
query(string): The parameterized SQL statement. Must contain only a single statement. (multiple statements separated by semi-colons are not allowed.) If any parameters are used, they are referred to as $1, $2, etc. params(string/table): An array of parameter values to substitute for the $1, $2, etc. placeholders in the original prepared query string. The number of elements in the array must match the number of placeholders. 
local result = db:query_params('SELECT * FROM names WHERE name = $1', "name1");
local result = db:query_params('SELECT * FROM names WHERE name = $1 and name2 = $2', {"name1", "name2"});

db:prepare(stmtname, query)

creates a prepared statement for later execution with db:execute() or db:send_execute(). This feature allows commands that will be used repeatedly to be parsed and planned just once, rather than each time they are executed. db:prepare() is supported only against PostgreSQL 7.4 or higher connections; it will fail when using earlier versions. The function creates a prepared statement named stmtname from the query string, which must contain a single SQL command. stmtname may be "" to create an unnamed statement, in which case any pre-existing unnamed statement is automatically replaced; otherwise it is an error if the statement name is already defined in the current session. If any parameters are used, they are referred to in the query as $1, $2, etc. Prepared statements for use with db:prepare() can also be created by executing SQL PREPARE statements. (But db:prepare() is more flexible since it does not require parameter types to be pre-specified.) Also, although there is no LUA function for deleting a prepared statement, the SQL DEALLOCATE statement can be used for that purpose.
stmtname (string): The name to give the prepared statement. Must be unique per-connection. If "" is specified, then an unnamed statement is created, overwriting any previously defined unnamed statement. query (string): The parameterized SQL statement. Must contain only a single statement. (multiple statements separated by semi-colons are not allowed.) If any parameters are used, they are referred to as $1, $2, etc.

db:execute(stmtname, params)

is like db:query_params(), but the command to be executed is specified by naming a previously-prepared statement, instead of giving a query string. This feature allows commands that will be used repeatedly to be parsed and planned just once, rather than each time they are executed. The statement must have been prepared previously in the current session. pg_execute() is supported only against PostgreSQL 7.4 or higher connections; it will fail when using earlier versions. The parameters are identical to db:query_params(), except that the name of a prepared statement is given instead of a query string.

db:send_query()

like db:query() but asynchronously.

db:send_prepare()

like db:prepare() but asynchronously.

db:send_execute()

like db:execute() but asynchronously.

db:send_query_params()

like db:query_params() but asynchronously.

db:get_result()

gets the result resource from an asynchronous query executed by db:send_query(), db:send_query_params() or db:send_execute(). db:send_query() and the other asynchronous query functions can send multiple queries to a PostgreSQL server and db:get_result() is used to get each query's results, one by one.

db:put_line(data)

sends a NULL-terminated string to the PostgreSQL backend server. This is needed in conjunction with PostgreSQL's COPY FROM command. COPY is a high-speed data loading interface supported by PostgreSQL. Data is passed in without being parsed, and in a single transaction. An alternative to using raw db:put_line() commands is to use db:copy_from(). This is a far simpler interface.

db:get_notify()

gets notifications generated by a NOTIFY SQL command. To receive notifications, the LISTEN SQL command must be issued.
result_type (string): An optional parameter that controls how the returned array is indexed. result_type is a constant and can take the following values: PGSQL_ASSOC, PGSQL_NUM and PGSQL_BOTH. Using PGSQL_NUM, db:get_notify() will return an array with numerical indices, using PGSQL_ASSOC it will return only associative indices while PGSQL_BOTH, the default, will return both numerical and associative indices.

db:end_copy()

syncs the PostgreSQL frontend (usually a web server process) with the PostgreSQL server after doing a copy operation performed by db:put_line(). db:end_copy() must be issued, otherwise the PostgreSQL server may get out of sync with the frontend and will report an error.

db:meta_data(table_name)

returns table definition for table_name as an array.
table_name(string): The name of the table.

db:lo_create()

creates a large object and returns the OID of the large object. PostgreSQL access modes INV_READ, INV_WRITE, and INV_ARCHIVE are not supported, the object is created always with both read and write access. INV_ARCHIVE has been removed from PostgreSQL itself (version 6.3 and above). To use the large object interface, it is necessary to enclose it within a transaction block. Instead of using the large object interface (which has no access controls and is cumbersome to use), try PostgreSQL's bytea column type and db:escape_bytea().

db:lo_unlink(oid)

deletes a large object with the oid . Returns TRUE on success or FALSE on failure.
oid (int): The OID of the large object in the database.

db:lo_open(oid, mode)

opens a large object in the database and returns large object resource so that it can be manipulated.
oid(int) : The OID of the large object in the database. mode : Can be either "r" for read-only, "w" for write only or "rw" for read and write.

db:lo_close(oid)

closes a large object. large_object is a resource for the large object from db:lo_open().

db:lo_read(oid[,len])

reads at most len bytes from a large object and returns it as a string.
oid: PostgreSQL large object (LOB) resource, returned by db:lo_open(). len: An optional maximum number of bytes to return. Defaults to 8192.

db:lo_write(oid, data[, len])

writes data into a large object at the current seek position.
data: The data to be written to the large object. If len is specified and is less than the length of data , only len bytes will be written. len :An optional maximum number of bytes to write. Must be greater than zero and no greater than the length of data . Defaults to the length of data .

db:lo_read_all(oid)

read all data in maximum number of bytes to return, Defaults to 8192
Return Values: 1) Number of bytes read or FALSE on error. 2) The data contents.

db:lo_import(pathname, oid)

creates a new large object in the database using a file on the filesystem as its data source.
pathname : The full path and file name of the file on the client filesystem from which to read the large object data.

db:lo_export(oid, pathname)

takes a large object in a PostgreSQL database and saves its contents to a file on the local filesystem.

db:lo_seek(oid, offset[,whence='PGSQL_SEEK_CUR'])

seeks a position within a large object resource.
offset(int): The number of bytes to seek. whence(string) :One of the constants PGSQL_SEEK_SET (seek from object start), PGSQL_SEEK_CUR (seek from current position) or PGSQL_SEEK_END (seek from object end)

db:lo_tell(oid)

returns the current position (offset from the beginning) of a large object.
Return Values: The current seek offset (in number of bytes) from the beginning of the large object. If there is an error, the return value is negative.

db:close()

closes the non-persistent connection to a PostgreSQL database associated with the given connection resource.

Result objects

the methods to contol the pgsql result handle

res:field_num(field_name)

will return the number of the field number that corresponds to the field_name in the given PostgreSQL result resource.
field_name(string): The name of the field.

res:field_name(field_number)

returns the name of the field occupying the given field_number in the given PostgreSQL result resource. Field numbering starts from 0.

res:field_table(field_number[,oid_only=0])

returns the name of the table that field belongs to, or the table's oid if oid_only is TRUE.

res:field_size(field_number)

returns the internal storage size (in bytes) of the field number in the given PostgreSQL result .

res:field_type()

returns a string containing the base type name of the given field_number in the given PostgreSQL result resource.

res:field_type_oid(field_number)

returns an integer containing the OID of the base type of the given field_number in the given PostgreSQL result resource.

res:field_is_null(row, field)

tests if a field in a PostgreSQL result resource is SQL NULL or not.
row :Row number in result to fetch. Rows are numbered from 0 upwards. If omitted, current row is fetched. field :Field number (starting from 0) as an integer or the field name as a string.

res:field_prtlen(field_name_or_number)

returns the actual printed length (number of characters) of a specific value in a PostgreSQL result . Row numbering starts at 0. This function will return -1 on an error.

res:fetch_row()

fetches one row of data from the result associated with the specified result resource.

res:fetch_assoc()

returns an associative array that corresponds to the fetched row (records).

res:fetch_array(result_type)

is an extended version of res:fetch_row(). In addition to storing the data in the numeric indices (field number) to the result array, it can also store the data using associative indices (field name). It stores both indicies by default.
result_type :An optional parameter that controls how the returned array is indexed. result_type is a constant and can take the following values: PGSQL_ASSOC, PGSQL_NUM and PGSQL_BOTH. Using PGSQL_NUM, res:fetch_array() will return an array with numerical indices, using PGSQL_ASSOC it will return only associative indices while PGSQL_BOTH, the default, will return both numerical and associative indices.

res:fetch_result(field)

returns the value of a particular row and field (column) in a PostgreSQL result resource.
field: A string representing the name of the field (column) to fetch, otherwise an int representing the field number to fetch. Fields are numbered from 0 upwards.

res:fetch_all()

returns an array that contains all rows (records) in the result resource.

res:fetch_all_columns(column)

returns an array that contains all rows (records) in a particular column of the result resource.
column :Column number, zero-based, to be retrieved from the result resource. Defaults to the first column if not specified.

res:last_oid()

is used to retrieve the OID assigned to an inserted row.

res:result_error()

returns any error message associated with the result resource. Therefore, the user has a better chance of getting the correct error message than with res:last_error().

res:result_seek(offset)

sets the internal row offset in a result resource.

res:result_status(type)

returns the status of a result resource, or the PostgreSQL command completion tag associated with the result
type :Either PGSQL_STATUS_LONG to return the numeric status of the result , or PGSQL_STATUS_STRING to return the command tag of the result . If not specified, PGSQL_STATUS_LONG is the default.

res:result_error_field(fieldcode)

returns one of the detailed error message fields associated with result resource. It is only available against a PostgreSQL 7.4 or above server. The error field is specified by the fieldcode .
fieldcode :Possible fieldcode values are: PGSQL_DIAG_SEVERITY, PGSQL_DIAG_SQLSTATE, PGSQL_DIAG_MESSAGE_PRIMARY, PGSQL_DIAG_MESSAGE_DETAIL, PGSQL_DIAG_MESSAGE_HINT, PGSQL_DIAG_STATEMENT_POSITION, PGSQL_DIAG_INTERNAL_POSITION (PostgreSQL 8.0+ only), PGSQL_DIAG_INTERNAL_QUERY (PostgreSQL 8.0+ only), PGSQL_DIAG_CONTEXT, PGSQL_DIAG_SOURCE_FILE, PGSQL_DIAG_SOURCE_LINE or PGSQL_DIAG_SOURCE_FUNCTION.

res:free_result()

frees the memory and data associated with the specified PostgreSQL query result resource.

res:num_fields()

returns the number of fields (columns) in a PostgreSQL result resource.

res:num_rows()

return the number of rows in a PostgreSQL result resource.

res:affected_rows()

returns the number of tuples (instances/records/rows) affected by INSERT, UPDATE, and DELETE queries.