You are looking at historical revision 9553 of this page. It may differ significantly from its current revision.
- Felix L. Winkelmann
- Alex Shinn
- Ed Watkeys
- Taylor Campbell
This extension provides an interface to the PostgreSQL relational database.
Connection functions[procedure] (pg:connect CONNECTION-SPEC)
Opens a connection to the database given in CONNECTION-SPEC, which should be a list of conses each consisting of a symbol and a value. The symbols should be parameter keywords recognized by PostgreSQL's connection function. See the PostgreSQL documentation for these. At the time of writing, they are host, hostaddr, port, dbname, user, password, connect_timeout, options, sslmode, service.
The return value is a connection-object.
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.[procedure] (pg:close CONNECTION)
Closes the given CONNECTION.[procedure] (pg:reset CONNECTION)
Resets, that is, reopens the connection with the same connection-specs as was given when opening the original CONNECTION.[procedure] (pg:connection? OBJECT)
Returns true if OBJECT is a PostgreSQL connection-object.
For each of the query functions, the query string is a string of one or more queries to PostgreSQL. If more than one query is given (separated by semicolons), the tuples are provided as if a single query has been executed. This means that the tuples you get will possibly not be of the same length. Tuples are given as vectors.
The values in the tuples are converted to a suitable Scheme representation, if it is supported. See Conversion.
For queries that don't return tuples, but which return the number of tuples affected instead, this number is given instead of a vector.
Remember that your QUERY-string might need to be escaped.[procedure] (pg:query-fold-left QUERY CONNECTION FOLD-FUNCTION . SEEDS)
Run FOLD-FUNCTION on each tuple or tuple count returned by the query (or queries) specified by QUERY until all tuples or tuple counts have been read, in left to right order, or until the FOLD-FUNCTION returns #f as its first return value. (See the source code for pg:query-tuples for an example of how to use this function, and also SRFI-44.)[procedure] (pg:query-for-each PROC QUERY CONNECTION)
Runs the QUERY on PostgreSQL CONNECTION, and then maps PROC over each tuple returned by the query, if any. The procedure should take one parameter, in which it is given a tuple. Returns nothing.
[procedure] (pg:query-tuples QUERY CONNECTION)
Returns a list of tuples produced by the database on CONNECTION as a reply to QUERY. See also pg:query-for-each.[procedure] (pg:sql-null-object? OBJECT)
Returns true if OBJECT is an SQL NULL-value. Typically used to check if an element in a tuple is NULL.
Query functions[procedure] (pg:escape-string STRING)
Quotes special characters in STRING which are otherwise interpreted by the SQL parser.
Represents SQL NULL values.
A condition of kind (exn postgresql) is signaled when an error occurs. The postgresql component of this condition contains several properties. Unless otherwise noted, these properties may not be present, in which case they have the value #f.
- One of the symbols error, fatal, panic, warning, notice, debug, info, log. Always present.
- a symbol representating a Postgresql error class.
- A symbol representing a Postgresql error code. See the Postgresql documentation for a description of error codes and error classes. They are mapped in an obvious way to Scheme symbols. See source for details.
- A secondary (to the usual exn message property) message with extra detail about the problem.
- A string with a suggestion about what to do about the problem.
- An integer indicating an error cursor position as an index into the original statement string. The first character has index 1, and positions are measured in characters, not bytes.
- An indication of the context in which the error occurred. Presently this includes a call stack traceback of active PL functions. The trace is one entry per line, most recent first.
- The file name of the Postgresql source-code location where the error was reported.
- A string containing the line number of the Postgresql source-code location where the error was reported.
- The name of the source-code function reporting the error.
Type information is read from the database the first time you connect to it. Note that ISO-style dates are assumed, so please set PostgreSQL up to use this. Here is an overview of how the mapping is done currently:
|PostgreSQL type||Scheme type|
|INT8||fixnum or inexact1|
|INT4||fixnum or inexact1|
|DATE||vector #(year month date)|
|TIME||vector #(hour minute second microsecond)|
|TIMESTAMP||vector #(year month date hour minute second microsecond)|
|TIMESTAMPTZ||vector #(year month date hour minute second microsecond timezone)|
|NUMERIC||fixnum or inexact
|OID||fixnum or inexact
1. This means you will get a fixnum if the number is small enough, and a floating point number otherwise. If it's too large to be represented as a floating point number, an error is signaled. 2. These are just returned as text for now.
<example> <expr> (let ([conn (pg:connect '((dbname . "johs")))])
(pg:query-for-each (lambda (tuple) (do [(i 0 (+ i 1))] [(= i (vector-length tuple))] (let ([element (vector-ref tuple i)]) (format #t "~15S" (if (pg:sql-null-object? element) "NULL" element)))) (newline)) "SELECT * FROM foo LIMIT 5" conn) (pg:close conn))
- 2.0.12 - added pg:named-tuples parameter [Contributed by Graham Fawcett]
- 2.0.11 - added syntax-case requirements to .meta file [Thanks to Michele Simionato]
- 2.0.10 - adapted to new easyffi usage [Thanks to rreal]
- 2.0.9 - Yet more improvements to error reporting
- 2.0.8 - More detailed error information, export pg:sql-null-object
- 2.0.7 - Added missing error-function [Thanks to Reed Sheridan]
- 2.0.6 - Removed dependency on format [Thanks to Reed Sheridan]
- 2.0.5 - Some bugfixes and pq:escape-string by Reed Sheridan; adapted to SRFI-69 hash-tables
- 2.0.4 - Changed usage of hygienic macros in setup script
- 2.0.3 - Bugfixes.
- 2.0.0 - Interface improvements. (Backward-incompatible.)
- 1.2.1 - Non-blocking queries.
- 1.2.0 - Optimizations, minor fixes and cleanups.
Copyright (C) 2004 Johannes Grødem <firstname.lastname@example.org> Redistribution and use in source and binary forms, with or without modification, is permitted. THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.