Module index

source download

Module: postgres.lsp

PostgreSQL interface (tested on PostgreSQL 9.4, should work on all supported versions)

Version: 1.02 - feature complete
Version: 1.03 - doc formatting
Version: 2.00 - replaced inc with ++
Version: 2.10 - new fnumber and fetch-value
Version: 2.11 - new query with optional parameters
Version: 2.12 - add MacPorts path, Fix bugs (error), (affected-rows), (fields ...), test against PostgreSQL 9.4
Version: 3.00 - add new postgreSQL imports, use pg_config to find libpq header, improve test coverage
Author: Jeremy Cowgar 2006, Ted Walther 2009, Lutz Mueller 2010, Unya 2012, Neil Tiffin 2015


At the beginning of the program file include a load statement for the module:
 (load "/usr/share/newlisp/modules/postgres.lsp")
 ; or
 (module "postgres.lsp") ; loads from (env "NEWLISPDIR") / modules

A version of libpq for a specific platform is required:

on Mac OS X: libpq.dylib
on Windows: libpq.dll

This library is installed when using the install package . Unix, Linux, and Mac each have package installers that can be used to install the PostgreSQL client (libpq) & server.

Libpq might be in a different location on a particular installation of PostgreSQL or have a different extension. This module attempts to find libpq using pg_config. If you can execute pg_config at the command line and see its results then everything should just work. If not, then you may have to edit the manual search code below to find your specific libpq.

If you are using PostgreSQL from a Linux distribution you will need to install the development headers for libpq, usually called libpq-dev.

The PostgreSQL server itself may reside on a different machine on the network. The library libpq will communicate with that server. The correct connection is created using the PgSQL:connect call.

At the bottom of the module file postgres.lsp a test routine test-pgsql is included to test for correct installation of PostgreSQL. You call it with the same arguments you would pass to :connect

Functions available

     PgSQL:connect ............. connect to a database
     PgSQL:close-db ............ close database connection
     PgSQL:error-conn .......... get connection error message *

Query PgSQL:query ............... execute a SQL statement

Query Results PgSQL:affected-rows ....... number of affected rows from operation PgSQL:data-seek ........... position in result for fetching PgSQL:error ............... get query error message PgSQL:fetch-all ........... get all rows from the last query PgSQL:fetch-row ........... get row from the query result PgSQL:fetch-value ......... get value from the query result PgSQL:fnumber ............. column number of query PgSQL:num-fields .......... columns in result of query PgSQL:num-rows ............ rows in result of query

Info PgSQL:database ............ return all database names PgSQL:fields .............. return all fields in a table PgSQL:tables .............. return all tables names

Misc PgSQL:escape .............. escapes single quote in input string PgSQL:escape-literal ...... escapes literal for PostgreSQL * PgSQL:escape-identifier ... escapes identifier for PostgreSQL *

* API may be specific to PostgreSQL

Differences from the MySQL module

The function :inserted-id isn't supported because PostgreSQL doesn't support it. Instead, use the RETURNING clause in your INSERT statement, then use :fetch-row or :fetch-all to find the value. INSERT RETURNING is a PostreSQL idiom documented here.

There is no :init function because it isn't needed by the underlying library. Just call :connect.

A typical PgSQL session

The following code piece outlines a typical PgSQL session:

 (module "postgres.lsp") ; load the module file

 (PgSQL:connect "" "auser" "secret" "mydb") ; logon
 (PgSQL:query "select ...;") ; SQL query
 (PgSQL:query "insert ...;") ; SQL query
The database server is listening on IP The program connects with username "auser" password "secret" to a database with the name "mydb". After connecting SQL statements are performed and finally the program disconnects from the server.

If the database server is running locally then "localhost" may be used for the host name.


This module doesn't support connections through a Unix socket.

Implementation Notes

As of 19 March 2015. On Windows only works with x86 (32bit) PostgreSQL install. pg_config must be in the path. Tested on OSX 10.10.2, Linux SMP Debian 3.16.7-ckt7-1, Windows 8.1 [version 6.3.9600]



syntax: (PgSQL:connect str-server str-userID str-password str-db)
parameter: str-server - The host name or IP address or 0 for localhost.
parameter: str-userID - The user ID for authentication.
parameter: str-password - The password for authentication.
parameter: str-db - The name of the database to connect to.

return: true for success or nil for failure.

Connects to a database on server and authenticates a user ID.



syntax: (PgSQL:connectdb str-conninfo)
parameter: str-conninfo - PostgreSQL Connection Parameters, To write an empty value or a value containing spaces, surround it with single quotes, e.g., keyword = a value. keyword is host, hostaddr, 'port, dbname, user, password, connect_timeout, client_encoding, options, application_name, fallback_application_name, keepalives, keepalives_idle, keepalives_interval, keepalives_count, tty, sslmode, sslcompression, sslcert, sslkey, sslrootcert, sslcrl, requirepeer, krbsrvname, gsslib, service in PostgreSQL 9.4.

return: true for success or nil on failure.

Connects to a database on server.



syntax: (PgSQL:query str-sql [param ...])
parameter: str-sql - A valid SQL query string. If parameters are used, they are referred to in the command string as $1, $2, etc.
parameter: param - Specifies the actual values of the parameters.

return: Returns a numeric status code

Sends a SQL query string to the database server for evaluation. The return value will be an integer representing one of the following enumerated types: PGRES_EMPTY_QUERY, PGRES_COMMAND_OK, PGRES_TUPLES_OK, PGRES_COPY_OUT, PGRES_COPY_IN, PGRES_BAD_RESPONSE, PGRES_FATAL_ERROR.

The numeric status code can be converted to a string using (PgSQL:result-str status-code). The numeric status code should not be used directly.

