Outdated egg!

This is an egg for CHICKEN 3, the unsupported old release. You're almost certainly looking for the CHICKEN 4 version of this egg, if it exists.

If it does not exist, there may be equivalent functionality provided by another egg; have a look at the egg index. Otherwise, please consider porting this egg to the current version of CHICKEN.

postgresql
  1. Outdated egg!
  2. postgresql
    1. Description
    2. Author
      1. Thanks to
    3. Requirements
    4. Download
    5. Documentation
      1. Connection functions
      2. Query functions
      3. Query functions
      4. Constants
      5. Error handling
      6. Conversion
    6. Example
    7. Changelog
    8. License

Description

Simple bindings for PostgreSQL's C-api.

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

Requirements

Download

postgresql.egg

Documentation

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 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)

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.

Query functions

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 [CONNECTION] STRING)

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.

Constants

[constant] (sql-null-object)

Represents SQL NULL values.

Error handling

 
 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.

severity
One of the symbols error, fatal, panic, warning, notice, debug, info, log. Always present.
error-class
a symbol representating a Postgresql error class.
error-code
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.
message-detail
A secondary (to the usual exn message property) message with extra detail about the problem.
message-hint
A string with a suggestion about what to do about the problem.
statement-position
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.
context
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.
source-file
The file name of the Postgresql source-code location where the error was reported.
source-line
A string containing the line number of the Postgresql source-code location where the error was reported.
source-function
The name of the source-code function reporting the error.

Conversion

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 1
INT4 fixnum or inexact 1
INT2 fixnum
FLOAT8 inexact
FLOAT4 inexact
ABSTIME unsupported 2
RELTIME unsupported 2
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 unsupported 2
NUMERIC fixnum or inexact 1
OID fixnum or inexact 1

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

 
(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))

Changelog

License

 
 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.