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

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

Describe {row,column}-fold-right

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