PostgreSQL is a free implementation of the relational database system,
which is available from
Once PostgreSQL is installed on your computer, EusLisp provides
links to the databases via the libpq.so library.
- Connecting to the Postgres database
Instantiate pq:pgsql with proper arguments. In most cases,
you just want to specify the database name and the user name.
If you don't know, just trust the defaults, namely
(instance pq:pgsql :init) is usually ok to make a connection.
- Synchronous data transfer
There are the synchronous and asynchronous interface in libpq.so.
Synchronous transfer is easier. You send SQL commands by
:exec method of the pgsql object, and get the result.
(send db :exec "select typname,oid from pg_type order by oid")
will give you a list of all data types defined in your database.
- Asynchronous database access
For asynchronous processing, you have to define a function or
method to receive a query result as the first argument. Let's
assume the receiver function is 'print'. Then a query should be
issued by the :sendQuery method with the receiver function name
as the second argument.
(send db :sendQuery "select oid from pg_type" 'print)
- Type conversion
Postgres database stores data in a variety of forms internally,
but every data item transferred between the database and the client
is always converted to the string format. Thus, integer 1234 is "1234",
and a symbol 'SYMBOL is "symbol". But, of course, since we want to
access a database to store lisp data, they should be handled as
lisp integers and lisp symbols.
I found the datatype information is stored in the pg_type table.
When we get data from a table, we can also retrieve the oid (object id)
attributed to each field. By looking up pg_type table with the oid,
we can know the datatype name, such as integer, character, date, etc.
However, there is no symbol! We can use the 'name' type instead,
but still there is incoherency to use as lisp symbol type, since
there is no escapes (vertical bar and backslash) and lower-case to
upcase conversion. I mean if we use the 'intern' function to
change the 'name' object to symbol, it becomes a symbol with the
lower case print-name. Do we call string-upcase before interning?
Usually it works, but not always, because escapes are ignored.
So I defined input and output function for Postgres in 'symbol_io.c'.
There is also a Makefile for it. Make symbol_io.so and copy it
to /usr/local/pgsql/lib. Invoke psql, and type "isymbol_io.sql",
which will make postgres to load the lisp_symbol_io functions, and
and define the symbol type.
Call make-type-hashtab function once before any other database retrieval
for the faster type look-up.
Then, every data transfered from the database is converted properly.
Currently, symbol, int, float, char (string), date, time, datetime
are coerced to corresponding lisp objects. Other unknown type data
are represented by strings.
The following codes put in another file will load this database module,
creates the *type-hashtab*, and reads the type list.
(unless (boundp 'db)
(setq db (instance pq:pgsql :init) ))
(send db :exec "select * from family")
(setq types (send db :exec "select typname,oid from pg\_type order by oid"))
:init key host port dbname user password [method]
connects to a database designated by host, port and dbname.
Host is defaulted to the localhost. The default port number is 5432.
Default values to dbname and user are obtained from the USER environment variable.
:type-conversion flag [method]
Basically, every result delivered by a database query consists of a string.
If type-conversion is set to NIL, no type conversion is performed, and
query result is returned as a list of strings.
If type-conversion is set to T, number is coerced to number,
and symbol is interned in the current package.
:exec sql [method]
- sends the SQL command to the database.
EusLisp waits for the completion of the database processing and
retrieves the results in a synchronous manner.
pq:table-fields db table [function]
returns the list of all fields in the table
managed in the db database.
Each list element is again a list, describing the field number
starting from one, the symbolic field name, and the field type,
such as text, int4, symbol, etc.
pq:table-attributes db table [function]
returns a list that describes attributes of the given table in db.
The attributes are, name, owner, read-write grants, number of fields,
pq:query db handler &rest sql [function]
sends an SQL command composed by the sql arguments to db.
If handler is specified, the data retrieval is processed in asynchronous
manner. The handler function is invoked when the database processing result
The SQL command is composed by combining sql arguments by
the format function.
pq:tables db [function]
- returns a list of all tables created in db.
pq:delimit-list xlist delimiter [function]
returns a string combining xlist with the constant delimter string.
For example, (delimit-list '(a b c) 'or) returns "a or b or c".
This function is useful to compose SQL commands.
pq:select db fields table &key where limit limit-offset order-by [function]
- sends an SQL command composed by the argument, and retrieves the
result in the synchronous manner.
The following example
gives a list of id, name and email selected from the address_book table
where the email ends with ".go.jp". Number of output lists are limited to
10, and the result is sorted by 'id'.
(select db '(id name email) 'address_book
:where "email like '\*.go.jp'"
pq:record-count db table [function]
returns the number of records in the table. db is a pgsql object.
Travis CI User