Module index

source download

Module: postgres.lsp

PostgreSQL interface (tested on PostgreSQL 9.4)

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 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 LINUX/UNIX: /usr/local/lib/
on Mac OS X: /usr/local/lib/libpq.dylib

This library is installed when using the Mac OS X install package here

This library might be in a different location on a particular installation of PostgreSQL or have a different name. Change accordingly in the code at the beginning.

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:query ............... execute a SQL statement
     PgSQL:num-rows ............ rows in result of query
     PgSQL:num-fields .......... columns in result of query
     PgSQL:fnumber ............. column number of query
     PgSQL:fetch-value ......... get value from the query result
     PgSQL:fetch-row ........... get row from the query result
     PgSQL:fetch-all ........... get all rows from the last query
     PgSQL:database ............ return all database names
     PgSQL:tables .............. return all tables names
     PgSQL:fields .............. return all fields in a table
     PgSQL:data-seek ........... position in result for fetching
     PgSQL:error ............... get error message
     PgSQL:affected-rows ....... number of affected rows from operation
     PgSQL:escape .............. escapes input string according to the SQL standard
     PgSQL:close-db ............ close database connection

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.


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



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, options, tty, sslmode, requiressl, krbsrvname, gsslib, service in PostgreSQL 8.3.

return: true for success or nil for 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 one of the following: PGRES_EMPTY_QUERY, PGRES_COMMAND_OK, PGRES_TUPLES_OK, PGRES_COPY_OUT, PGRES_COPY_IN, PGRES_BAD_RESPONSE, PGRES_FATAL_ERROR

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: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 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:escape str-sql)

return: escaped string

This function will escape 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:close-db)

return: Always true.

Closes the database connection and frees associated resources.

- ∂ -

generated with newLISP  and newLISPdoc