You can edit this page using wiki syntax for markup.

Article contents:== Outdated egg! This is an egg for CHICKEN 3, the unsupported old release. You're almost certainly looking for [[/eggref/4/postgresql|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 [[https://wiki.call-cc.org/chicken-projects/egg-index-4.html|egg index]]. Otherwise, please consider porting this egg to the current version of CHICKEN. [[tags: egg]] == postgresql [[toc:]] === Description Simple bindings for [[http://www.postgresql.org/|PostgreSQL]]'s C-api. === Author Original author: [[mailto:johs@copyleft.no|Johannes Grødem]] Please do not mail to Johannes directly as he no longer develops this egg. Current maintainer: [[/users/peter-bex|Peter Bex]] ==== Thanks to * Felix L. Winkelmann * Alex Shinn * Ed Watkeys * Taylor Campbell === Requirements * [[easyffi]] * [[syntax-case]] === Download [[http://code.call-cc.org/legacy-eggs/3/postgresql.egg|postgresql.egg]] === Documentation This extension provides an interface to the PostgreSQL relational database. ==== Connection functions <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. ==== 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|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 [[http://srfi.schemers.org/srfi-44/|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}}. ==== Query functions <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. ==== Constants <constant>(sql-null-object)</constant> 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 [[http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html|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: <table> <tr> <th>PostgreSQL type</th> <th>Scheme type</th> </tr> <tr><td>TEXT</td><td>string</td></tr> <tr><td>BYTEA</td><td>string</td></tr> <tr><td>CHAR</td><td>char</td></tr> <tr><td>BPCHAR</td><td>char</td></tr> <tr><td>BOOL</td><td>boolean</td></tr> <tr><td>INT8</td><td>fixnum or inexact<nowiki>¹</nowiki></td></tr> <tr><td>INT4</td><td>fixnum or inexact<nowiki>¹</nowiki></td></tr> <tr><td>INT2</td><td>fixnum</td></tr> <tr><td>FLOAT8</td><td>inexact</td></tr> <tr><td>FLOAT4</td><td>inexact</td></tr> <tr><td>ABSTIME</td><td>unsupported<nowiki>²</nowiki></td></tr> <tr><td>RELTIME</td><td>unsupported<nowiki>²</nowiki></td></tr> <tr><td>DATE</td><td>vector {{#(year month date)}}</td></tr> <tr><td>TIME</td><td>vector {{#(hour minute second microsecond)}}</td></tr> <tr> <td>TIMESTAMP</td> <td>vector {{#(year month date hour minute second microsecond)}}</td> </tr> <tr> <td>TIMESTAMPTZ</td> <td>vector {{#(year month date hour minute second microsecond timezone)}}</td> </tr> <tr><td>INTERVAL</td><td>unsupported<nowiki>²</nowiki></td></tr> <tr><td>NUMERIC</td><td>fixnum or inexact<nowiki>¹</nowiki></td></tr> <tr><td>OID</td><td>fixnum or inexact<nowiki>¹</nowiki></td></tr> </table> 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 <enscript highlight="scheme"> (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)) </enscript> === Changelog * trunk - Add PQescapeStringConn functionality to pg:escape-string. Fixed weird compilation issue with SRFI-69 by removing it from USES list. * 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. === 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. Description of your changes:

I would like to authenticate

Spam control

What do you get when you add 1 to 2?