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

Last change on this file since 14901 was 14901, checked in by sjamaan, 10 years ago

Add note about threading and postgresql

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