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 SXEmacs 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.

pq::getssl

The SSL session of the database connection, nil for none.

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 sxemacs_codenames;"))
     ⇒ #<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 sxemacs_codenames;"))
  (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 sxemacs_codenames;"))
     ⇒ #<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 sxemacs-codenames;"))
     ⇒ <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 sxemacs_codenames;"))
     ⇒ #<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 sxemacs_codenames;"))
     ⇒ #<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 sxemacs_codenames;"))
     ⇒ #<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 sxemacs_codenames;"))
  (setq i (pq-nfields R))
  (while (>= (decf i) 0)
    (push (pq-fname R i) l))
  l)
     ⇒ ("id" "version" "codename")

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 sxemacs_codenames;"))
     ⇒ #<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 sxemacs_codenames;"))
  (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 sxemacs_codenames;"))
     ⇒ #<PGresult PGRES_TUPLES_OK[5] - SELECT>
(pq-get-value R 0 1)
     ⇒ "22.1.0"
(pq-get-value R 1 1)
     ⇒ "22.1.1"
(pq-get-value R 1 2)
     ⇒ "Aston Martin"

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 sxemacs_codenames;"))
     ⇒ #<PGresult PGRES_TUPLES_OK[5] - SELECT>
(pq-get-length R 0 1)
     ⇒ 6
(pq-get-length R 1 1)
     ⇒ 6
(pq-get-length R 1 2)
     ⇒ 12

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 sxemacs_codenames
                   VALUES (6, '22.1.5', 'Bugatto');"))
     ⇒ #<PGresult PGRES_COMMAND_OK[1] - INSERT 542086 1>
(pq-cmd-status R)
     ⇒ "INSERT 2069411 1"
(setq R (pq-exec P "UPDATE sxemacs_codenames SET codename='Bugatti'
                    WHERE version='22.1.5';"))
     ⇒ #<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 sxemacs_codenames VALUES
                    (7, '22.1.6', 'Cadillac');"))
     ⇒ #<PGresult PGRES_COMMAND_OK[1] - INSERT 38688 1>
(pq-cmd-tuples R)
     ⇒ "1"
(setq R (pq-exec P "SELECT * from sxemacs_codenames;"))
     ⇒ #<PGresult PGRES_TUPLES_OK[7] - SELECT>
(pq-cmd-tuples R)
     ⇒ ""
(setq R (pq-exec P "DELETE FROM sxemacs_codenames
                    WHERE codename LIKE '%urbo';"))
     ⇒ #<PGresult PGRES_COMMAND_OK[1] - DELETE 1>
(pq-cmd-tuples R)
     ⇒ "1"

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 sxemacs_codenames VALUES
                    (8, '22.1.7', 'Celica');"))
     ⇒ #<PGresult PGRES_COMMAND_OK[1] - INSERT 542089 1>
(pq-oid-value R)
     ⇒ 542089
(setq R (pq-exec P "SELECT version FROM sxemacs_codenames
                    WHERE codename='Bugatti';"))
     ⇒ #<PGresult PGRES_TUPLES_OK[1] - 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.