source: project/wiki/eggref/4/postgresql @ 14770

Last change on this file since 14770 was 14770, checked in by sjamaan, 11 years ago

Add docs for the Chicken 4 port of the Postgresql egg

File size: 20.7 KB
Line 
1[[tags: egg]]
2
3== postgresql
4
5[[toc:]]
6
7=== Description
8
9Bindings 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* [[sql-null]]
30
31=== Documentation
32
33This extension provides an interface to the
34[[http://www.postgresql.org|PostgreSQL]] relational database.
35
36==== Connection functions
37
38<procedure>(connect CONNECTION-SPEC [TYPE-PARSERS TYPE-UNPARSERS])</procedure>
39
40Opens a connection to the database given in CONNECTION-SPEC, which
41should be either a PostgreSQL connection string or an alist with
42entries consisting of a symbol and a value (which is internally
43converted to such a string).  The symbols should be connection
44keywords recognized by PostgreSQL's connection function.  See the
45PostgreSQL documentation for these.  At the time of writing, they are
46{{host}}, {{hostaddr}}, {{port}}, {{dbname}}, {{user}}, {{password}},
47{{connect_timeout}}, {{options}}, {{sslmode}}, {{service}}.
48
49{{TYPE-PARSERS}} is an optional alist that maps PostgreSQL type names
50to parser procedures, TYPE-UNPARSERS is an optional alist that maps
51predicates to unparser procedures.  They default to
52{{(default-type-parsers)}} and {{(default-type-unparsers)}},
53respectively (see [[#type-conversion|below]]).
54
55The return value is a connection-object.
56
57Also note that while these bindings use the non-blocking
58interface to connect to PostgreSQL, if you specify a hostname
59(using the {{host}}-keyword), the function
60might not be able to yield because the resolver will block.
61
62
63<procedure>(disconnect CONNECTION)</procedure>
64
65Disconnects from the given {{CONNECTION}}.
66
67<procedure>(reset-connection CONNECTION)</procedure>
68
69Resets, that is, reopens the connection with the same connection-specs
70as was given when opening the original CONNECTION.
71
72<procedure>(connection? OBJECT)</procedure>
73
74Returns true if OBJECT is a PostgreSQL connection-object.
75
76==== Query procedures
77
78<procedure>(query CONN QUERY . PARAMS)</procedure>
79
80Execute {{QUERY}}, which is a string containing one SQL statement.
81{{CONN}} indicates the connection on which to execute the query, and
82{{PARAMS}} is an arbitrary number of optional arguments indicating
83positional parameters ({{$1}}, {{$2}} etc).
84
85This returns a result object (see below).
86
87<examples>
88<example>
89<init>(use postgresql)</init>
90<expr>
91(let ((conn (connect '((dbname . test)))))
92  (row-map identity (query conn "SELECT $1::text, 2::int2" "hello")))
93</expr>
94<result>
95(("hello" 2))
96</result>
97</example>
98</example>
99
100<procedure>(query* CONN QUERY [PARAMS] [format: FORMAT] [raw: RAW?])</procedure>
101
102A less convenient but slightly more powerful version of the {{query}}
103procedure; {{PARAMS}} must now be a list (instead of rest-arguments).
104{{FORMAT}} is a symbol specifying how to return the resultset: either
105as {{binary}} or {{text}} (the default).  {{RAW}} is a boolean which
106defines whether the {{PARAMS}} should be treated "raw" or first passed
107through the unparsers associated with {{CONN}}.  If they are treated
108"raw", they must all be strings, blobs or sql-null objects.
109
110See [[#type-conversion|type conversion]] for more info on unparsers.
111
112<examples>
113<example>
114<init>(use postgresql)</init>
115<expr>
116(let ((conn (connect '((dbname . test)))))
117  (row-map*
118    (lambda (a b) (list (blob->string a) (blob->u8vector b)))
119    (query* conn "SELECT $1::text, 2::int2" '("hello") format: 'binary)))
120</expr>
121<result>
122(("hello" #u8(0 2)))
123</result>
124</example>
125</example>
126
127<procedure>(multi-query CONN QUERIES)</procedure>
128
129A simplified query procedure.  This allows no positional params and no
130control over how the result is returned (defaulting to text).  The one
131advantage is that it is allowed to enter multiple SQL statements in
132the {{QUERIES}} string.  This procedure returns a list of result
133objects, one for every statement in the string.
134
135<examples>
136<example>
137<init>(use postgresql)</init>
138<expr>
139(let ((conn (connect '((dbname . test)))))
140   (map (lambda (r) (row-values r 0))
141        (multi-query conn "SELECT 'hello', 'world'; SELECT 1")))
142</expr>
143<result>
144(("hello" "world") (1))
145</result>
146</example>
147</example>
148
149==== High-level API
150
151Usually you will use only these procedures to process result sets,
152but you can fall back to (or even mix with) the low-level API if
153you need to do custom things.
154
155<procedure>(row-fold KONS KNIL RESULT)</procedure>
156<procedure>(row-fold* KONS KNIL RESULT)</procedure>
157
158This is the fundamental fold operator for result sets. It calls
159{{(kons row seed)}} for every row, where {{row}} is the list of
160values in the current row and {{seed}} is the accumulated result
161from previous calls (initially {{knil}}).  It returns the final
162accumulated result.
163
164The starred version works the same, except it calls
165{{(kons col1 col2 ... seed)}} instead, so the procedure must
166know how many columns you have in the result set.
167
168<examples>
169<example>
170<init>(use postgresql)</init>
171<expr>
172(let ((conn (connect '((dbname . test)))))
173   (row-fold (lambda (row sum) (+ (car row) sum))
174             0
175             (query conn "SELECT 1 UNION SELECT 2")))
176</expr>
177<result>
1783
179</result>
180</example>
181<example>
182<init>(use postgresql)</init>
183<expr>
184(let ((conn (connect '((dbname . test)))))
185   (row-fold* (lambda (value str) (string-append str value))
186              ""
187              (query conn "SELECT 'hello, ' UNION SELECT 'world'")))
188</expr>
189<result>
190"hello, world"
191</result>
192</example>
193
194<procedure>(column-fold KONS KNIL RESULT)</procedure>
195<procedure>(column-fold* KONS KNIL RESULT)</procedure>
196
197As {{row-fold}}/{{row-fold*}}, except this iterates sideways through
198the columns instead of lengthways through the columns, calling
199{{KONS}} with all values in all the rows of the current column, from
200left to right.
201
202The starred version is much less useful here since you often don't
203know the number of returned columns, but it is provided for consistency.
204
205<examples>
206<example>
207<init>(use postgresql)</init>
208<expr>
209(let ((conn (connect '((dbname . test)))))
210   (column-fold (lambda (col sum) (+ (car col) sum))
211                0
212                (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
213</expr>
214<result>
215101
216</result>
217</example>
218</examples>
219
220<procedure>(row-for-each PROC RESULT)</procedure>
221<procedure>(row-for-each* PROC RESULT)</procedure>
222
223Simple {{for-each}}, calling the {{(PROC row)}} on each row, in turn,
224only for the purpose of its side-effects.  The starred version calls
225{{(PROC col1 col2)}}.
226
227<procedure>(column-for-each PROC RESULT)</procedure>
228<procedure>(column-for-each* PROC RESULT)</procedure>
229
230Column variants of {{row-for-each}}/{{row-for-each*}}.
231
232<procedure>(row-map PROC RESULT)</procedure>
233<procedure>(row-map* PROC RESULT)</procedure>
234
235Maps rows to lists by applying {{PROC}} to every row and using its
236result in the result list on the position corresponding to that of the
237row.  This procedure is not guaranteed to walk the result set in any
238particular order, so do ''not'' rely on the order {{PROC}} will be
239called.
240
241<examples>
242<example>
243<init>(use postgresql)</init>
244<expr>
245(let ((conn (connect '((dbname . test)))))
246   (row-map* +
247             (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
248</expr>
249<result>
250(101 202)
251</result>
252</example>
253</examples>
254
255<procedure>(column-map PROC RESULT)</procedure>
256<procedure>(column-map* PROC RESULT)</procedure>
257
258Column variants of {{row-map}}/{{row-map*}}.
259
260<examples>
261<example>
262<init>(use postgresql)</init>
263<expr>
264(let ((conn (connect '((dbname . test)))))
265   (column-map* +
266                (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
267</expr>
268<result>
269(3 300)
270</result>
271</example>
272</examples>
273
274==== Low-level API
275
276<procedure>(result? OBJ)</result>
277
278Returns {{#t}} when {{OBJ}} is a result object, {{#f}} otherwise.
279
280<procedure>(clear-result! RES)</procedure>
281
282Directly clean up all memory used by the result object.  This is
283normally deferred until garbage collection, but it's made available
284for when you want more control over when results are released.
285
286<procedure>(value-at RES ROW COLUMN [raw: RAW])</procedure>
287
288Returns the value at the specified {{ROW}} and {{COLUMN}}.  It is
289parsed by an appropriate parser unless {{RAW}} is specified and
290{{#t}}.  If {{RAW}} is true, the value is either a string, blob or an
291sql-null object.  Otherwise, it depends on the parsers.
292
293See [[#type-conversion|type conversion]] for more info on parsers.
294
295<procedure>(row-values RES ROW [raw: RAW])</procedure>
296
297Returns a list of all the columns' values at the given {{ROW}} number.
298
299<procedure>(column-values RES ROW [raw: RAW])</procedure>
300
301Returns a list of all the rows' values at the given {{COLUMN}} number.
302
303<procedure>(row-alist RES ROW)</procedure>
304
305Returns an alist of the values at the given {{ROW}} number.  The keys
306of the alist are made up by the matching column names, as symbols.
307
308<procedure>(affected-rows RES)</procedure>
309
310For INSERT or UPDATE statements, this returns the number of rows
311affected by the statement that RES is a result for.  Otherwise it's
312zero.
313
314<procedure>(inserted-oid RES)</procedure>
315
316For INSERT statements resulting in a single record being inserted,
317this returns the OID (a number) assigned to the newly inserted row.
318Returns {{#f}} for non-INSERT or multi-row INSERTs, or INSERTs into
319tables without OIDs.
320
321<procedure>(row-count RES)</procedure>
322
323Returns the number of rows in the result set.
324
325<procedure>(column-count RES)</procedure>
326
327Returns the number of columns in the result set.
328
329<procedure>(column-index RES COLUMN)</procedure>
330
331Returns the index of {{COLUMN}} in the result set. {{COLUMN}} should
332be a symbol indicating the column name.
333
334<procedure>(column-name RES INDEX)</procedure>
335
336Returns the name of the column (a symbol) at the position in the
337result set specified by {{INDEX}}.  This is its aliased name in the
338result set.
339
340<procedure>(column-names RES)</procedure>
341
342Returns a list of all the column names (symbols) in the result set.
343The position in the list reflects the position of the column in the
344result set.
345
346<procedure>(column-format RES INDEX)</procedure>
347
348Returns the format of the column at {{INDEX}}, which is a symbol;
349Either {{text}} or {{binary}}.  This determines whether the value
350returned by {{value-at}} will be a string or a blob.
351
352<procedure>(column-type RES INDEX)</procedure>
353
354Returns the OID (an integer) of the column at {{INDEX}}.
355
356<procedure>(column-type-modifier RES INDEX)</procedure>
357
358Returns an type-specific modifier (a number), or {{#f}} if the type
359has no modifier.
360
361<procedure>(table-oid RES INDEX)</procedure>
362
363The OID (a number) of the table from whence the result column at
364{{INDEX}} originated, or {{#f}} if the column is not a simple
365reference to a table column.
366
367<procedure>(table-column-index RES INDEX)</procedure>
368
369Returns the column number (within its table) of the column making up
370the query result column at the position specified by {{INDEX}}.
371
372'''Note:''' This procedure returns indexes starting at zero, as one
373would expect.  However, the underlying C function {{PQftablecol}} is
374one-based.  This might trip up experienced Postgres hackers.
375
376
377==== Value escaping
378
379To embed arbitrary values in query strings, you must escape them
380first, to protect yourself from SQL injection bugs.  This is not
381required if you use positional arguments (the {{PARAMS}} arguments
382in the {{query}} procedures).
383
384<procedure>(escape-string CONNECTION STRING)</procedure>
385
386Quotes special characters in {{STRING}} which are otherwise
387interpreted by the SQL parser, obeying the {{CONNECTION}}'s encoding
388settings.
389
390<procedure>(escape-bytea CONNECTION STRING)</procedure>
391
392Quotes special characters in {{STRING}} which are otherwise
393interpreted by the SQL parser.  This differs from {{escape-string}} in
394that some bytes are doubly encoded so they can be used for bytea
395columns.
396
397This is required because of a technicality; PostgreSQL first parses
398the string value as a string, and then casts this string to bytea,
399interpreting another layer of escape codes.
400
401For example, {{E'a\\000bcd'}} is first converted to {{'a\000bcd'}} by
402the text parser, and then interpreted by the bytea parser as an "a"
403followed by a NUL byte, followed by "bcd".  In Scheme, the value
404returned by {{(escape-bytea conn "a\x00bcd")}} is {{"a\\\\000bcd"}}.
405Yes, that's a lot of backslashes :)
406
407<procedure>(unescape-bytea STRING)</procedure>
408
409This unescapes a bytea '''result''' from the server.  It is '''not the
410inverse of {{escape-bytea}}''', because string values returned by the
411server are not escaped for the text-parser. (ie, step one in the encoding
412process described under {{escape-bytea}} is skipped)
413
414==== Constants
415
416<constant>invalid-oid</constant>
417
418Represents the numeric value of the invalid Oid.  Rarely useful,
419except perhaps when doing low-level operations in the system catalog.
420
421==== Error handling
422
423  condition: postgresql
424
425A condition of kind (exn postgresql) is signaled when an error occurs.
426The postgresql component of this condition contains several
427properties.  Unless otherwise noted, these properties may not be
428present, in which case they have the value {{#f}}.
429
430; {{severity}} : One of the symbols {{error}}, {{fatal}}, {{panic}}, {{warning}},  {{notice}}, {{debug}}, {{info}}, {{log}}. Always present.
431; {{error-class}} : A string representating a Postgresql error class (the first two characters of {{error-code}}).
432; {{error-code}} :  A string representing a Postgresql error code.  See the [[http://www.postgresql.org/docs/8.3/static/errcodes-appendix.html|Postgresql documentation]] for a description of error codes and error classes.
433; {{message-detail}} :  A secondary (to the usual {{exn message}} property) message with extra detail about the problem.
434; {{message-hint}} :  A string with a suggestion about what to do about the problem.
435; {{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.
436; {{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.
437; {{source-file}} :  The file name of the Postgresql source-code location where the error was reported.
438; {{source-line}} :  A string containing the line number of the Postgresql source-code location where the error was reported.
439; {{source-function}} :  The name of the source-code function reporting the error.
440
441==== Type conversion
442
443Type information is read from the database the first time you connect
444to it.  Result set values are either text or binary (or sql null).  If
445they are text, they are converted to Scheme objects by type parsers,
446as described below.  If they are binary, they will be returned as
447unprocessed blobs (which you can then convert to u8vectors or strings).
448
449===== Parsers
450
451<parameter>(default-type-parsers [ALIST])</parameter>
452
453Postgres result values are always just strings, but it is possible to
454map these to real Scheme objects.  With this parameter, you can
455map your own custom postgresql datatype to Scheme datatypes.
456
457The alist is a mapping of Postgres type names (strings) to procedures
458accepting a string and returning a Scheme object of the desired type.
459
460The parsers can also be set per connection with the {{TYPE-PARSERS}}
461argument of the {{connect}} procedure.
462
463<examples>
464<example>
465<init>(use postgresql)</init>
466<expr>
467(parameterize ((default-type-parsers `(("text" . ,string->symbol))))
468  (let ((conn (connect '((dbname . test)))))
469    (row-map identity (query conn "SELECT 'hello'::text"))))
470</expr>
471<result>
472(hello)
473</result>
474</example>
475</examples>
476
477The default parsers look like this:
478
479<enscript highlight=scheme>
480`(("text" . ,identity)
481  ("bytea" . ,bytea-parser)
482  ("char" . ,char-parser)
483  ("bpchar" . ,identity)
484  ("bool" . ,bool-parser)
485  ("int8" . ,numeric-parser)
486  ("int4" . ,numeric-parser)
487  ("int2" . ,numeric-parser)
488  ("float4" . ,numeric-parser)
489  ("float8" . ,numeric-parser)
490  ("numeric" . ,numeric-parser)
491  ("oid" . ,numeric-parser))
492</enscript>
493
494These parsers are described below.  For anything where no parser is
495found, the value is returned verbatim (which is always a string, or a
496blob in case of binary data).
497
498<procedure>(update-type-parsers! CONN [TYPE-PARSERS])</procedure>
499
500As described above, type information is extracted from the system
501catalog whenever you initiate a new connection.  However, there is a
502bootstrapping problem when you are defining custom data types.  You
503must first connect before you can define your custom data types.  But
504the type parsers do not have the information for this new type yet, so
505you must update them.
506
507To do this, you can call {{update-type-parsers!}}.  This procedure
508updates all the type parsers originally associated with connection
509{{CONN}}.  By providing the optional {{TYPE-PARSERS}}, you can
510override the existing type parsers for this connection with new ones,
511otherwise the old ones are just refreshed.
512
513<procedure>(bool-parser STR)</procedure>
514
515Returns {{#t}} if the string equals {{"t"}}, {{#f}} otherwise.
516
517<procedure>(bytea-parser STR)</procedure>
518
519Returns a u8vector containing the bytes in STR, after unescaping it
520using {{unescape-bytea}}.
521
522<procedure>(char-parser STR)</procedure>
523
524Returns the first character in STR.
525
526<procedure>(numeric-parser STR)</procedure>
527
528Returns {{STR}} converted to a number using decimal representation.
529If {{STR}} could not be converted to a number, raises an error.
530
531===== Unparsers
532
533<parameter>(default-type-unparsers [ALIST])</parameter>
534
535Just as PostgreSQL types are converted to Scheme types in result sets,
536Scheme types need to be converted to PostgreSQL types when providing
537positional parameters to queries.  For this, the library uses type
538unparsers.  Just like type parsers, you can override them either
539per-connection using the {{TYPE-UNPARSERS}} parameter to the
540{{connect}} procedure, or globally by changing a parameter.
541
542This alist is a mapping of predicates to unparsers.  Predicates are
543procedures which accept a scheme object and return a true value if the
544object is of the type for which the unparser is intended.  Unparsers
545are procedures which accept a scheme object and return either a
546string, a blob or an sql-null object to be used in the query.
547
548It is not necessary to reload type unparsers after defining a new data
549type in the database.
550
551Order matters; the type unparser alist is traversed from left to
552right, trying predicates in order and invoking the unparser linked to
553the first predicate that does not return {{#f}}.  If none of the
554predicates match, the type must be of string, blob or sql-null type.
555If not, the query procedure will raise an error.
556
557The default unparsers look like this:
558
559<enscript highlight=scheme>
560`((,string? . ,identity)
561  (,u8vector? . ,u8vector->blob/shared)
562  (,char? . ,string)
563  (,boolean? . ,bool-unparser)
564  (,number? . ,number->string))
565</enscript>
566
567<procedure>(bool-unparser B)</procedure>
568
569Returns {{"TRUE"}} for true values and {{"FALSE"}} for {{#f}}.
570
571=== Changelog
572
573* 3.0 - Port to Chicken 4.  Major code overhaul, backwards incompatible.
574* 2.0.14 - Add PQescapeStringConn functionality to pg:escape-string. Fixed weird compilation issue with SRFI-69 by removing it from USES list.
575* 2.0.12 - added {{pg:named-tuples}} parameter [Contributed by Graham Fawcett]
576* 2.0.11 - added syntax-case requirements to .meta file [Thanks to Michele Simionato]
577* 2.0.10 - adapted to new easyffi usage [Thanks to rreal]
578* 2.0.9 - Yet more improvements to error reporting
579* 2.0.8 - More detailed error information, export {{pg:sql-null-object}}
580* 2.0.7 - Added missing error-function [Thanks to Reed Sheridan]
581* 2.0.6 - Removed dependency on format [Thanks to Reed Sheridan]
582* 2.0.5 - Some bugfixes and {{pq:escape-string}} by Reed Sheridan; adapted to SRFI-69 hash-tables
583* 2.0.4 - Changed usage of hygienic macros in setup script
584* 2.0.3 - Bugfixes.
585* 2.0.0 - Interface improvements.  (Backward-incompatible.)
586* 1.2.1 - Non-blocking queries.
587* 1.2.0 - Optimizations, minor fixes and cleanups.
588
589=== License
590
591  Copyright (C) 2008-2009 Peter Bex
592  Copyright (C) 2004 Johannes GrÞdem <johs@copyleft.no>
593  Redistribution and use in source and binary forms, with or without
594  modification, is permitted.
595 
596  THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS
597  OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
598  WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
599  ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE
600  LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
601  CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
602  OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
603  BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
604  LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
605  (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
606  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
607  DAMAGE.
Note: See TracBrowser for help on using the repository browser.