Postgresql

Author

Original author: Johannes Grødem

Please do not mail to Johannes directly as he no longer develops this egg.

Current maintainer: Peter Bex

Thanks to

Felix L. Winkelmann
Alex Shinn
Ed Watkeys
Taylor Campbell

Repository

https://code.more-magic.net/chicken-postgresql

Requirements

sql-null

You will also need to have libpq installed, including development headers.

Documentation

This extension provides an interface to the PostgreSQL relational database.

Connection management

[procedure] (connect [CONNECTION-SPEC [TYPE-PARSERS [TYPE-UNPARSERS [NOTIFY-HANDLER]]]])

Opens a connection to the database given in CONNECTION-SPEC, which should be either a PostgreSQL connection string or an alist with entries consisting of a symbol and a value. The symbols should be connection keywords recognized by PostgreSQL's connection function. See the list of PQconnectdbParams parameter keywords in the PostgreSQL documentation. At the time of writing, they are host, hostaddr, port, dbname, user, password, connect_timeout, options, sslmode, service.

If any parameter is not specified, or the CONNECTION-SPEC was not supplied at all, the regular libpq rules apply. That means it falls back to checking various environment variables and settings from pg_service.conf, as well as passwords from pgpass. This is to be preferred over hardcoding connection settings in your code.

Using an alist for CONNECTION-SPEC is recommended; when available (when using libpq from Postgres 9.0 or later), PQconnectStartParams will be used. This prevents parsing errors when keys or values contain "special" characters like equals signs or single quotes. This also adds a layer of security for when connection specifier components come from an untrusted source.

TYPE-PARSERS is an optional alist that maps PostgreSQL type names to parser procedures, TYPE-UNPARSERS is an optional alist that maps predicates to unparser procedures. They default to (default-type-parsers) and (default-type-unparsers), respectively (see below).

NOTIFY-HANDLER is an optional procedure which should accept three arguments: A channel name (string), the triggering backend's PID (integer) and the payload (string). It is invoked whenever there are asynchronous notifications available through LISTEN. It defaults to (default-notify-handler) (see below). It may also be #f to disable handling of notifications.

The return value is a connection-object.

Important: You cannot use the same connection from multiple threads. If you need to talk to the same server from different threads, simply create a second connection.

Also note that while these bindings use the non-blocking interface to connect to PostgreSQL, if you specify a hostname (using the host-keyword), the function might not be able to yield because the resolver will block. If you don't want this, you'd have to call some kind of custom asynchronous resolver.

[procedure] (disconnect CONNECTION)

Disconnects from the given CONNECTION.

[procedure] (reset-connection CONNECTION)

Resets, that is, reopens the connection with the same connection-specs as was given when opening the original CONNECTION.

[procedure] (type-parsers CONNECTION)

Retrieve the alist of type parsers associated with the CONNECTION.

[procedure] (type-unparsers CONNECTION)

Retrieve the alist of type unparsers associated with the CONNECTION.

[procedure] (connection? OBJECT)

Returns true if OBJECT is a PostgreSQL connection-object.

[procedure] (connected? CONNECTION)

Returns true if the CONNECTION represents an open connection.

Query procedures

[procedure] (query CONN QUERY . PARAMS)

Execute QUERY on connection CONN and return a result object. The result object can be read out by several procedures, ranging from the low-level value-at to the high-level row-fold. See the High-level API and Low-level result API sections below for information on how to read out result objects.

QUERY can either be a string or a symbol. If it is a string, it should contain an SQL query to execute. This query can contain placeholders like $1, $2 etc, which refer to positional arguments in PARAMS. For example:

(import postgresql)

