61. PostgreSQL Support

XEmacs can be linked with PostgreSQL libpq run-time support to provide relational database access from Emacs Lisp code.

61.1 Building XEmacs with PostgreSQL support

XEmacs PostgreSQL support requires linking to the PostgreSQL libpq library. Describing how to build and install PostgreSQL is beyond the scope of this document. See the PostgreSQL manual for details.

If you have installed XEmacs from one of the binary kits on (ftp://ftp.xemacs.org/), or are using an XEmacs binary from a CD ROM, you may have XEmacs PostgreSQL support by default. M-x describe-installation will tell you if you do.

If you are building XEmacs from source, you need to install PostgreSQL first. On some systems, PostgreSQL will come pre-installed in /usr. In this case, it should be autodetected when you run configure. If PostgreSQL is installed into its default location, `/usr/local/pgsql', you must specify --site-prefixes=/usr/local/pgsql when you run configure. If PostgreSQL is installed into another location, use that instead of `/usr/local/pgsql' when specifying --site-prefixes.

As of XEmacs 21.2, PostgreSQL versions 6.5.3 and 7.0 are supported. XEmacs Lisp support for V7.0 is somewhat more extensive than support for V6.5. In particular, asynchronous queries are supported.

61.2 XEmacs PostgreSQL libpq API

The XEmacs PostgreSQL API is intended to be a policy-free, low-level binding to libpq. The intent is to provide all the basic functionality and then let high level Lisp code decide its own policies.

This documentation assumes that the reader has knowledge of SQL, but requires no prior knowledge of libpq.

There are many examples in this manual and some setup will be required. In order to run most of the following examples, the following code needs to be executed. In addition to the data is in this table, nearly all of the examples will assume that the free variable P refers to this database connection. The examples in the original edition of this manual were run against Postgres 7.0beta1.

(progn
  (setq P (pq-connectdb ""))
  ;; id is the primary key, shikona is a Japanese word that
  ;; means `the professional name of a Sumo wrestler', and
  ;; rank is the Sumo rank name.
  (pq-exec P (concat "CREATE TABLE xemacs_test"
                     " (id int, shikona text, rank text);"))
  (pq-exec P "COPY xemacs_test FROM stdin;")
  (pq-put-line P "1\tMusashimaru\tYokuzuna\n")
  (pq-put-line P "2\tDejima\tOozeki\n")
  (pq-put-line P "3\tMusoyama\tSekiwake\n")
  (pq-put-line P "4\tMiyabiyama\tSekiwake\n")
  (pq-put-line P "5\tWakanoyama\tMaegashira\n")
  (pq-put-line P "\\.\n")
  (pq-end-copy P))
     => nil

61.2.1 libpq Lisp Variables

Various Unix environment variables are used by libpq to provide defaults to the many different parameters. In the XEmacs Lisp API, these environment variables are bound to Lisp variables to provide more convenient access to Lisp Code. These variables are passed to the backend database server during the establishment of a database connection and when the pq-setenv call is made.

Variable: pg:host

Initialized from the PGHOST environment variable. The default host to connect to.

Variable: pg:user

Initialized from the PGUSER environment variable. The default database user name.

Variable: pg:options

Initialized from the PGOPTIONS environment variable. Default additional server options.

Variable: pg:port

Initialized from the PGPORT environment variable. The default TCP port to connect to.

Variable: pg:tty

Initialized from the PGTTY environment variable. The default debugging TTY.

Compatibility note: Debugging TTYs are turned off in the XEmacs Lisp binding.

Variable: pg:database

Initialized from the PGDATABASE environment variable. The default database to connect to.

Variable: pg:realm

Initialized from the PGREALM environment variable. The default Kerberos realm.

Variable: pg:client-encoding

Initialized from the PGCLIENTENCODING environment variable. The default client encoding.

Compatibility note: This variable is not present in non-Mule XEmacsen. This variable is not present in versions of libpq prior to 7.0. In the current implementation, client encoding is equivalent to the file-name-coding-system format.

Variable: pg:authtype

Initialized from the PGAUTHTYPE environment variable. The default authentication scheme used.

Compatibility note: This variable is unused in versions of libpq after 6.5. It is not implemented at all in the XEmacs Lisp binding.

Variable: pg:geqo

Initialized from the PGGEQO environment variable. Genetic optimizer options.

Variable: pg:cost-index

Initialized from the PGCOSTINDEX environment variable. Cost index options.

Variable: pg:cost-heap

Initialized from the PGCOSTHEAP environment variable. Cost heap options.

Variable: pg:tz

Initialized from the PGTZ environment variable. Default timezone.

Variable: pg:date-style

Initialized from the PGDATESTYLE environment variable. Default date style in returned date objects.

Variable: pg-coding-system

This is a variable controlling which coding system is used to encode non-ASCII strings sent to the database.

Compatibility Note: This variable is not present in InfoDock.

61.2.2 libpq Lisp Symbols and Datatypes

The following set of symbols are used to represent the intermediate states involved in the asynchronous interface.

Symbol: pgres::polling-failed

Undocumented. A fatal error has occurred during processing of an asynchronous operation.

Symbol: pgres::polling-reading

An intermediate status return during an asynchronous operation. It indicates that one may use select before polling again.

Symbol: pgres::polling-writing

An intermediate status return during an asynchronous operation. It indicates that one may use select before polling again.

Symbol: pgres::polling-ok

An asynchronous operation has successfully completed.

Symbol: pgres::polling-active

An intermediate status return during an asynchronous operation. One can call the poll function again immediately.

Function: pq-pgconn conn field

conn A database connection object. field A symbol indicating which field of PGconn to fetch. Possible values are shown in the following table.

pq::db

Database name

pq::user

Database user name

pq::pass

Database user's password

pq::host

Hostname database server is running on

pq::port

TCP port number used in the connection

pq::tty

Debugging TTY

Compatibility note: Debugging TTYs are not used in the XEmacs Lisp API.

pq::options

Additional server options

pq::status

Connection status. Possible return values are shown in the following table.

pg::connection-ok

The normal, connected status.

pg::connection-bad

The connection is not open and the PGconn object needs to be deleted by pq-finish.

pg::connection-started

An asynchronous connection has been started, but is not yet complete.

pg::connection-made

An asynchronous connect has been made, and there is data waiting to be sent.

pg::connection-awaiting-response

Awaiting data from the backend during an asynchronous connection.

pg::connection-auth-ok

Received authentication, waiting for the backend to start up.

pg::connection-setenv

Negotiating environment during an asynchronous connection.

pq::error-message

The last error message that was delivered to this connection.

pq::backend-pid

The process ID of the backend database server.

The PGresult object is used by libpq to encapsulate the results of queries. The printed representation takes on four forms. When the PGresult object contains tuples from an SQL SELECT it will look like:

(setq R (pq-exec P "SELECT * FROM xemacs_test;"))
     => #<PGresult PGRES_TUPLES_OK[5] - SELECT>

The number in brackets indicates how many rows of data are available. When the PGresult object is the result of a command query that doesn't return anything, it will look like:

(pq-exec P "CREATE TABLE a_new_table (i int);")
     => #<PGresult PGRES_COMMAND_OK - CREATE>

When either the query is a command-type query that can affect a number of different rows, but doesn't return any of them it will look like:

(progn
  (pq-exec P "INSERT INTO a_new_table VALUES (1);")
  (pq-exec P "INSERT INTO a_new_table VALUES (2);")
  (pq-exec P "INSERT INTO a_new_table VALUES (3);")
  (setq R (pq-exec P "DELETE FROM a_new_table;")))
     => #<PGresult PGRES_COMMAND_OK[3] - DELETE 3>

Lastly, when the underlying PGresult object has been deallocated directly by pq-clear the printed representation will look like:

(progn
  (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
  (pq-clear R)
  R)
     => #<PGresult DEAD>

The following set of functions are accessors to various data in the PGresult object.

Function: pq-result-status result

Return status of a query result. result is a PGresult object. The return value is one of the symbols in the following table.

pgres::empty-query

A query contained no text. This is usually the result of a recoverable error, or a minor programming error.

pgres::command-ok

A query command that doesn't return anything was executed properly by the backend.

pgres::tuples-ok

A query command that returns tuples was executed properly by the backend.

pgres::copy-out

Copy Out data transfer is in progress.

pgres::copy-in

Copy In data transfer is in progress.

pgres::bad-response

An unexpected response was received from the backend.

pgres::nonfatal-error

Undocumented. This value is returned when the libpq function PQresultStatus is called with a NULL pointer.

pgres::fatal-error

Undocumented. An error has occurred in processing the query and the operation was not completed.

Function: pq-res-status result

Return the query result status as a string, not a symbol. result is a PGresult object.

(setq R (pq-exec P "SELECT * FROM xemacs_test;")) => #<PGresult PGRES_TUPLES_OK[5] - SELECT> (pq-res-status R) => "PGRES_TUPLES_OK"

Function: pq-result-error-message result

Return an error message generated by the query, if any. result is a PGresult object.

(setq R (pq-exec P "SELECT * FROM xemacs-test;")) => <A fatal error is signaled in the echo area> (pq-result-error-message R) => "ERROR: parser: parse error at or near \"-\" "

Function: pq-ntuples result

Return the number of tuples in the query result. result is a PGresult object.

(setq R (pq-exec P "SELECT * FROM xemacs_test;")) => #<PGresult PGRES_TUPLES_OK[5] - SELECT> (pq-ntuples R) => 5

Function: pq-nfields result

Return the number of fields in each tuple of the query result. result is a PGresult object.

(setq R (pq-exec P "SELECT * FROM xemacs_test;")) => #<PGresult PGRES_TUPLES_OK[5] - SELECT> (pq-nfields R) => 3

Function: pq-binary-tuples result

Returns t if binary tuples are present in the results, nil otherwise. result is a PGresult object.

(setq R (pq-exec P "SELECT * FROM xemacs_test;")) => #<PGresult PGRES_TUPLES_OK[5] - SELECT> (pq-binary-tuples R) => nil

Function: pq-fname result field-index

Returns the name of a specific field. result is a PGresult object. field-index is the number of the column to select from. The first column is number zero.

(let (i l) (setq R (pq-exec P "SELECT * FROM xemacs_test;")) (setq i (pq-nfields R)) (while (>= (decf i) 0) (push (pq-fname R i) l)) l) => ("id" "shikona" "rank")

Function: pq-fnumber result field-name

Return the field number corresponding to the given field name. -1 is returned on a bad field name. result is a PGresult object. field-name is a string representing the field name to find.

(setq R (pq-exec P "SELECT * FROM xemacs_test;")) => #<PGresult PGRES_TUPLES_OK[5] - SELECT> (pq-fnumber R "id") => 0 (pq-fnumber R "Not a field") => -1

Function: pq-ftype result field-num

Return an integer code representing the data type of the specified column. result is a PGresult object. field-num is the field number.

The return value of this function is the Object ID (Oid) in the database of the type. Further queries need to be made to various system tables in order to convert this value into something useful.

Function: pq-fmod result field-num

Return the type modifier code associated with a field. Field numbers start at zero. result is a PGresult object. field-index selects which field to use.

Function: pq-fsize result field-index

Return size of the given field. result is a PGresult object. field-index selects which field to use.

(let (i l) (setq R (pq-exec P "SELECT * FROM xemacs_test;")) (setq i (pq-nfields R)) (while (>= (decf i) 0) (push (list (pq-ftype R i) (pq-fsize R i)) l)) l) => ((23 23) (25 25) (25 25))

Function: pq-get-value result tup-num field-num

Retrieve a return value. result is a PGresult object. tup-num selects which tuple to fetch from. field-num selects which field to fetch from.

Both tuples and fields are numbered from zero.

(setq R (pq-exec P "SELECT * FROM xemacs_test;")) => #<PGresult PGRES_TUPLES_OK[5] - SELECT> (pq-get-value R 0 1) => "Musashimaru" (pq-get-value R 1 1) => "Dejima" (pq-get-value R 2 1) => "Musoyama"

Function: pq-get-length result tup-num field-num

Return the length of a specific value. result is a PGresult object. tup-num selects which tuple to fetch from. field-num selects which field to fetch from.

(setq R (pq-exec P "SELECT * FROM xemacs_test;")) => #<PGresult PGRES_TUPLES_OK[5] - SELECT> (pq-get-length R 0 1) => 11 (pq-get-length R 1 1) => 6 (pq-get-length R 2 1) => 8

Function: pq-get-is-null result tup-num field-num

Return t if the specific value is the SQL NULL. result is a PGresult object. tup-num selects which tuple to fetch from. field-num selects which field to fetch from.

Function: pq-cmd-status result

Return a summary string from the query. result is a PGresult object.

(setq R (pq-exec P "INSERT INTO xemacs_test VALUES (6, 'Wakanohana', 'Yokozuna');")) => #<PGresult PGRES_COMMAND_OK[1] - INSERT 542086 1> (pq-cmd-status R) => "INSERT 542086 1" (setq R (pq-exec P "UPDATE xemacs_test SET rank='retired' WHERE shikona='Wakanohana';")) => #<PGresult PGRES_COMMAND_OK[1] - UPDATE 1> (pq-cmd-status R) => "UPDATE 1"

Note that the first number returned from an insertion, like in the example, is an object ID number and will almost certainly vary from system to system since object ID numbers in Postgres must be unique across all databases.

Function: pq-cmd-tuples result

Return the number of tuples if the last command was an INSERT/UPDATE/DELETE. If the last command was something else, the empty string is returned. result is a PGresult object.

(setq R (pq-exec P "INSERT INTO xemacs_test VALUES (7, 'Takanohana', 'Yokuzuna');")) => #<PGresult PGRES_COMMAND_OK[1] - INSERT 38688 1> (pq-cmd-tuples R) => "1" (setq R (pq-exec P "SELECT * from xemacs_test;")) => #<PGresult PGRES_TUPLES_OK[7] - SELECT> (pq-cmd-tuples R) => "" (setq R (pq-exec P "DELETE FROM xemacs_test WHERE shikona LIKE '%hana';")) => #<PGresult PGRES_COMMAND_OK[2] - DELETE 2> (pq-cmd-tuples R) => "2"

Function: pq-oid-value result

Return the object id of the insertion if the last command was an INSERT. 0 is returned if the last command was not an insertion. result is a PGresult object.

In the first example, the numbers you will see on your local system will almost certainly be different, however the second number from the right in the unprintable PGresult object and the number returned by pq-oid-value should match.

(setq R (pq-exec P "INSERT INTO xemacs_test VALUES (8, 'Terao', 'Maegashira');")) => #<PGresult PGRES_COMMAND_OK[1] - INSERT 542089 1> (pq-oid-value R) => 542089 (setq R (pq-exec P "SELECT shikona FROM xemacs_test WHERE rank='Maegashira';")) => #<PGresult PGRES_TUPLES_OK[2] - SELECT> (pq-oid-value R) => 0

Function: pq-make-empty-pgresult conn status

Create an empty pgresult with the given status. conn a database connection object status a value that can be returned by pq-result-status.

The caller is responsible for making sure the return value gets properly freed.

61.2.3 Synchronous Interface Functions

Function: pq-connectdb conninfo

Establish a (synchronous) database connection. conninfo A string of blank separated options. Options are of the form "option = value". If value contains blanks, it must be single quoted. Blanks around the equal sign are optional. Multiple option assignments are blank separated.

(pq-connectdb "dbname=japanese port = 25432") => #<PGconn localhost:25432 steve/japanese>

The printed representation of a database connection object has four fields. The first field is the hostname where the database server is running (in this case localhost), the second field is the port number, the third field is the database user name, and the fourth field is the name of the database.

Database connection objects which have been disconnected and will generate an immediate error if they are used look like:

#<PGconn BAD>

Bad connections can be reestablished with pq-reset, or deleted entirely with pq-finish.

A database connection object that has been deleted looks like:

(let ((P1 (pq-connectdb ""))) (pq-finish P1) P1) => #<PGconn DEAD>

Note that database connection objects are the most heavy weight objects in XEmacs Lisp at this writing, usually representing as much as several megabytes of virtual memory on the machine the database server is running on. It is wisest to explicitly delete them when you are finished with them, rather than letting garbage collection do it. An example idiom is:

(let ((P (pq-connectiondb ""))) (unwind-protect (progn (...)) ; access database here (pq-finish P)))

The following options are available in the options string:

authtype

Authentication type. Same as PGAUTHTYPE. This is no longer used.

user

Database user name. Same as PGUSER.

password

Database password.

dbname

Database name. Same as PGDATABASE

host

Symbolic hostname. Same as PGHOST.

hostaddr

Host address as four octets (eg. like 192.168.1.1).

port

TCP port to connect to. Same as PGPORT.

tty

Debugging TTY. Same as PGTTY. This value is suppressed in the XEmacs Lisp API.

options

Extra backend database options. Same as PGOPTIONS.

A database connection object is returned regardless of whether a connection was established or not.

Function: pq-reset conn

Reestablish database connection. conn A database connection object.

This function reestablishes a database connection using the original connection parameters. This is useful if something has happened to the TCP link and it has become broken.

Function: pq-exec conn query

Make a synchronous database query. conn A database connection object. query A string containing an SQL query. A PGresult object is returned, which in turn may be queried by its many accessor functions to retrieve state out of it. If the query string contains multiple SQL commands, only results from the final command are returned.

(setq R (pq-exec P "SELECT * FROM xemacs_test; DELETE FROM xemacs_test WHERE id=8;")) => #<PGresult PGRES_COMMAND_OK[1] - DELETE 1>

Function: pq-notifies conn

Return the latest async notification that has not yet been handled. conn A database connection object. If there has been a notification, then a list of two elements will be returned. The first element contains the relation name being notified, the second element contains the backend process ID number. nil is returned if there aren't any notifications to process.

Function: PQsetenv conn

Synchronous transfer of environment variables to a backend conn A database connection object.

Environment variable transfer is done as a normal part of database connection.

Compatibility note: This function was present but not documented in versions of libpq prior to 7.0.

61.2.4 Asynchronous Interface Functions

Making command by command examples is too complex with the asynchronous interface functions. See the examples section for complete calling sequences.

Function: pq-connect-start conninfo

Begin establishing an asynchronous database connection. conninfo A string containing the connection options. See the documentation of pq-connectdb for a listing of all the available flags.

Function: pq-connect-poll conn

An intermediate function to be called during an asynchronous database connection. conn A database connection object. The result codes are documented in a previous section.

Function: pq-is-busy conn

Returns t if pq-get-result would block waiting for input. conn A database connection object.

Function: pq-consume-input conn

Consume any available input from the backend. conn A database connection object.

Nil is returned if anything bad happens.

Function: pq-reset-start conn

Reset connection to the backend asynchronously. conn A database connection object.

Function: pq-reset-poll conn

Poll an asynchronous reset for completion conn A database connection object.

Function: pq-reset-cancel conn

Attempt to request cancellation of the current operation. conn A database connection object.

The return value is t if the cancel request was successfully dispatched, nil if not (in which case conn->errorMessage is set). Note: successful dispatch is no guarantee that there will be any effect at the backend. The application must read the operation result as usual.

Function: pq-send-query conn query

Submit a query to Postgres and don't wait for the result. conn A database connection object. Returns: t if successfully submitted nil if error (conn->errorMessage is set)

Function: pq-get-result conn

Retrieve an asynchronous result from a query. conn A database connection object.

nil is returned when no more query work remains.

Function: pq-set-nonblocking conn arg

Sets the PGconn's database connection non-blocking if the arg is TRUE or makes it non-blocking if the arg is FALSE, this will not protect you from PQexec(), you'll only be safe when using the non-blocking API. conn A database connection object.

Function: pq-is-nonblocking conn

Return the blocking status of the database connection conn A database connection object.

Function: pq-flush conn

Force the write buffer to be written (or at least try) conn A database connection object.

Function: PQsetenvStart conn

Start asynchronously passing environment variables to a backend. conn A database connection object.

Compatibility note: this function is only available with libpq-7.0.

Function: PQsetenvPoll conn

Check an asynchronous environment variables transfer for completion. conn A database connection object.

Compatibility note: this function is only available with libpq-7.0.

Function: PQsetenvAbort conn

Attempt to terminate an asynchronous environment variables transfer. conn A database connection object.

Compatibility note: this function is only available with libpq-7.0.

61.2.5 Large Object Support

Function: pq-lo-import conn filename

Import a file as a large object into the database. conn a database connection object filename filename to import

On success, the object id is returned.

Function: pq-lo-export conn oid filename

Copy a large object in the database into a file. conn a database connection object. oid object id number of a large object. filename filename to export to.

61.2.6 Other libpq Functions

Function: pq-finish conn

Destroy a database connection object by calling free on it. conn a database connection object

It is possible to not call this routine because the usual XEmacs garbage collection mechanism will call the underlying libpq routine whenever it is releasing stale PGconn objects. However, this routine is useful in unwind-protect clauses to make connections go away quickly when unrecoverable errors have occurred.

After calling this routine, the printed representation of the XEmacs wrapper object will contain the string "DEAD".

Function: pq-client-encoding conn

Return the client encoding as an integer code. conn a database connection object

(pq-client-encoding P) => 1

Compatibility note: This function did not exist prior to libpq-7.0 and does not exist in a non-Mule XEmacs.

Function: pq-set-client-encoding conn encoding

Set client coding system. conn a database connection object encoding a string representing the desired coding system

(pq-set-client-encoding P "EUC_JP") => 0

The current idiom for ensuring proper coding system conversion is the following (illustrated for EUC Japanese encoding):

(setq P (pq-connectdb "...")) (let ((file-name-coding-system 'euc-jp) (pg-coding-system 'euc-jp)) (pq-set-client-encoding "EUC_JP") ...) (pq-finish P)

Compatibility note: This function did not exist prior to libpq-7.0 and does not exist in a non-Mule XEmacs.

Function: pq-env-2-encoding

Return the integer code representing the coding system in PGCLIENTENCODING.

(pq-env-2-encoding) => 0

Compatibility note: This function did not exist prior to libpq-7.0 and does not exist in a non-Mule XEmacs.

Function: pq-clear res

Destroy a query result object by calling free() on it. res a query result object

Note: The memory allocation systems of libpq and XEmacs are different. The XEmacs representation of a query result object will have both the XEmacs version and the libpq version freed at the next garbage collection when the object is no longer being referenced. Calling this function does not release the XEmacs object, it is still subject to the usual rules for Lisp objects. The printed representation of the XEmacs object will contain the string "DEAD" after this routine is called indicating that it is no longer useful for anything.

Function: pq-conn-defaults

Return a data structure that represents the connection defaults. The data is returned as a list of lists, where each sublist contains info regarding a single option.

61.2.7 Unimplemented libpq Functions

Unimplemented Function: PGconn *PQsetdbLogin (char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName, char *login, char *pwd)

Synchronous database connection. pghost is the hostname of the PostgreSQL backend to connect to. pgport is the TCP port number to use. pgoptions specifies other backend options. pgtty specifies the debugging tty to use. dbName specifies the database name to use. login specifies the database user name. pwd specifies the database user's password.

This routine is deprecated as of libpq-7.0, and its functionality can be replaced by external Lisp code if needed.

Unimplemented Function: PGconn *PQsetdb (char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName)

Synchronous database connection. pghost is the hostname of the PostgreSQL backend to connect to. pgport is the TCP port number to use. pgoptions specifies other backend options. pgtty specifies the debugging tty to use. dbName specifies the database name to use.

This routine was deprecated in libpq-6.5.

Unimplemented Function: int PQsocket (PGconn *conn)

Return socket file descriptor to a backend database process. conn database connection object.

Unimplemented Function: void PQprint (FILE *fout, PGresult *res, PGprintOpt *ps)

Print out the results of a query to a designated C stream. fout C stream to print to res the query result object to print ps the print options structure.

This routine is deprecated as of libpq-7.0 and cannot be sensibly exported to XEmacs Lisp.

Unimplemented Function: void PQdisplayTuples (PGresult *res, FILE *fp, int fillAlign, char *fieldSep, int printHeader, int quiet)

res query result object to print fp C stream to print to fillAlign pad the fields with spaces fieldSep field separator printHeader display headers? quiet

This routine was deprecated in libpq-6.5.

Unimplemented Function: void PQprintTuples (PGresult *res, FILE *fout, int printAttName, int terseOutput, int width)

res query result object to print fout C stream to print to printAttName print attribute names terseOutput delimiter bars width width of column, if 0, use variable width

This routine was deprecated in libpq-6.5.

Unimplemented Function: int PQmblen (char *s, int encoding)

Determine length of a multibyte encoded char at *s. s encoded string encoding type of encoding

Compatibility note: This function was introduced in libpq-7.0.

Unimplemented Function: void PQtrace (PGconn *conn, FILE *debug_port)

Enable tracing on debug_port. conn database connection object. debug_port C output stream to use.

Unimplemented Function: void PQuntrace (PGconn *conn)

Disable tracing. conn database connection object.

Unimplemented Function: char *PQoidStatus (PGconn *conn)

Return the object id as a string of the last tuple inserted. conn database connection object.

Compatibility note: This function is deprecated in libpq-7.0, however it is used internally by the XEmacs binding code when linked against versions prior to 7.0.

Unimplemented Function: PGresult *PQfn (PGconn *conn, int fnid, int *result_buf, int *result_len, int result_is_int, PQArgBlock *args, int nargs)

"Fast path" interface -- not really recommended for application use conn A database connection object. fnid result_buf result_len result_is_int args nargs

The following set of very low level large object functions aren't appropriate to be exported to Lisp.

Unimplemented Function: int pq-lo-open (PGconn *conn, int lobjid, int mode)

conn a database connection object. lobjid a large object ID. mode opening modes.

Unimplemented Function: int pq-lo-close (PGconn *conn, int fd)

conn a database connection object. fd a large object file descriptor

Unimplemented Function: int pq-lo-read (PGconn *conn, int fd, char *buf, int len)

conn a database connection object. fd a large object file descriptor. buf buffer to read into. len size of buffer.

Unimplemented Function: int pq-lo-write (PGconn *conn, int fd, char *buf, size_t len)

conn a database connection object. fd a large object file descriptor. buf buffer to write from. len size of buffer.

Unimplemented Function: int pq-lo-lseek (PGconn *conn, int fd, int offset, int whence)

conn a database connection object. fd a large object file descriptor. offset whence

Unimplemented Function: int pq-lo-creat (PGconn *conn, int mode)

conn a database connection object. mode opening modes.

Unimplemented Function: int pq-lo-tell (PGconn *conn, int fd)

conn a database connection object. fd a large object file descriptor.

Unimplemented Function: int pq-lo-unlink (PGconn *conn, int lobjid)

conn a database connection object. lbojid a large object ID.

61.3 XEmacs PostgreSQL libpq Examples

This is an example of one method of establishing an asynchronous connection.

(defun database-poller (P)
  (message "%S before poll" (pq-pgconn P 'pq::status))
  (pq-connect-poll P)
  (message "%S after poll" (pq-pgconn P 'pq::status))
  (if (eq (pq-pgconn P 'pq::status) 'pg::connection-ok)
      (message "Done!")
    (add-timeout .1 'database-poller P)))
     => database-poller
(progn
  (setq P (pq-connect-start ""))
  (add-timeout .1 'database-poller P))
     => pg::connection-started before poll
     => pg::connection-made after poll
     => pg::connection-made before poll
     => pg::connection-awaiting-response after poll
     => pg::connection-awaiting-response before poll
     => pg::connection-auth-ok after poll
     => pg::connection-auth-ok before poll
     => pg::connection-setenv after poll
     => pg::connection-setenv before poll
     => pg::connection-ok after poll
     => Done!
P
     => #<PGconn localhost:25432 steve/steve>

Here is an example of one method of doing an asynchronous reset.

(defun database-poller (P)
  (let (PS)
    (message "%S before poll" (pq-pgconn P 'pq::status))
    (setq PS (pq-reset-poll P))
    (message "%S after poll [%S]" (pq-pgconn P 'pq::status) PS)
    (if (eq (pq-pgconn P 'pq::status) 'pg::connection-ok)
	(message "Done!")
      (add-timeout .1 'database-poller P))))
     => database-poller
(progn
  (pq-reset-start P)
  (add-timeout .1 'database-poller P))
     => pg::connection-started before poll
     => pg::connection-made after poll [pgres::polling-writing]
     => pg::connection-made before poll
     => pg::connection-awaiting-response after poll [pgres::polling-reading]
     => pg::connection-awaiting-response before poll
     => pg::connection-setenv after poll [pgres::polling-reading]
     => pg::connection-setenv before poll
     => pg::connection-ok after poll [pgres::polling-ok]
     => Done!
P
     => #<PGconn localhost:25432 steve/steve>

And finally, an asynchronous query.

(defun database-poller (P)
  (let (R)
    (pq-consume-input P)
    (if (pq-is-busy P)
	(add-timeout .1 'database-poller P)
      (setq R (pq-get-result P))
      (if R
	  (progn
	    (push R result-list)
	    (add-timeout .1 'database-poller P))))))
     => database-poller
(when (pq-send-query P "SELECT * FROM xemacs_test;")
  (setq result-list nil)
  (add-timeout .1 'database-poller P))
     => 885
;; wait a moment
result-list
     => (#<PGresult PGRES_TUPLES_OK - SELECT>)

Here is an example showing how multiple SQL statements in a single query can have all their results collected.

;; Using the same database-poller function from the previous example
(when (pq-send-query P "SELECT * FROM xemacs_test;
SELECT * FROM pg_database;
SELECT * FROM pg_user;")
  (setq result-list nil)
  (add-timeout .1 'database-poller P))
     => 1782
;; wait a moment
result-list
     => (#<PGresult PGRES_TUPLES_OK - SELECT> #<PGresult PGRES_TUPLES_OK - SELECT> #<PGresult PGRES_TUPLES_OK - SELECT>)

Here is an example which illustrates collecting all data from a query, including the field names.

(defun pg-util-query-results (results)
  "Retrieve results of last SQL query into a list structure."
  (let ((i (1- (pq-ntuples R)))
	j l1 l2)
    (while (>= i 0)
      (setq j (1- (pq-nfields R)))
      (setq l2 nil)
      (while (>= j 0)
	(push (pq-get-value R i j) l2)
	(decf j))
      (push l2 l1)
      (decf i))
    (setq j (1- (pq-nfields R)))
    (setq l2 nil)
    (while (>= j 0)
      (push (pq-fname R j) l2)
      (decf j))
    (push l2 l1)
    l1))
     => pg-util-query-results
(setq R (pq-exec P "SELECT * FROM xemacs_test ORDER BY field2 DESC;"))
     => #<PGresult PGRES_TUPLES_OK - SELECT>
(pg-util-query-results R)
     => (("f1" "field2") ("a" "97") ("b" "97") ("stuff" "42") ("a string" "12") ("foo" "10") ("string" "2") ("text" "1"))

Here is an example of a query that uses a database cursor.

(let (data R)
  (setq R (pq-exec P "BEGIN;"))
  (setq R (pq-exec P "DECLARE k_cursor CURSOR FOR SELECT * FROM xemacs_test ORDER BY f1 DESC;"))

  (setq R (pq-exec P "FETCH k_cursor;"))
  (while (eq (pq-ntuples R) 1)
    (push (list (pq-get-value R 0 0) (pq-get-value R 0 1)) data)
    (setq R (pq-exec P "FETCH k_cursor;")))
  (setq R (pq-exec P "END;"))
  data)
     => (("a" "97") ("a string" "12") ("b" "97") ("foo" "10") ("string" "2") ("stuff" "42") ("text" "1"))

Here's another example of cursors, this time with a Lisp macro to implement a mapping function over a table.

(defmacro map-db (P table condition callout)
  `(let (R)
     (pq-exec ,P "BEGIN;")
     (pq-exec ,P (concat "DECLARE k_cursor CURSOR FOR SELECT * FROM "
			 ,table
			 " "
			 ,condition
			 " ORDER BY f1 DESC;"))
     (setq R (pq-exec P "FETCH k_cursor;"))
     (while (eq (pq-ntuples R) 1)
       (,callout (pq-get-value R 0 0) (pq-get-value R 0 1))
       (setq R (pq-exec P "FETCH k_cursor;")))
     (pq-exec P "END;")))
     => map-db
(defun callback (arg1 arg2)
  (message "arg1 = %s, arg2 = %s" arg1 arg2))
     => callback
(map-db P "xemacs_test" "WHERE field2 > 10" callback)
     => arg1 = stuff, arg2 = 42
     => arg1 = b, arg2 = 97
     => arg1 = a string, arg2 = 12
     => arg1 = a, arg2 = 97
     => #<PGresult PGRES_COMMAND_OK - COMMIT>