From the libpq documentation:
If the result status is PGRES_TUPLES_OK, then the functions described below can be used to retrieve the rows returned by the query. Note that a SELECT command that happens to retrieve zero rows still shows PGRES_TUPLES_OK. PGRES_COMMAND_OK is for commands that can never return rows (INSERT, UPDATE, etc.). A response of PGRES_EMPTY_QUERY might indicate a bug in the client software.

 (PgSQL:query "select $1||$2" "abc" "def")
 (PgSQL:fetch-all) ; -> (("abcdef"))

 (PgSQL:query "select $1 + $2" 10 20)
 (PgSQL:fetch-all) ; -> (("30"))

 (PgSQL:query "select $1::timestamp + $2::interval" "2012-10-01 00:00:00" "123456 seconds")
 (PgSQL:fetch-all) ; -> (("2012-10-02 10:17:36"))

 (PgSQL:query "create table tbl (a integer, b integer)")
 (dotimes (i 10) (PgSQL:query "insert into tbl values ($1, $2)" i (* i 2)))
 ;    a | b
 ;   ---+----
 ;    0 |  0
 ;    1 |  2
 ;    2 |  4
 ;    ...
 ;    9 | 18

 (PgSQL:query "select * from tbl where a=$1 or a=$2" 2 9)
 (PgSQL:fetch-all) ; -> (("2" "4") ("9" "18"))



syntax: (PgSQL:num-rows)

return: Number of rows from last query.



syntax: (PgSQL:num-fields)

return: Number of columns from last query.



syntax: (PgSQL:fnumber str-column)
parameter: str-column - The column name.

return: the column number associated with the given column name. return nil when not found column name.



syntax: (PgSQL:fname int-column)
parameter: int-column - The integer column number.

return: the column name associated with the given column number. return nil when not found column name.



syntax: (PgSQL:fetch-value num-row col)
parameter: num-row - row number
parameter: col - column number or column name string.

return: A single value

Fetches the single value in the row and column specified. Used by the :fetch-row and :fetch-all functions. A field containing the NULL value will return the symbol NULL



syntax: (PgSQL:fetch-row)

return: A list of field elements.

Fetches a row from a previous SQL PgSQL:query select statement. Subsequent calls fetch row by row from the result table until the end of the table is reached.



syntax: (PgSQL:fetch-all)

return: All rows/fields from the last query, or nil

The whole result set from the query is returned at once as a list of row lists.



syntax: (PgSQL:databases)

return: A list of databases.

Performs the query SELECT datname FROM pg_database which shows all the database schemas hosted by the connected server.



syntax: (PgSQL:tables)

return: A list of tables in the database, or nil

Performs the query SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'



syntax: (PgSQL:fields str-table)
parameter: str-table - The name of the table.

return: A list of fields in the table, or nil

Unlike the equivalent function in the MySQL module, this function only shows the names of all the fields in the given table. It does not show the field specification, which you would need to recreate the table.



syntax: (PgSQL:data-seek num-offset)
parameter: num-offset - The '0' based offset to position inside the data set.

return: Always true.

Sets a position in the result set which will be used by the next PgSQL:fetch-row call. If the offset is out of the allowed range for the result set a subsequent fetch-row will return nil.



syntax: (PgSQL:error)

return: A string containing the query result error message.

If there was no error, this function returns nil.



syntax: (PgSQL:affected-rows)

return: Number of rows affected by the last PgSQL:query operation, or nil

This function will only return a value following the execution of an INSERT, UPDATE, DELETE, MOVE, FETCH, or COPY statement, or an EXECUTE of a prepared query that contains an INSERT, UPDATE, or DELETE statement. It will return nil after all other queries.



syntax: (PgSQL:error-conn)

return: error message string

Returns the error message most recently generated by an operation on the connection.



syntax: (PgSQL:escape str-sql)

return: escaped string

This function only escapes the ' character in str-sql, as per the SQL standard. Depending on whether you are using binary data or have configured Postgres to allow C escapes you may need more advanced escaping than this function provides.



syntax: (PgSQL:escape-literal str)
parameter: str - string to be escaped

return: escaped string

This function escapes a string for use within an SQL command. This is useful when inserting data values as literal constants in SQL commands. Certain characters (such as quotes and backslashes) must be escaped to prevent them from being interpreted specially by the SQL parser. escape-literal performs this operation. This function was added in PostgreSQL 9.1 and will throw an error if you are using an older libpq.



syntax: (PgSQL:escape-identifier str)
parameter: str - string to be escaped

return: escaped string

This function escapes a string for use as an SQL identifier, such as a table, column, or function name. This is useful when a user-supplied identifier might contain special characters that would otherwise not be interpreted as part of the identifier by the SQL parser, or when the identifier might contain upper case characters whose case should be preserved. This function was added in PostgreSQL 9.1 and will throw an error if you are using an older libpq.



syntax: (PgSQL:close-db)

return: Always true.

Closes the database connection and frees associated resources.



syntax: (PgSQL:host)

return: connected host name string



syntax: (PgSQL:port)

return: connected port name string



syntax: (PgSQL:db)

return: connected database name string



syntax: (PgSQL:options)

return: connected options string



syntax: (PgSQL:user)

return: connected user name string



syntax: (PgSQL:result-str int-status-code)
parameter: int-status-code - An integer query result status code

return: string for the given integer status code




syntax: (PgSQL:lib-version)

return: libpq version as string (e.g. "9.1.1")



syntax: (PgSQL:protocol-version)

return: protocol version "2", or "3" as string, "0" bad connection.

Interrogates the frontend/backend protocol being used.



syntax: (PgSQL:server-version)

return: backend server version as string (e.g. "9.1.1")

- ∂ -

generated with newLISP  and newLISPdoc