(let ((conn (connect '((dbname . test)))))
  (row-values (query conn "SELECT $1::text, 2::int2" "hello")))
 => ("hello" 2)

If QUERY is a symbol, it must match the name of a prepared statement you created earlier. The number of parameters passed as PARAMS must match the number of placeholders used when the statement was prepared.

To actually create a prepared statement, you can simply use the query procedure with an SQL PREPARE statement. The placeholders in that statement are deferred until execute time. It allows no parameters to be sent at preparation time, which is a limitation in the PostgreSQL protocol itself. You could use escape-string if you really must pass in dynamic values when preparing the statement.

(import postgresql)

(let ((conn (connect '((dbname . test)))))
  ;; Can't pass any arguments here:
  (query conn "PREPARE mystmt (text, int) AS SELECT $1, $2")
  ;; They are deferred until statement execution time:
  (row-values (query conn 'mystmt "hi" 42)))
 => ("hi" 42)  ; a list of all values at row 0 (see "row-values" below)

(let ((conn (connect '((dbname . test)))))
  ;; If we absolutely need dynamic values, we can escape them manually:
  (query conn (sprintf "PREPARE mystmt (text) AS SELECT $1, '~A', 'bar'"
		       (escape-string conn "foo")))
  (row-values (query conn 'mystmt "hi")))
 => ("hi" "foo" "bar")

As you can see from the examples above, PostgreSQL types are automatically converted to corresponding Scheme types. This can be extended to support your own user-defined types, see the section about type-conversion below for more information on how to do that.

[procedure] (query* CONN QUERY [PARAMS] [format: FORMAT] [raw: RAW?])

A less convenient but slightly more powerful version of the query procedure; PARAMS must now be a list (instead of rest-arguments). FORMAT is a symbol specifying how to return the resultset: either as binary or text (the default). RAW is a boolean which defines whether the PARAMS should be treated "raw" or first passed through the unparsers associated with CONN. If they are treated "raw", they must all be strings, blobs or sql-null objects.

See type conversion for more info on unparsers.

(import postgresql (chicken blob))

(let ((conn (connect '((dbname . test)))))
  (row-map*
    (lambda (a b) (list (blob->string a) (blob->u8vector b)))
    (query* conn "SELECT $1::text, 2::int2" '("hello") format: 'binary)))
 => (("hello" #u8(0 2)))

High-level API

Usually you will use only these procedures to process result sets, but you can fall back to (or even mix with) the low-level API if you need to do custom things.

[procedure] (row-fold KONS KNIL RESULT)
[procedure] (row-fold* KONS KNIL RESULT)

This is the fundamental result set iterator. It calls (kons row seed) for every row, where row is the list of values in the current row and seed is the accumulated result from previous calls (initially knil), ie its pattern looks like (KONS ROWN ... (KONS ROW2 (KONS ROW1 KNIL))). It returns the final accumulated result.

The starred version works the same, except it calls (kons rowN-col1 rowN-col2 ... seed) instead of (kons rowN seed), so the procedure must know how many columns you have in the result set.

(import postgresql)

(let ((conn (connect '((dbname . test)))))
   (row-fold (lambda (row sum) (+ (car row) sum))
             0
             (query conn "SELECT 1 UNION SELECT 2")))
 => 3

(let ((conn (connect '((dbname . test)))))
   (row-fold* (lambda (value str) (string-append str value))
              ""
              (query conn "SELECT 'hello, ' UNION SELECT 'world'")))
 => "hello, world"

[procedure] (column-fold KONS KNIL RESULT)
[procedure] (column-fold* KONS KNIL RESULT)

As row-fold/row-fold*, except this iterates sideways through the columns instead of lengthways through the columns, calling KONS with all values in all the rows of the current column, from left to right.

The starred version is much less useful here since you often don't know the number of returned columns, but it is provided for consistency.

(import postgresql)

(let ((conn (connect '((dbname . test)))))
   (column-fold (lambda (col sum) (+ (car col) sum))
                0
                (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
 => 101

[procedure] (row-fold-right KONS KNIL RESULT)
[procedure] (row-fold-right* KONS KNIL RESULT)

The fundamental result set recursion operator; Calls (KONS COL1 (KONS COL2 (KONS ... KNIL))) instead of (KONS COLN ... (KONS COL2 (KONS COL1 KNIL))).

[procedure] (column-fold-right KONS KNIL RESULT)
[procedure] (column-fold-right* KONS KNIL RESULT)

Column variants of row-fold-right/row-fold-right*.

[procedure] (row-for-each PROC RESULT)
[procedure] (row-for-each* PROC RESULT)

Simple for-each, calling the (PROC row) on each row, in turn, only for the purpose of its side-effects. The starred version calls (PROC col1 col2 ...).

(import postgresql)

(let ((conn (connect '((dbname . test)))))
   (row-for-each write (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
; prints:  (1 100) (2 200)

[procedure] (column-for-each PROC RESULT)
[procedure] (column-for-each* PROC RESULT)

Column variants of row-for-each/row-for-each*.

[procedure] (row-map PROC RESULT)
[procedure] (row-map* PROC RESULT)

Maps rows to lists by applying PROC to every row and using its result in the result list on the position corresponding to that of the row. This procedure is not guaranteed to walk the result set in any particular order, so do not rely on the order PROC will be called.

(import postgresql)

(let ((conn (connect '((dbname . test)))))
   (row-map* + (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
 => (101 202)

[procedure] (column-map PROC RESULT)
[procedure] (column-map* PROC RESULT)

Column variants of row-map/row-map*.

(import postgresql)

(let ((conn (connect '((dbname . test)))))
   (column-map* + (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
 => (3 300)

Transaction management

[procedure] (with-transaction CONN THUNK [isolation: LEVEL] [access: MODE])

Execute THUNK within a BEGIN TRANSACTION block, and return the value of thunk.

The transaction is committed if thunk returns a true value. If an exception occurs during thunk, or thunk returns #f, or the commit fails, the transaction will be rolled back. If this rollback fails, that is a critical error and you should likely abort.

Nested applications of with-transaction are supported -- only those statements executed within THUNK are committed or rolled back by any with-transaction call, as you would expect.

However, escaping or re-entering the dynamic extent of thunk will not commit or rollback the in-progress transaction, so it is highly discouraged to jump out of a transaction. You will definitely run into trouble, unless you can ensure that no other statements will be executed on this connection until the outermost with-transaction returns normally.

If you provide LEVEL (which can be the symbol read-committed or serializable) this will set the transaction isolation mode for the transaction. If you provide MODE (which can be the symbol read/write or read-only) this will set the access mode for the transaction.

LEVEL is only allowed in the outermost transaction (when in-transaction? returns #f); if you provide it in an inner transaction, an error is raised. In subtransactions, MODE can only be "downgraded" to read-only from inside a read/write transaction, but you can't "upgrade" to read/write from a read-only transaction.

[procedure] (in-transaction? CONN)

Returns #t if there is currently a transaction in progress on the connection CONN. Returns #f if no transaction is in progress.

Low-level result API

This API allows you to inspect result objects on the individual row and column level.

[procedure] (result? OBJ)

Returns #t when OBJ is a result object, #f otherwise.

[procedure] (clear-result! RES)

Directly clean up all memory used by the result object. This is normally deferred until garbage collection, but it's made available for when you want more control over when results are released.

[procedure] (value-at RES [COLUMN [ROW]] [raw: RAW])

Returns the value at the specified COLUMN and ROW. It is parsed by an appropriate parser unless RAW is specified and #t. If RAW is true, the value is either a string, blob or an sql-null object. Otherwise, it depends on the parsers.

If ROW or COLUMN are not specified, they default to zero. This makes for more convenient syntax if you're just reading out a result of a query which always has one row or even one value.

See type conversion for more info on parsers.

[procedure] (row-values RES [ROW] [raw: RAW])

Returns a list of all the columns' values at the given ROW number. If ROW is omitted, it defaults to zero. If RAW is true, the values are either strings, blobs or sql-null objects. Otherwise, it depends on the parsers.

[procedure] (column-values RES [COLUMN] [raw: RAW])

Returns a list of all the rows' values at the given COLUMN number. If COLUMN is omitted, it defaults to zero. If RAW is true, the values are either strings, blobs or sql-null objects. Otherwise, it depends on the parsers.

[procedure] (row-alist RES [ROW] [raw: RAW])

Returns an alist of the values at the given ROW number. The keys of the alist are made up by the matching column names, as symbols.

If ROW is omitted, it defaults to zero. If RAW is true, the values are either strings, blobs or sql-null objects. Otherwise, it depends on the parsers.

[procedure] (affected-rows RES)

For INSERT or UPDATE statements, this returns the number of rows affected by the statement that RES is a result for. Otherwise it's zero.

[procedure] (inserted-oid RES)

For INSERT statements resulting in a single record being inserted, this returns the OID (a number) assigned to the newly inserted row. Returns #f for non-INSERT or multi-row INSERTs, or INSERTs into tables without OIDs.

[procedure] (row-count RES)

Returns the number of rows in the result set.

[procedure] (column-count RES)

Returns the number of columns in the result set.

[procedure] (column-index RES COLUMN)

Returns the index of COLUMN in the result set. COLUMN should be a symbol indicating the column name.

[procedure] (column-name RES INDEX)

Returns the name of the column (a symbol) at the position in the result set specified by INDEX. This is its aliased name in the result set.

[procedure] (column-names RES)

Returns a list of all the column names (symbols) in the result set. The position in the list reflects the position of the column in the result set.

[procedure] (column-format RES INDEX)

Returns the format of the column at INDEX, which is a symbol; Either text or binary. This determines whether the value returned by value-at will be a string or a blob.

[procedure] (column-type RES INDEX)

Returns the OID (an integer) of the column at INDEX.

[procedure] (column-type-modifier RES INDEX)

Returns an type-specific modifier (a number), or #f if the type has no modifier.

[procedure] (table-oid RES INDEX)

The OID (a number) of the table from whence the result column at INDEX originated, or #f if the column is not a simple reference to a table column.

[procedure] (table-column-index RES INDEX)

Returns the column number (within its table) of the column making up the query result column at the position specified by INDEX.

Note: This procedure returns indexes starting at zero, as one would expect. However, the underlying C function PQftablecol is one-based. This might trip up experienced Postgres hackers.

Value escaping

To embed arbitrary values in query strings, you must escape them first, to protect yourself from SQL injection bugs. This is not required if you use positional arguments (the PARAMS arguments in the query procedures).

[procedure] (escape-string CONNECTION STRING)

Escapes special characters in STRING which are otherwise interpreted by the SQL parser, obeying the CONNECTION's encoding and escaping settings, using the escaping syntax for string contexts. This does NOT add surrounding quotes to the string; that's up to you to add.

Example:

;; This prevents people from changing a query's parse tree.
;; For example, they could try to turn a query like
;; SELECT * FROM USERS WHERE id='x'
;;    into
;; SELECT * FROM USERS WHERE id='1' OR '1'='1'
;; by quoting the value for X, you get the intended parse tree:
;; SELECT * FROM USERS WHERE id='1''' OR ''1''=''1'
(escape-string conn "1' OR '1'='1") => "1'' OR ''1''=''1"

;; Depending on the value of standard_conforming_strings you might also get
(escape-string conn "1' OR '1'='1") => "1\\' OR \\'1\\'=\\'1"

;; Of course, when using these strings you still need to surround
;; the output of escape-string with single quotes

[procedure] (quote-identifier CONNECTION STRING)

Escapes special characters in STRING which are otherwise interpreted by the SQL parser, obeying the CONNECTION's encoding settings and escaping settings, using the escaping syntax for identifier context. Identifiers are table names, aliases etc. Surrounding double quotes will be added.

This procedure corresponds to PQescapeIdentifier, but the name was changed to reflect the fact that it performs escaping and adds quotation marks around the string.

NOTE: This procedure is only available when the egg is built against the libpq from PostgreSQL 9.0 or later. If you are using an older version, this will raise a (exn postgresql unsupported-version) error with an upgrade message.

Example:

;; Spaces are normally not allowed in table names, but when you
;; quote them, they are allowed
(quote-identifier conn "a table with spaces") => "\"a table with spaces\""

;; Can't use a column or table called order because it is a
;; reserved word. However, escaping it makes it usable.
(quote-identifier conn "order") => "\"order\""

;; Table names are case-insensitive and always implicitly downcased.
;; If you need to access a table with a capital in its name,
;; quoting the table also helps:
(quote-identifier conn "Foo") => "\"Foo\""

[procedure] (escape-bytea CONNECTION OBJ)

Quotes special characters in OBJ (a string, blob or srfi-4 vector), which would otherwise be interpreted by the SQL parser. This differs from escape-string in that some bytes are doubly encoded so they can be used for bytea columns.

This is required because of a technicality; PostgreSQL first parses the string value as a string, and then casts this string to bytea, interpreting another layer of escape codes.

For example, E'a\\000bcd' is first converted to 'a\000bcd' by the text parser, and then interpreted by the bytea parser as an "a" followed by a NUL byte, followed by "bcd". In Scheme, the value returned by (escape-bytea conn "a\x00bcd") is "a\\\\000bcd". Yes, that's a lot of backslashes :)

[procedure] (unescape-bytea STRING)

This unescapes a bytea result from the server. It is not the inverse of escape-bytea, because string values returned by the server are not escaped for the text-parser. (ie, step one in the encoding process described under escape-bytea is skipped)

The result type of unescape-bytea is a u8vector (in earlier versions, this was a string).

COPY support

High-level COPY API

This API is experimental and as such should be expected to change. If you have suggestions on how to improve the API, please let me know!

[procedure] (copy-query-fold KONS KNIL CONNECTION QUERY . PARAMS)
[procedure] (copy-query*-fold KONS KNIL CONNECTION QUERY [PARAMS] [format: FORMAT] [raw: RAW?])

This is the fundamental COPY TO STDOUT iterator. It calls (kons data seed) for every row of COPY data returned by QUERY, where data is either a string or a blob depending on whether the COPY query asked for binary or text data and seed is the accumulated result from previous calls (initially knil), ie its pattern looks like (KONS DATAN ... (KONS DATA2 (KONS DATA1 KNIL))). It returns the final accumulated result.