source: project/wiki/postgresql @ 10452

Last change on this file since 10452 was 10452, checked in by sjamaan, 12 years ago

Mark me as maintainer of postgresql

File size: 9.2 KB
Line 
1[[tags: egg]]
2
3== postgresql
4
5[[toc:]]
6
7=== Description
8
9Simple bindings for [[http://www.postgresql.org/|PostgreSQL]]'s C-api.
10
11=== Author
12
13Original author: [[mailto:johs@copyleft.no|Johannes GrÞdem]]
14
15Please do not mail to Johannes directly as he no longer develops
16this egg.
17
18Current maintainer: [[Peter Bex]]
19
20==== Thanks to
21
22* Felix L. Winkelmann
23* Alex Shinn
24* Ed Watkeys
25* Taylor Campbell
26
27=== Requirements
28
29* [[easyffi]]
30* [[syntax-case]]
31
32=== Download
33
34[[http://www.call-with-current-continuation.org/eggs/postgresql.egg|postgresql.egg]]
35
36=== Documentation
37
38This extension provides an interface to the PostgreSQL relational
39database.
40
41==== Connection functions
42
43<procedure>(pg:connect CONNECTION-SPEC)</procedure>
44
45Opens a connection to the database given in CONNECTION-SPEC,
46which should be a list of conses each consisting of a symbol and a
47value.  The symbols should be parameter keywords recognized by
48PostgreSQL's connection function.  See the PostgreSQL documentation
49for these.  At the time of writing, they are {{host}},
50{{hostaddr}}, {{port}}, {{dbname}},
51{{user}}, {{password}}, {{connect_timeout}},
52{{options}}, {{sslmode}}, {{service}}.
53
54The return value is a connection-object.
55
56Also note that while these bindings use the non-blocking
57interface to connect to PostgreSQL, if you specify a hostname
58(using the {{host}}-keyword), the function
59might not be able to yield because the resolver will block.
60
61<procedure>(pg:close CONNECTION)</procedure>
62
63Closes the given {{CONNECTION}}.
64
65<procedure>(pg:reset CONNECTION)</procedure>
66
67Resets, that is, reopens the connection with the same
68connection-specs as was given when opening the original CONNECTION.
69
70<procedure>(pg:connection? OBJECT)</procedure>
71
72Returns true if OBJECT is a PostgreSQL connection-object.
73
74==== Query functions
75
76For each of the query functions, the query string is a string of one
77or more queries to PostgreSQL.  If more than one query is given
78(separated by semicolons), the tuples are provided as if a single
79query has been executed.  This means that the tuples you get will
80possibly not be of the same length.  Tuples are given as vectors.
81
82The values in the tuples are converted to a suitable Scheme
83representation, if it is supported.  See [[#conversion|Conversion]].
84
85For queries that don't return tuples, but which return the number of
86tuples affected instead, this number is given instead of a vector.
87
88Remember that your QUERY-string might need to be escaped.
89
90<procedure>(pg:query-fold-left QUERY CONNECTION FOLD-FUNCTION . SEEDS)</procedure>
91
92Run FOLD-FUNCTION on each tuple or tuple count returned by the
93query (or queries) specified by QUERY until all tuples or tuple
94counts have been read, in left to right order, or until the
95FOLD-FUNCTION returns {{#f}} as its first return value.
96(See the source code for {{pg:query-tuples}} for an
97example of how to use this function, and also
98[[http://srfi.schemers.org/srfi-44/|SRFI-44]].)
99
100<procedure>(pg:query-for-each PROC QUERY CONNECTION)</procedure>
101
102Runs the QUERY on PostgreSQL CONNECTION, and then maps PROC over each
103tuple returned by the query, if any.  The procedure should take one
104parameter, in which it is given a tuple.  Returns nothing.
105
106<procedure>(pg:query-tuples QUERY CONNECTION)</procedure>
107
108Returns a list of tuples produced by the database on CONNECTION
109as a reply to QUERY.  See also {{pg:query-for-each}}.
110
111<procedure>(pg:sql-null-object? OBJECT)</procedure>
112
113Returns true if OBJECT is an SQL {{NULL}}-value.  Typically used to
114check if an element in a tuple is {{NULL}}.
115
116==== Query functions
117
118<procedure>(pg:escape-string STRING)</procedure>
119
120Quotes special characters in {{STRING}} which are otherwise interpreted by the SQL parser.
121
122==== Constants
123
124<constant>(sql-null-object)</constant>
125
126Represents SQL {{NULL}} values.
127
128==== Error handling
129
130  condition: postgresql
131
132A condition of kind (exn postgresql) is
133signaled when an error occurs.  The postgresql component of this condition contains
134several properties.  Unless otherwise noted, these properties
135may not be present, in which case they have the value {{#f}}.
136
137; {{severity}} : One of the symbols {{error}}, {{fatal}}, {{panic}}, {{warning}},  {{notice}}, {{debug}}, {{info}}, {{log}}. Always present.
138; {{error-class}} : a symbol representating a Postgresql error class. 
139; {{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.
140; {{message-detail}} :  A secondary (to the usual {{exn message}} property) message with extra detail about the problem.
141; {{message-hint}} :  A string with a suggestion about what to do about the problem.
142; {{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.
143; {{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.
144; {{source-file}} :  The file name of the Postgresql source-code location where the error was reported.
145; {{source-line}} :  A string containing the line number of the Postgresql source-code location where the error was reported.
146; {{source-function}} :  The name of the source-code function reporting the error.
147
148==== Conversion
149
150Type information is read from the database the first time you
151connect to it.  Note that ISO-style dates are assumed, so please set
152PostgreSQL up to use this.  Here is an overview of how the mapping
153is done currently:
154
155<table>
156<tr>
157<th>PostgreSQL type</th>
158<th>Scheme type</th>
159</tr>
160<tr> <td>TEXT</td> <td>string</td> </tr>
161<tr> <td>BYTEA</td> <td>string</td> </tr>
162<tr> <td>CHAR</td> <td>char</td> </tr>
163<tr> <td>BPCHAR</td> <td>char</td> </tr>
164<tr> <td>BOOL</td> <td>boolean</td> </tr>
165<tr> <td>INT8</td> <td>fixnum or inexact<sup>1</sup></td> </tr>
166<tr> <td>INT4</td> <td>fixnum or inexact<sup>1</sup></td> </tr>
167<tr> <td>INT2</td> <td>fixnum</td> </tr>
168<tr> <td>FLOAT8</td> <td>inexact</td> </tr>
169<tr> <td>FLOAT4</td> <td>inexact</td> </tr>
170<tr> <td>ABSTIME</td> <td>unsupported<sup>2</sup></td> </tr>
171<tr> <td>RELTIME</td> <td>unsupported<sup>2</sup></td> </tr>
172<tr> <td>DATE</td> <td>vector {{#(year month date)}}</td> </tr>
173<tr> <td>TIME</td> <td>vector {{#(hour minute second microsecond)}}</td> </tr>
174<tr> 
175<td>TIMESTAMP</td>
176<td>vector {{#(year month date hour minute second microsecond)}}</td>
177</tr>
178<tr>
179<td>TIMESTAMPTZ</td>
180<td>vector {{#(year month date hour minute second microsecond timezone)}}</td>
181</tr>
182<tr> <td>INTERVAL</td> <td>unsupported<nowiki><sup>2</sup></nowiki></td> </tr>
183<tr> <td>NUMERIC</td> <td>fixnum or inexact<nowiki><sup>1</sup></nowiki></td> </tr>
184<tr> <td>OID</td> <td>fixnum or inexact<nowiki><sup>1</sup></nowiki></td> </tr>
185</table>
186
187
1881. 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.
1892. These are just returned as text for now.
190
191=== Example
192
193<example>
194<expr>
195(let ([conn (pg:connect '((dbname . "johs")))])
196  (pg:query-for-each
197   (lambda (tuple)
198     (do [(i 0 (+ i 1))]
199         [(= i (vector-length tuple))]
200       (let ([element (vector-ref tuple i)])
201         (format #t "~15S" (if (pg:sql-null-object? element)
202                               "NULL"
203                               element))))
204     (newline))
205   "SELECT * FROM foo LIMIT 5"
206   conn)
207  (pg:close conn))
208</expr>
209</example>
210
211=== Changelog
212
213* 2.0.12 - added {{pg:named-tuples}} parameter [Contributed by Graham Fawcett]
214* 2.0.11 - added syntax-case requirements to .meta file [Thanks to Michele Simionato]
215* 2.0.10 - adapted to new easyffi usage [Thanks to rreal]
216* 2.0.9 - Yet more improvements to error reporting
217* 2.0.8 - More detailed error information, export {{pg:sql-null-object}}
218* 2.0.7 - Added missing error-function [Thanks to Reed Sheridan]
219* 2.0.6 - Removed dependency on format [Thanks to Reed Sheridan]
220* 2.0.5 - Some bugfixes and {{pq:escape-string}} by Reed Sheridan; adapted to SRFI-69 hash-tables
221* 2.0.4 - Changed usage of hygienic macros in setup script
222* 2.0.3 - Bugfixes.
223* 2.0.0 - Interface improvements.  (Backward-incompatible.)
224* 1.2.1 - Non-blocking queries.
225* 1.2.0 - Optimizations, minor fixes and cleanups.
226
227=== License
228
229  Copyright (C) 2004 Johannes GrÞdem <johs@copyleft.no>
230  Redistribution and use in source and binary forms, with or without
231  modification, is permitted.
232 
233  THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS
234  OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
235  WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
236  ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE
237  LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
238  CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
239  OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
240  BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
241  LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
242  (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
243  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
244  DAMAGE.
Note: See TracBrowser for help on using the repository browser.