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

Last change on this file since 21513 was 21513, checked in by sjamaan, 9 years ago

Document update-type-unparsers! procedure

File size: 31.6 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
31You will also need to have
32[[http://www.postgresql.org/docs/current/interactive/libpq.html|libpq]]
33installed, including development headers.
34
35=== Documentation
36
37This extension provides an interface to the
38[[http://www.postgresql.org|PostgreSQL]] relational database.
39
40==== Connection management
41
42<procedure>(connect CONNECTION-SPEC [TYPE-PARSERS [TYPE-UNPARSERS]])</procedure>
43
44Opens a connection to the database given in CONNECTION-SPEC, which
45should be either a PostgreSQL connection string or an alist with
46entries consisting of a symbol and a value (which is internally
47converted to such a string).  The symbols should be connection
48keywords recognized by PostgreSQL's connection function.  See the
49[[http://www.postgresql.org/docs/current/interactive/libpq-connect.html|list of PQconnectdbParams parameter keywords]] in the PostgreSQL documentation.
50At the time of writing, they are {{host}}, {{hostaddr}}, {{port}}, {{dbname}}, {{user}}, {{password}}, {{connect_timeout}}, {{options}}, {{sslmode}}, {{service}}.
51
52{{TYPE-PARSERS}} is an optional alist that maps PostgreSQL type names
53to parser procedures, TYPE-UNPARSERS is an optional alist that maps
54predicates to unparser procedures.  They default to
55{{(default-type-parsers)}} and {{(default-type-unparsers)}},
56respectively (see [[#type-conversion|below]]).
57
58The return value is a connection-object.
59
60Also note that while these bindings use the non-blocking
61interface to connect to PostgreSQL, if you specify a hostname
62(using the {{host}}-keyword), the function
63might not be able to yield because the resolver will block.
64
65'''Note''': You cannot use the same connection from multiple threads.
66If you need to talk to the same server from different threads, simply
67create a second connection.
68
69<procedure>(disconnect CONNECTION)</procedure>
70
71Disconnects from the given {{CONNECTION}}.
72
73<procedure>(reset-connection CONNECTION)</procedure>
74
75Resets, that is, reopens the connection with the same connection-specs
76as was given when opening the original {{CONNECTION}}.
77
78<procedure>(type-parsers CONNECTION)</procedure>
79
80Retrieve the alist of type parsers associated with the {{CONNECTION}}.
81
82<procedure>(type-unparsers CONNECTION)</procedure>
83
84Retrieve the alist of type unparsers associated with the {{CONNECTION}}.
85
86<procedure>(connection? OBJECT)</procedure>
87
88Returns true if OBJECT is a PostgreSQL connection-object.
89
90==== Query procedures
91
92<procedure>(query CONN QUERY . PARAMS)</procedure>
93
94Execute {{QUERY}}, which is a string containing one SQL statement.
95{{CONN}} indicates the connection on which to execute the query, and
96{{PARAMS}} is an arbitrary number of optional arguments indicating
97positional parameters ({{$1}}, {{$2}} etc).
98
99This returns a result object (see below).
100
101<enscript highlight="scheme">
102(use postgresql)
103
104(let ((conn (connect '((dbname . test)))))
105  (row-values (query conn "SELECT $1::text, 2::int2" "hello")))
106 => (("hello" 2))
107</enscript>
108
109<procedure>(query* CONN QUERY [PARAMS] [format: FORMAT] [raw: RAW?])</procedure>
110
111A less convenient but slightly more powerful version of the {{query}}
112procedure; {{PARAMS}} must now be a list (instead of rest-arguments).
113{{FORMAT}} is a symbol specifying how to return the resultset: either
114as {{binary}} or {{text}} (the default).  {{RAW}} is a boolean which
115defines whether the {{PARAMS}} should be treated "raw" or first passed
116through the unparsers associated with {{CONN}}.  If they are treated
117"raw", they must all be strings, blobs or sql-null objects.
118
119See [[#type-conversion|type conversion]] for more info on unparsers.
120
121<enscript highlight="scheme">
122(use postgresql)
123
124(let ((conn (connect '((dbname . test)))))
125  (row-map*
126    (lambda (a b) (list (blob->string a) (blob->u8vector b)))
127    (query* conn "SELECT $1::text, 2::int2" '("hello") format: 'binary)))
128 => (("hello" #u8(0 2)))
129</enscript>
130
131==== High-level API
132
133Usually you will use only these procedures to process result sets,
134but you can fall back to (or even mix with) the low-level API if
135you need to do custom things.
136
137<procedure>(row-fold KONS KNIL RESULT)</procedure><br>
138<procedure>(row-fold* KONS KNIL RESULT)</procedure><br>
139
140This is the fundamental result set iterator. It calls
141{{(kons row seed)}} for every row, where {{row}} is the list of
142values in the current row and {{seed}} is the accumulated result
143from previous calls (initially {{knil}}), ie its pattern looks
144like {{(KONS ROWN ... (KONS ROW2 (KONS ROW1 KNIL)))}}.  It returns
145the final accumulated result.
146
147The starred version works the same, except it calls
148{{(kons rowN-col1 rowN-col2 ... seed)}} instead of {{(kons rowN seed)}},
149so the procedure must know how many columns you have in the result set.
150
151<enscript highlight="scheme">
152(use postgresql)
153
154(let ((conn (connect '((dbname . test)))))
155   (row-fold (lambda (row sum) (+ (car row) sum))
156             0
157             (query conn "SELECT 1 UNION SELECT 2")))
158 => 3
159
160(let ((conn (connect '((dbname . test)))))
161   (row-fold* (lambda (value str) (string-append str value))
162              ""
163              (query conn "SELECT 'hello, ' UNION SELECT 'world'")))
164 => "hello, world"
165</enscript>
166
167<procedure>(column-fold KONS KNIL RESULT)</procedure><br>
168<procedure>(column-fold* KONS KNIL RESULT)</procedure><br>
169
170As {{row-fold}}/{{row-fold*}}, except this iterates sideways through
171the columns instead of lengthways through the columns, calling
172{{KONS}} with all values in all the rows of the current column, from
173left to right.
174
175The starred version is much less useful here since you often don't
176know the number of returned columns, but it is provided for consistency.
177
178<enscript highlight="scheme">
179(use postgresql)
180
181(let ((conn (connect '((dbname . test)))))
182   (column-fold (lambda (col sum) (+ (car col) sum))
183                0
184                (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
185 => 101
186</enscript>
187
188<procedure>(row-fold-right KONS KNIL RESULT)</procedure><br>
189<procedure>(row-fold-right* KONS KNIL RESULT)</procedure><br>
190
191The fundamental result set recursion operator;  Calls
192{{(KONS COL1 (KONS COL2 (KONS ... KNIL)))}} instead of
193{{(KONS COLN ... (KONS COL2 (KONS COL1 KNIL)))}}.
194
195<procedure>(column-fold-right KONS KNIL RESULT)</procedure><br>
196<procedure>(column-fold-right* KONS KNIL RESULT)</procedure><br>
197
198Column variants of {{row-fold-right}}/{{row-fold-right*}}.
199
200<procedure>(row-for-each PROC RESULT)</procedure><br>
201<procedure>(row-for-each* PROC RESULT)</procedure><br>
202
203Simple {{for-each}}, calling the {{(PROC row)}} on each row, in turn,
204only for the purpose of its side-effects.  The starred version calls
205{{(PROC col1 col2 ...)}}.
206
207<procedure>(column-for-each PROC RESULT)</procedure><br>
208<procedure>(column-for-each* PROC RESULT)</procedure><br>
209
210Column variants of {{row-for-each}}/{{row-for-each*}}.
211
212<procedure>(row-map PROC RESULT)</procedure><br>
213<procedure>(row-map* PROC RESULT)</procedure><br>
214
215Maps rows to lists by applying {{PROC}} to every row and using its
216result in the result list on the position corresponding to that of the
217row.  This procedure is not guaranteed to walk the result set in any
218particular order, so do ''not'' rely on the order {{PROC}} will be
219called.
220
221<enscript highlight="scheme">
222(use postgresql)
223
224(let ((conn (connect '((dbname . test)))))
225   (row-map* + (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
226 => (101 202)
227</enscript>
228
229<procedure>(column-map PROC RESULT)</procedure><br>
230<procedure>(column-map* PROC RESULT)</procedure><br>
231
232Column variants of {{row-map}}/{{row-map*}}.
233
234<enscript highlight="scheme">
235(use postgresql)
236
237(let ((conn (connect '((dbname . test)))))
238   (column-map* + (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
239 => (3 300)
240</enscript>
241
242===== Transaction management
243
244<procedure>(with-transaction CONN THUNK)</procedure>
245
246Execute {{THUNK}} within a {{BEGIN TRANSACTION}} block, and return
247the value of {{thunk}}.
248
249The transaction is committed if {{thunk}} returns a true value.  If an
250exception occurs during {{thunk}}, or {{thunk}} returns {{#f}}, or the
251commit fails, the transaction will be rolled back. If this rollback
252fails, that is a critical error and you should likely abort.
253
254Nested applications of {{with-transaction}} '''are''' supported --
255only those statements executed within {{THUNK}} are committed or
256rolled back by any {{with-transaction}} call, as you would expect.
257
258However, escaping or re-entering the dynamic extent of {{thunk}} will
259not commit or rollback the in-progress transaction, so it is highly
260discouraged to jump out of a transaction.  You will definitely run
261into trouble, unless you can ensure that no other statements will be
262executed on this connection until the outermost {{with-transaction}}
263returns normally.
264
265<procedure>(in-transaction? CONN)</procedure>
266
267Returns {{#t}} if there is currently a transaction in progress on the
268connection {{CONN}}.  Returns {{#f}} if no transaction is in progress.
269
270==== Low-level result API
271
272This API allows you to inspect result objects on the individual row
273and column level.
274
275<procedure>(result? OBJ)</procedure>
276
277Returns {{#t}} when {{OBJ}} is a result object, {{#f}} otherwise.
278
279<procedure>(clear-result! RES)</procedure>
280
281Directly clean up all memory used by the result object.  This is
282normally deferred until garbage collection, but it's made available
283for when you want more control over when results are released.
284
285<procedure>(value-at RES [COLUMN [ROW]] [raw: RAW])</procedure>
286
287Returns the value at the specified {{COLUMN}} and {{ROW}}.  It is
288parsed by an appropriate parser unless {{RAW}} is specified and
289{{#t}}.  If {{RAW}} is true, the value is either a string, blob or an
290sql-null object.  Otherwise, it depends on the parsers.
291
292If {{ROW}} or {{COLUMN}} are not specified, they default to zero.
293This makes for more convenient syntax if you're just reading out
294a result of a query which always has one row or even one value.
295
296See [[#type-conversion|type conversion]] for more info on parsers.
297
298<procedure>(row-values RES [ROW] [raw: RAW])</procedure>
299
300Returns a list of all the columns' values at the given {{ROW}} number.
301If {{ROW}} is omitted, it defaults to zero.  If {{RAW}} is true, the
302values are either strings, blobs or sql-null objects.  Otherwise, it
303depends on the parsers.
304
305<procedure>(column-values RES [COLUMN] [raw: RAW])</procedure>
306
307Returns a list of all the rows' values at the given {{COLUMN}} number.
308If {{COLUMN}} is omitted, it defaults to zero.  If {{RAW}} is true, the
309values are either strings, blobs or sql-null objects.  Otherwise, it
310depends on the parsers.
311
312<procedure>(row-alist RES [ROW] [raw: RAW])</procedure>
313
314Returns an alist of the values at the given {{ROW}} number.  The keys
315of the alist are made up by the matching column names, as symbols.
316
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>(affected-rows RES)</procedure>
322
323For INSERT or UPDATE statements, this returns the number of rows
324affected by the statement that RES is a result for.  Otherwise it's
325zero.
326
327<procedure>(inserted-oid RES)</procedure>
328
329For INSERT statements resulting in a single record being inserted,
330this returns the OID (a number) assigned to the newly inserted row.
331Returns {{#f}} for non-INSERT or multi-row INSERTs, or INSERTs into
332tables without OIDs.
333
334<procedure>(row-count RES)</procedure>
335
336Returns the number of rows in the result set.
337
338<procedure>(column-count RES)</procedure>
339
340Returns the number of columns in the result set.
341
342<procedure>(column-index RES COLUMN)</procedure>
343
344Returns the index of {{COLUMN}} in the result set. {{COLUMN}} should
345be a symbol indicating the column name.
346
347<procedure>(column-name RES INDEX)</procedure>
348
349Returns the name of the column (a symbol) at the position in the
350result set specified by {{INDEX}}.  This is its aliased name in the
351result set.
352
353<procedure>(column-names RES)</procedure>
354
355Returns a list of all the column names (symbols) in the result set.
356The position in the list reflects the position of the column in the
357result set.
358
359<procedure>(column-format RES INDEX)</procedure>
360
361Returns the format of the column at {{INDEX}}, which is a symbol;
362Either {{text}} or {{binary}}.  This determines whether the value
363returned by {{value-at}} will be a string or a blob.
364
365<procedure>(column-type RES INDEX)</procedure>
366
367Returns the OID (an integer) of the column at {{INDEX}}.
368
369<procedure>(column-type-modifier RES INDEX)</procedure>
370
371Returns an type-specific modifier (a number), or {{#f}} if the type
372has no modifier.
373
374<procedure>(table-oid RES INDEX)</procedure>
375
376The OID (a number) of the table from whence the result column at
377{{INDEX}} originated, or {{#f}} if the column is not a simple
378reference to a table column.
379
380<procedure>(table-column-index RES INDEX)</procedure>
381
382Returns the column number (within its table) of the column making up
383the query result column at the position specified by {{INDEX}}.
384
385'''Note:''' This procedure returns indexes starting at zero, as one
386would expect.  However, the underlying C function {{PQftablecol}} is
387one-based.  This might trip up experienced Postgres hackers.
388
389
390==== Value escaping
391
392To embed arbitrary values in query strings, you must escape them
393first, to protect yourself from SQL injection bugs.  This is not
394required if you use positional arguments (the {{PARAMS}} arguments
395in the {{query}} procedures).
396
397<procedure>(escape-string CONNECTION STRING)</procedure>
398
399Quotes special characters in {{STRING}} which are otherwise
400interpreted by the SQL parser, obeying the {{CONNECTION}}'s encoding
401settings.
402
403<procedure>(escape-bytea CONNECTION STRING)</procedure>
404
405Quotes special characters in {{STRING}} which are otherwise
406interpreted by the SQL parser.  This differs from {{escape-string}} in
407that some bytes are doubly encoded so they can be used for bytea
408columns.
409
410This is required because of a technicality; PostgreSQL first parses
411the string value as a string, and then casts this string to bytea,
412interpreting another layer of escape codes.
413
414For example, {{E'a\\000bcd'}} is first converted to {{'a\000bcd'}} by
415the text parser, and then interpreted by the bytea parser as an "a"
416followed by a NUL byte, followed by "bcd".  In Scheme, the value
417returned by {{(escape-bytea conn "a\x00bcd")}} is {{"a\\\\000bcd"}}.
418Yes, that's a lot of backslashes :)
419
420<procedure>(unescape-bytea STRING)</procedure>
421
422This unescapes a bytea '''result''' from the server.  It is '''not the
423inverse of {{escape-bytea}}''', because string values returned by the
424server are not escaped for the text-parser. (ie, step one in the encoding
425process described under {{escape-bytea}} is skipped)
426
427==== COPY support
428
429===== High-level COPY API
430
431This API is ''experimental'' and as such should be expected to change.
432If you have suggestions on how to improve the API, please let me know!
433
434<procedure>(copy-query-fold KONS KNIL CONNECTION QUERY . PARAMS)</procedure><br>
435<procedure>(copy-query*-fold KONS KNIL CONNECTION QUERY [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
436
437This is the fundamental {{COPY TO STDOUT}} iterator. It calls
438{{(kons data seed)}} for every row of COPY data returned by {{QUERY}},
439where {{data}} is either a string or a blob depending on whether
440the {{COPY}} query asked for binary or text data and {{seed}} is the
441accumulated result from previous calls (initially {{knil}}), ie its
442pattern looks like {{(KONS DATAN ... (KONS DATA2 (KONS DATA1 KNIL)))}}.
443It returns the final accumulated result.
444
445The starred and nonstarred version are analogous to {{query}} and {{query*}}.
446
447<enscript highlight="scheme">
448(use postgresql)
449
450(let ((conn (connect '((dbname . test)))))
451  (copy-query-fold
452    (lambda (data sum)
453      ;; Note the string-drop-right is necessary because the default
454      ;; COPY format uses newlines to indicate row endings
455      (+ (string->number (string-drop-right data 1)) sum))
456    0 conn "COPY (SELECT 1 UNION SELECT 2) TO STDOUT"))
457 => 3
458</enscript>
459
460<procedure>(copy-query-fold-right KONS KNIL CONNECTION . PARAMS)</procedure><br>
461<procedure>(copy-query*-fold-right KONS KNIL CONNECTION [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
462
463The fundamental {{COPY TO STDOUT}} recursion operator;  Calls
464{{(KONS DATA1 (KONS DATA2 (KONS ... KNIL)))}} instead of
465{{(KONS DATAN ... (KONS DATA2 (KONS DATA1 KNIL)))}}.
466
467'''Warning''': It is not recommended to use this when the returned
468data is very big.  It is usually much cheaper (memory-wise) to use
469{{copy-query-fold}} and reverse the result object, if the object's
470type supports that.
471
472The starred and nonstarred version are analogous to {{query}} and {{query*}}.
473
474<procedure>(copy-query-map PROC CONNECTION . PARAMS)</procedure><br>
475<procedure>(copy-query*-map PROC CONNECTION [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
476
477Maps {{COPY TO STDOUT}} output rows from {{QUERY}} to lists by calling
478{{PROC}} on each data row returned by the server.  If the {{QUERY}}
479asked for binary data, the data supplied to {{PROC}} will be in
480blob form.  Otherwise, the data will be provided as strings.
481
482The starred and nonstarred version are analogous to {{query}} and {{query*}}.
483
484<enscript highlight="scheme">
485(use postgresql)
486
487(let ((conn (connect '((dbname . test)))))
488   (copy-map (lambda (x) (apply + (map string->number (string-split x))))
489             conn "COPY (SELECT 1, 100 UNION SELECT 2, 200) TO STDOUT"))
490 => (101 202)
491</enscript>
492
493<procedure>(copy-query-for-each PROC CONNECTION . PARAMS)</procedure><br>
494<procedure>(copy-query*-for-each PROC CONNECTION [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
495
496Simple {{for-each}}, calling the {{(PROC data)}} on each row of
497{{COPY TO STDOUT}} data returned by {{QUERY}, in turn, only for the
498purpose of its side-effects.
499
500The starred and nonstarred version are analogous to {{query}} and {{query*}}.
501
502<procedure>(with-output-to-copy-query THUNK CONNECTION QUERY . PARAMS)</procedure><br>
503<procedure>(with-output-to-copy-query* THUNK CONNECTION QUERY [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
504
505Call {{THUNK}} with {{CURRENT-OUTPUT-PORT}} parameterized to a port
506that writes {{COPY FROM STDIN}} data to the database connection
507{{CONN}} for {{QUERY}}.
508
509Returns the final result of {{THUNK}}.
510
511The starred and nonstarred version are analogous to {{query}} and {{query*}}.
512
513<enscript highlight="scheme">
514(use postgresql)
515
516(with-output-to-copy-query
517  (lambda () (print "one\t1") (print "two\t2") "That's a wrap")
518  conn "COPY my_table (textcol, numbercol) FROM STDIN")
519 => "That's a wrap"
520</enscript>
521
522<procedure>(call-with-output-copy-query PROC CONNECTION QUERY . PARAMS)</procedure><br>
523<procedure>(call-with-output-copy-query* PROC CONNECTION QUERY [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
524
525Like {{with-output-to-copy-query}}, except it calls {{PROC}} with one
526argument (the copy port) instead of parameterizing {{CURRENT-OUTPUT-PORT}}.
527
528
529===== Low-level COPY API
530
531This API is close to the C API.  It requires you to first execute a
532COPY query (using the {{query}} procedure), and then you can put or
533get data from the connection.  You cannot run other queries while the
534connection is in COPY state.
535
536<procedure>(put-copy-data CONNECTION DATA)</procedure>
537
538Put copy data on the {{CONNECTION}}. {{DATA}} is either a string, a
539blob or a u8vector and should be in the format expected by the server.
540
541<procedure>(put-copy-end CONNECTION [ERROR-MESSAGE])</procedure>
542
543This ends the COPY process.  If {{ERROR-MESSAGE}} is supplied and not
544{{#f}}, the data sent up till now is discarded by the server and an
545error message is triggered on the server.  If {{ERROR-MESSAGE}} is not
546supplied or {{#f}}, the server will commit the copied data to the
547target table and succeed.
548
549A result object is returned upon success.  This result object
550is currently not useful.
551
552<procedure>(get-copy-data CONNECTION [format: FORMAT])</procedure>
553
554Obtain one row of COPY data from the server.  The data's contents will
555be in the format indicated by the server.  If FORMAT is {{'text}}, it
556the data will be returned as a string, if it is {{'binary}}, it will
557be returned as a blob.  The user is responsible for providing the
558right format to match the output format of the query sent earlier.
559
560After the last row is received, this procedure returns a result object
561(which can be detected by calling {{result?}} on it).
562
563==== Constants
564
565<constant>invalid-oid</constant>
566
567Represents the numeric value of the invalid Oid.  Rarely useful,
568except perhaps when doing low-level operations in the system catalog.
569
570==== Error handling
571
572  condition: postgresql
573
574A condition of kind (exn postgresql) is signaled when an error occurs.
575The postgresql component of this condition contains several
576properties.  Unless otherwise noted, these properties may not be
577present, in which case they have the value {{#f}}.
578
579; {{severity}} : One of the symbols {{error}}, {{fatal}}, {{panic}}, {{warning}},  {{notice}}, {{debug}}, {{info}}, {{log}}. Always present.
580; {{error-class}} : A string representing a Postgresql error class (the first two characters of {{error-code}}).
581; {{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.
582; {{message-detail}} :  A secondary (to the usual {{exn message}} property) message with extra detail about the problem.
583; {{message-hint}} :  A string with a suggestion about what to do about the problem.
584; {{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.
585; {{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.
586; {{source-file}} :  The file name of the Postgresql source-code location where the error was reported.
587; {{source-line}} :  A string containing the line number of the Postgresql source-code location where the error was reported.
588; {{source-function}} :  The name of the source-code function reporting the error.
589
590==== Type conversion
591
592Type information is read from the database the first time you connect
593to it.  Result set values are either text or binary (or sql null).  If
594they are text, they are converted to Scheme objects by type parsers,
595as described below.  If they are binary, they will be returned as
596unprocessed blobs (which you can then convert to u8vectors or strings).
597
598===== Parsers
599
600<parameter>(default-type-parsers [ALIST])</parameter>
601
602Postgres result values are always just strings, but it is possible to
603map these to real Scheme objects.  With this parameter, you can
604map your own custom postgresql datatype to Scheme datatypes.
605
606The alist is a mapping of Postgres type names (strings) to procedures
607accepting a string and returning a Scheme object of the desired type.
608
609The parsers can also be set per connection with the {{TYPE-PARSERS}}
610argument of the {{connect}} procedure.
611
612<enscript highlight="scheme">
613(use postgresql)
614
615(parameterize ((default-type-parsers `(("text" . ,string->symbol))))
616  (let ((conn (connect '((dbname . test)))))
617    (symbol? (value-at (query conn "SELECT 'hello'::text")))))
618 => #t
619</enscript>
620
621The default parsers look like this:
622
623<enscript highlight=scheme>
624`(("text" . ,identity)
625  ("bytea" . ,bytea-parser)
626  ("char" . ,char-parser)
627  ("bpchar" . ,identity)
628  ("bool" . ,bool-parser)
629  ("int8" . ,numeric-parser)
630  ("int4" . ,numeric-parser)
631  ("int2" . ,numeric-parser)
632  ("float4" . ,numeric-parser)
633  ("float8" . ,numeric-parser)
634  ("numeric" . ,numeric-parser)
635  ("oid" . ,numeric-parser)
636  ("record" . ,(make-composite-parser (circular-list identity))))
637</enscript>
638
639These parsers are described below.  For anything where no parser is
640found, the value is returned verbatim (which is always a string, or a
641blob in case of binary data).
642
643Array and composite (row) types are automatically handled; unless a
644type-specific parser is defined, a parser is automatically created by
645combining the parsers for their constituent elements.
646
647<procedure>(update-type-parsers! CONN [TYPE-PARSERS])</procedure>
648
649As described above, type information is extracted from the system
650catalog whenever you initiate a new connection.  However, there is a
651bootstrapping problem when you are defining custom data types.  You
652must first connect before you can define your custom data types.  But
653the type parsers do not have the information for this new type yet, so
654you must update them.
655
656To do this, you can call {{update-type-parsers!}}.  This procedure
657updates all the type parsers originally associated with connection
658{{CONN}}.  By providing the optional {{TYPE-PARSERS}}, you can
659override the existing type parsers for this connection with new ones,
660otherwise the old ones are just refreshed.
661
662<procedure>(bool-parser STR)</procedure>
663
664Returns {{#t}} if the string equals {{"t"}}, {{#f}} otherwise.
665
666<procedure>(bytea-parser STR)</procedure>
667
668Returns a u8vector containing the bytes in STR, after unescaping it
669using {{unescape-bytea}}.
670
671<procedure>(char-parser STR)</procedure>
672
673Returns the first character in STR.
674
675<procedure>(numeric-parser STR)</procedure>
676
677Returns {{STR}} converted to a number using decimal representation.
678If {{STR}} could not be converted to a number, raises an error.
679
680<procedure>(make-array-parser SUBPARSER [DELIMITER])</procedure>
681
682Returns a procedure that can be used to parse arrays containing
683elements that {{SUBPARSER}} parses.  It will split the elements using
684the {{DELIMITER}} character, which defaults to {{#\,}}.
685
686For example, to create a parser for arrays of integers, use
687{{(make-array-parser numeric-parser)}}.
688
689<procedure>(make-composite-parser SUBPARSERS)</procedure>
690
691Returns a procedure that can be used to parse composite values (aka
692"row values").  It will use the list of {{SUBPARSERS}} to parse each
693element in the row by looking up the parser at the matching position
694in the list.  For example, to create a parser for rows containing an
695integer and a boolean, use
696{{(make-composite-parser (list numeric-parser bool-parser))}}.
697
698===== Unparsers
699
700<parameter>(default-type-unparsers [ALIST])</parameter>
701
702Just as PostgreSQL types are converted to Scheme types in result sets,
703Scheme types need to be converted to PostgreSQL types when providing
704positional parameters to queries.  For this, the library uses type
705unparsers.  Just like type parsers, you can override them either
706per-connection using the {{TYPE-UNPARSERS}} parameter to the
707{{connect}} procedure, or globally by changing a parameter.
708
709This alist is a mapping of predicates to unparsers.  Predicates are
710procedures which accept a scheme object and return a true value if the
711object is of the type for which the unparser is intended.  Unparsers
712are procedures which accept two arguments; the connection object and
713the scheme object to unparse.  Unparsers return either a string, a blob
714or an sql-null object to be used in the query.
715
716It is not necessary to reload type unparsers after defining a new data
717type in the database.
718
719Order matters; the type unparser alist is traversed from left to
720right, trying predicates in order and invoking the unparser linked to
721the first predicate that does not return {{#f}}.  If none of the
722predicates match, the type must be of string, blob or sql-null type.
723If not, the query procedure will raise an error.
724
725The default unparsers look like this:
726
727<enscript highlight=scheme>
728`((,string? . ,(lambda (conn s) s))
729  (,u8vector? . ,(lambda (conn v) (u8vector->blob/shared v)))
730  (,char? . ,(lambda (conn c) (string c)))
731  (,boolean? . ,bool-unparser)
732  (,number? . ,(lambda (conn n) (number->string n)))
733  (,vector? . ,vector-unparser)
734  (,pair? . ,list-unparser))
735</enscript>
736
737<procedure>(update-type-unparsers! CONN TYPE-UNPARSERS)</procedure>
738
739Similar to {{update-type-parsers!}}, this procedure allows you to
740update all the type unparsers originally associated with connection
741{{CONN}}.
742
743<procedure>(bool-unparser CONN B)</procedure>
744
745Returns {{"TRUE"}} for true values and {{"FALSE"}} for {{#f}}.
746
747<procedure>(vector-unparser CONN V)</procedure>
748
749Returns a string representing an array containing the objects in the
750vector {{V}}.  The elements of {{V}} are unparsed recursively by their
751respective subparsers.  It is the responsibility of the program to use
752correct values for an array; the elements should all be of the same
753type and, if they are vectors themselves, all vectors should have the
754same length and recursive vector depth.  Otherwise, you will get an
755error from postgresql.
756
757<procedure>(list-unparser CONN L)</procedure>
758
759Returns a string representing a composite object (aka row value)
760containing the objects in the list {{L}}.  The elements of {{L}} are
761unparsed recursively by their respective subparsers.
762
763
764=== Changelog
765
766* 3.6 Fix problem with built-in "name" type which got falsely interpreted as an array (and possibly others as well) [Reported by David Krentzlin]. Fix foreign type signature for {{escape-bytea}} internal function so it correctly accepts bytea values containing NUL bytes (Chicken 4.6.0 and later).
767* 3.5.1 Small change in type information query so it works in Postgres 8.2 (and possibly older versions). [Thanks to Alaric Snell-Pym and Florian Zumbiehl]
768* 3.5 Add support for composite and array types.  Change semantics of 'raw' option to {{query*}} procedure. Backwards incompatible change; pass connection to all type unparsers.
769* 3.4.1 Improve COPY interface
770* 3.4 Add "raw" option to row-alist for consistency. Fix library flags in setup script [Thanks to Felix]. Add {{COPY}} support. Remove {{multi-query}}.
771* 3.3 - Fix connection over TCP/IP problem
772* 3.2 - Add transaction support.
773* 3.1 - Small (but backwards incompatible) interface improvement: make row and column arguments optional.
774* 3.0 - Port to Chicken 4.  Major code overhaul, backwards incompatible.
775* 2.0.14 - Add PQescapeStringConn functionality to pg:escape-string. Fixed weird compilation issue with SRFI-69 by removing it from USES list.
776* 2.0.12 - added {{pg:named-tuples}} parameter [Contributed by Graham Fawcett]
777* 2.0.11 - added syntax-case requirements to .meta file [Thanks to Michele Simionato]
778* 2.0.10 - adapted to new easyffi usage [Thanks to rreal]
779* 2.0.9 - Yet more improvements to error reporting
780* 2.0.8 - More detailed error information, export {{pg:sql-null-object}}
781* 2.0.7 - Added missing error-function [Thanks to Reed Sheridan]
782* 2.0.6 - Removed dependency on format [Thanks to Reed Sheridan]
783* 2.0.5 - Some bugfixes and {{pq:escape-string}} by Reed Sheridan; adapted to SRFI-69 hash-tables
784* 2.0.4 - Changed usage of hygienic macros in setup script
785* 2.0.3 - Bugfixes.
786* 2.0.0 - Interface improvements.  (Backward-incompatible.)
787* 1.2.1 - Non-blocking queries.
788* 1.2.0 - Optimizations, minor fixes and cleanups.
789
790=== License
791
792  Copyright (C) 2008-2009 Peter Bex
793  Copyright (C) 2004 Johannes GrÞdem <johs@copyleft.no>
794  Redistribution and use in source and binary forms, with or without
795  modification, is permitted.
796 
797  THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS
798  OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
799  WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
800  ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE
801  LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
802  CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
803  OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
804  BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
805  LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
806  (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
807  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
808  DAMAGE.
Note: See TracBrowser for help on using the repository browser.