postgresql
Note: This is taken from the Chicken Wiki, where a more recent version could be available.
Simple bindings for PostgreSQL's C-api.
Original author: Johannes Grødem
Please do not mail to Johannes directly as he no longer develops this egg.
Current maintainer: Peter Bex
This extension provides an interface to the PostgreSQL relational database.
<procedure>(pg:connect CONNECTION-SPEC)</procedure>
Opens a connection to the database given in CONNECTION-SPEC, which should be 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 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)</procedure>
Closes the given CONNECTION.
<procedure>(pg:reset CONNECTION)</procedure>
Resets, that is, reopens the connection with the same connection-specs as was given when opening the original CONNECTION.
<procedure>(pg:connection? OBJECT)</procedure>
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)</procedure>
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)</procedure>
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)</procedure>
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)</procedure>
Returns true if OBJECT is an SQL NULL-value. Typically used to check if an element in a tuple is NULL.
<procedure>(pg:escape-string [CONNECTION] STRING)</procedure>
Quotes special characters in STRING which are otherwise interpreted by the SQL parser. The CONNECTION is optional, but recommended. Not specifying the connection is deprecated. It results in a call to PQescapeString, which is not guaranteed to give a correct response. Specifying the connection results in a call to PQescapeStringConn, which checks the server's character encoding and other settings.
In future versions of this library, not specifying the connection will be an error.
<constant>(sql-null-object)</constant>
Represents SQL NULL values.
condition: postgresql
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.
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 |
|---|---|
TEXT | string |
BYTEA | string |
CHAR | char |
BPCHAR | char |
BOOL | boolean |
INT8 | fixnum or inexact<sup>1</sup> |
INT4 | fixnum or inexact<sup>1</sup> |
INT2 | fixnum |
FLOAT8 | inexact |
FLOAT4 | inexact |
ABSTIME | unsupported<sup>2</sup> |
RELTIME | unsupported<sup>2</sup> |
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) |
INTERVAL | unsupported2 |
NUMERIC | fixnum or inexact1 |
OID | fixnum or inexact1 |
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))</expr> </example>
Copyright (C) 2004 Johannes Grødem <johs@copyleft.no> 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.