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

Last change on this file since 30642 was 30642, checked in by sjamaan, 7 years ago

Update postgresql changelog

File size: 41.4 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.  The symbols should be
47connection keywords recognized by PostgreSQL's connection function.
48See the [[http://www.postgresql.org/docs/current/interactive/libpq-connect.html|list of PQconnectdbParams parameter keywords]] in the PostgreSQL documentation.
49At the time of writing, they are {{host}}, {{hostaddr}}, {{port}}, {{dbname}}, {{user}}, {{password}}, {{connect_timeout}}, {{options}}, {{sslmode}}, {{service}}.
50
51If any parameter is not specified, or the {{CONNECTION-SPEC}} was
52not supplied at all, the regular libpq rules apply.  That means it
53falls back to checking various
54[[http://www.postgresql.org/docs/current/interactive/libpq-envars.html|environment variables]]
55and settings from
56[[http://www.postgresql.org/docs/current/interactive/libpq-pgservice.html|pg_service.conf]],
57as well as passwords from [[http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html|pgpass]].
58This is to be preferred over hardcoding connection settings in your code.
59
60Using an alist for {{CONNECTION-SPEC}} is recommended; when available
61(when using libpq from Postgres 9.0 or later), PQconnectStartParams
62will be used.  This prevents parsing errors when keys or values
63contain "special" characters like equals signs or single quotes.  This
64also adds a layer of security for when connection specifier components
65come from an untrusted source.
66
67{{TYPE-PARSERS}} is an optional alist that maps PostgreSQL type names
68to parser procedures, TYPE-UNPARSERS is an optional alist that maps
69predicates to unparser procedures.  They default to
70{{(default-type-parsers)}} and {{(default-type-unparsers)}},
71respectively (see [[#type-conversion|below]]).
72
73The return value is a connection-object.
74
75'''Important''': You cannot use the same connection from multiple
76threads.  If you need to talk to the same server from different
77threads, simply create a second connection.
78
79Also note that while these bindings use the non-blocking interface to
80connect to PostgreSQL, if you specify a hostname (using the
81{{host}}-keyword), the function might not be able to yield because the
82resolver will '''block'''.  If you don't want this, you'd have to call
83some kind of custom asynchronous resolver.
84
85
86<procedure>(disconnect CONNECTION)</procedure>
87
88Disconnects from the given {{CONNECTION}}.
89
90<procedure>(reset-connection CONNECTION)</procedure>
91
92Resets, that is, reopens the connection with the same connection-specs
93as was given when opening the original {{CONNECTION}}.
94
95<procedure>(type-parsers CONNECTION)</procedure>
96
97Retrieve the alist of type parsers associated with the {{CONNECTION}}.
98
99<procedure>(type-unparsers CONNECTION)</procedure>
100
101Retrieve the alist of type unparsers associated with the {{CONNECTION}}.
102
103<procedure>(connection? OBJECT)</procedure>
104
105Returns true if OBJECT is a PostgreSQL connection-object.
106
107<procedure>(connected? CONNECTION)</procedure>
108
109Returns true if the {{CONNECTION}} represents an open connection.
110
111==== Query procedures
112
113<procedure>(query CONN QUERY . PARAMS)</procedure>
114
115Execute {{QUERY}} on connection {{CONN}} and return a result object.
116The result object can be read out by several procedures, ranging from
117the low-level {{value-at}} to the high-level {{row-fold}}.  See the
118[[#high-level-api|High-level API]] and
119[[#low-level-result-api|Low-level result API]] sections below for
120information on how to read out result objects.
121
122{{QUERY}} can either be a string or a symbol.  If it is a string, it
123should contain an SQL query to execute.  This query can contain
124placeholders like {{$1}}, {{$2}} etc, which refer to positional
125arguments in {{PARAMS}}.  For example:
126
127<enscript highlight="scheme">
128(use postgresql)
129
130(let ((conn (connect '((dbname . test)))))
131  (row-values (query conn "SELECT $1::text, 2::int2" "hello")))
132 => ("hello" 2)
133</enscript>
134
135If {{QUERY}} is a symbol, it must match the name of a prepared
136statement you created earlier.  The number of parameters passed as
137{{PARAMS}} must match the number of placeholders used when the
138statement was prepared.
139
140To actually create a prepared statement, you can simply use the
141{{query}} procedure with an SQL {{PREPARE}} statement.  The
142placeholders in that statement are deferred until execute time.  It
143allows ''no'' parameters to be sent at preparation time, which is a
144limitation in the PostgreSQL protocol itself.  You could use
145{{escape-string}} if you really ''must'' pass in dynamic values when
146preparing the statement.
147
148<enscript highlight="scheme">
149(use postgresql)
150
151(let ((conn (connect '((dbname . test)))))
152  ;; Can't pass any arguments here:
153  (query conn "PREPARE mystmt (text, int) AS SELECT $1, $2")
154  ;; They are deferred until statement execution time:
155  (row-values (query conn 'mystmt "hi" 42)))
156 => ("hi" 42)  ; a list of all values at row 0 (see "row-values" below)
157
158(let ((conn (connect '((dbname . test)))))
159  ;; If we absolutely need dynamic values, we can escape them manually:
160  (query conn (sprintf "PREPARE mystmt (text) AS SELECT $1, '~A', 'bar'"
161                       (escape-string conn "foo")))
162  (row-values (query conn 'mystmt "hi")))
163 => ("hi" "foo" "bar")
164</enscript>
165
166As you can see from the examples above, PostgreSQL types are
167automatically converted to corresponding Scheme types.  This can be
168extended to support your own user-defined types, see
169[[#type-conversion|the section about type-conversion]] below for more
170information on how to do that.
171
172<procedure>(query* CONN QUERY [PARAMS] [format: FORMAT] [raw: RAW?])</procedure>
173
174A less convenient but slightly more powerful version of the {{query}}
175procedure; {{PARAMS}} must now be a list (instead of rest-arguments).
176{{FORMAT}} is a symbol specifying how to return the resultset: either
177as {{binary}} or {{text}} (the default).  {{RAW}} is a boolean which
178defines whether the {{PARAMS}} should be treated "raw" or first passed
179through the unparsers associated with {{CONN}}.  If they are treated
180"raw", they must all be strings, blobs or sql-null objects.
181
182See [[#type-conversion|type conversion]] for more info on unparsers.
183
184<enscript highlight="scheme">
185(use postgresql)
186
187(let ((conn (connect '((dbname . test)))))
188  (row-map*
189    (lambda (a b) (list (blob->string a) (blob->u8vector b)))
190    (query* conn "SELECT $1::text, 2::int2" '("hello") format: 'binary)))
191 => (("hello" #u8(0 2)))
192</enscript>
193
194==== High-level API
195
196Usually you will use only these procedures to process result sets,
197but you can fall back to (or even mix with) the low-level API if
198you need to do custom things.
199
200<procedure>(row-fold KONS KNIL RESULT)</procedure><br>
201<procedure>(row-fold* KONS KNIL RESULT)</procedure><br>
202
203This is the fundamental result set iterator. It calls
204{{(kons row seed)}} for every row, where {{row}} is the list of
205values in the current row and {{seed}} is the accumulated result
206from previous calls (initially {{knil}}), ie its pattern looks
207like {{(KONS ROWN ... (KONS ROW2 (KONS ROW1 KNIL)))}}.  It returns
208the final accumulated result.
209
210The starred version works the same, except it calls
211{{(kons rowN-col1 rowN-col2 ... seed)}} instead of {{(kons rowN seed)}},
212so the procedure must know how many columns you have in the result set.
213
214<enscript highlight="scheme">
215(use postgresql)
216
217(let ((conn (connect '((dbname . test)))))
218   (row-fold (lambda (row sum) (+ (car row) sum))
219             0
220             (query conn "SELECT 1 UNION SELECT 2")))
221 => 3
222
223(let ((conn (connect '((dbname . test)))))
224   (row-fold* (lambda (value str) (string-append str value))
225              ""
226              (query conn "SELECT 'hello, ' UNION SELECT 'world'")))
227 => "hello, world"
228</enscript>
229
230<procedure>(column-fold KONS KNIL RESULT)</procedure><br>
231<procedure>(column-fold* KONS KNIL RESULT)</procedure><br>
232
233As {{row-fold}}/{{row-fold*}}, except this iterates sideways through
234the columns instead of lengthways through the columns, calling
235{{KONS}} with all values in all the rows of the current column, from
236left to right.
237
238The starred version is much less useful here since you often don't
239know the number of returned columns, but it is provided for consistency.
240
241<enscript highlight="scheme">
242(use postgresql)
243
244(let ((conn (connect '((dbname . test)))))
245   (column-fold (lambda (col sum) (+ (car col) sum))
246                0
247                (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
248 => 101
249</enscript>
250
251<procedure>(row-fold-right KONS KNIL RESULT)</procedure><br>
252<procedure>(row-fold-right* KONS KNIL RESULT)</procedure><br>
253
254The fundamental result set recursion operator;  Calls
255{{(KONS COL1 (KONS COL2 (KONS ... KNIL)))}} instead of
256{{(KONS COLN ... (KONS COL2 (KONS COL1 KNIL)))}}.
257
258<procedure>(column-fold-right KONS KNIL RESULT)</procedure><br>
259<procedure>(column-fold-right* KONS KNIL RESULT)</procedure><br>
260
261Column variants of {{row-fold-right}}/{{row-fold-right*}}.
262
263<procedure>(row-for-each PROC RESULT)</procedure><br>
264<procedure>(row-for-each* PROC RESULT)</procedure><br>
265
266Simple {{for-each}}, calling the {{(PROC row)}} on each row, in turn,
267only for the purpose of its side-effects.  The starred version calls
268{{(PROC col1 col2 ...)}}.
269
270<enscript highlight="scheme">
271(use postgresql)
272
273(let ((conn (connect '((dbname . test)))))
274   (row-for-each write (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
275; prints:  (1 100) (2 200)
276</enscript>
277
278<procedure>(column-for-each PROC RESULT)</procedure><br>
279<procedure>(column-for-each* PROC RESULT)</procedure><br>
280
281Column variants of {{row-for-each}}/{{row-for-each*}}.
282
283<procedure>(row-map PROC RESULT)</procedure><br>
284<procedure>(row-map* PROC RESULT)</procedure><br>
285
286Maps rows to lists by applying {{PROC}} to every row and using its
287result in the result list on the position corresponding to that of the
288row.  This procedure is not guaranteed to walk the result set in any
289particular order, so do ''not'' rely on the order {{PROC}} will be
290called.
291
292<enscript highlight="scheme">
293(use postgresql)
294
295(let ((conn (connect '((dbname . test)))))
296   (row-map* + (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
297 => (101 202)
298</enscript>
299
300<procedure>(column-map PROC RESULT)</procedure><br>
301<procedure>(column-map* PROC RESULT)</procedure><br>
302
303Column variants of {{row-map}}/{{row-map*}}.
304
305<enscript highlight="scheme">
306(use postgresql)
307
308(let ((conn (connect '((dbname . test)))))
309   (column-map* + (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
310 => (3 300)
311</enscript>
312
313===== Transaction management
314
315<procedure>(with-transaction CONN THUNK [isolation: LEVEL] [access: MODE])</procedure>
316
317Execute {{THUNK}} within a {{BEGIN TRANSACTION}} block, and return
318the value of {{thunk}}.
319
320The transaction is committed if {{thunk}} returns a true value.  If an
321exception occurs during {{thunk}}, or {{thunk}} returns {{#f}}, or the
322commit fails, the transaction will be rolled back. If this rollback
323fails, that is a critical error and you should likely abort.
324
325Nested applications of {{with-transaction}} '''are''' supported --
326only those statements executed within {{THUNK}} are committed or
327rolled back by any {{with-transaction}} call, as you would expect.
328
329However, escaping or re-entering the dynamic extent of {{thunk}} will
330not commit or rollback the in-progress transaction, so it is highly
331discouraged to jump out of a transaction.  You will definitely run
332into trouble, unless you can ensure that no other statements will be
333executed on this connection until the outermost {{with-transaction}}
334returns normally.
335
336If you provide {{LEVEL}} (which can be the symbol {{read-committed}}
337or {{serializable}}) this will set the transaction isolation mode for
338the transaction.  If you provide {{MODE}} (which can be the symbol
339{{read/write}} or {{read-only}}) this will set the access mode for the
340transaction.
341
342{{LEVEL}} is only allowed in the outermost transaction (when
343{{in-transaction?}} returns {{#f}}); if you provide it in an inner
344transaction, an error is raised.  In subtransactions, {{MODE}} can
345only be "downgraded" to {{read-only}} from inside a {{read/write}}
346transaction, but you can't "upgrade" to {{read/write}} from a
347{{read-only}} transaction.
348
349<procedure>(in-transaction? CONN)</procedure>
350
351Returns {{#t}} if there is currently a transaction in progress on the
352connection {{CONN}}.  Returns {{#f}} if no transaction is in progress.
353
354==== Low-level result API
355
356This API allows you to inspect result objects on the individual row
357and column level.
358
359<procedure>(result? OBJ)</procedure>
360
361Returns {{#t}} when {{OBJ}} is a result object, {{#f}} otherwise.
362
363<procedure>(clear-result! RES)</procedure>
364
365Directly clean up all memory used by the result object.  This is
366normally deferred until garbage collection, but it's made available
367for when you want more control over when results are released.
368
369<procedure>(value-at RES [COLUMN [ROW]] [raw: RAW])</procedure>
370
371Returns the value at the specified {{COLUMN}} and {{ROW}}.  It is
372parsed by an appropriate parser unless {{RAW}} is specified and
373{{#t}}.  If {{RAW}} is true, the value is either a string, blob or an
374sql-null object.  Otherwise, it depends on the parsers.
375
376If {{ROW}} or {{COLUMN}} are not specified, they default to zero.
377This makes for more convenient syntax if you're just reading out
378a result of a query which always has one row or even one value.
379
380See [[#type-conversion|type conversion]] for more info on parsers.
381
382<procedure>(row-values RES [ROW] [raw: RAW])</procedure>
383
384Returns a list of all the columns' values at the given {{ROW}} number.
385If {{ROW}} is omitted, it defaults to zero.  If {{RAW}} is true, the
386values are either strings, blobs or sql-null objects.  Otherwise, it
387depends on the parsers.
388
389<procedure>(column-values RES [COLUMN] [raw: RAW])</procedure>
390
391Returns a list of all the rows' values at the given {{COLUMN}} number.
392If {{COLUMN}} is omitted, it defaults to zero.  If {{RAW}} is true, the
393values are either strings, blobs or sql-null objects.  Otherwise, it
394depends on the parsers.
395
396<procedure>(row-alist RES [ROW] [raw: RAW])</procedure>
397
398Returns an alist of the values at the given {{ROW}} number.  The keys
399of the alist are made up by the matching column names, as symbols.
400
401If {{ROW}} is omitted, it defaults to zero.  If {{RAW}} is true, the
402values are either strings, blobs or sql-null objects.  Otherwise, it
403depends on the parsers.
404
405<procedure>(affected-rows RES)</procedure>
406
407For INSERT or UPDATE statements, this returns the number of rows
408affected by the statement that RES is a result for.  Otherwise it's
409zero.
410
411<procedure>(inserted-oid RES)</procedure>
412
413For INSERT statements resulting in a single record being inserted,
414this returns the OID (a number) assigned to the newly inserted row.
415Returns {{#f}} for non-INSERT or multi-row INSERTs, or INSERTs into
416tables without OIDs.
417
418<procedure>(row-count RES)</procedure>
419
420Returns the number of rows in the result set.
421
422<procedure>(column-count RES)</procedure>
423
424Returns the number of columns in the result set.
425
426<procedure>(column-index RES COLUMN)</procedure>
427
428Returns the index of {{COLUMN}} in the result set. {{COLUMN}} should
429be a symbol indicating the column name.
430
431<procedure>(column-name RES INDEX)</procedure>
432
433Returns the name of the column (a symbol) at the position in the
434result set specified by {{INDEX}}.  This is its aliased name in the
435result set.
436
437<procedure>(column-names RES)</procedure>
438
439Returns a list of all the column names (symbols) in the result set.
440The position in the list reflects the position of the column in the
441result set.
442
443<procedure>(column-format RES INDEX)</procedure>
444
445Returns the format of the column at {{INDEX}}, which is a symbol;
446Either {{text}} or {{binary}}.  This determines whether the value
447returned by {{value-at}} will be a string or a blob.
448
449<procedure>(column-type RES INDEX)</procedure>
450
451Returns the OID (an integer) of the column at {{INDEX}}.
452
453<procedure>(column-type-modifier RES INDEX)</procedure>
454
455Returns an type-specific modifier (a number), or {{#f}} if the type
456has no modifier.
457
458<procedure>(table-oid RES INDEX)</procedure>
459
460The OID (a number) of the table from whence the result column at
461{{INDEX}} originated, or {{#f}} if the column is not a simple
462reference to a table column.
463
464<procedure>(table-column-index RES INDEX)</procedure>
465
466Returns the column number (within its table) of the column making up
467the query result column at the position specified by {{INDEX}}.
468
469'''Note:''' This procedure returns indexes starting at zero, as one
470would expect.  However, the underlying C function {{PQftablecol}} is
471one-based.  This might trip up experienced Postgres hackers.
472
473
474==== Value escaping
475
476To embed arbitrary values in query strings, you must escape them
477first, to protect yourself from SQL injection bugs.  This is not
478required if you use positional arguments (the {{PARAMS}} arguments
479in the {{query}} procedures).
480
481<procedure>(escape-string CONNECTION STRING)</procedure>
482
483Escapes special characters in {{STRING}} which are otherwise
484interpreted by the SQL parser, obeying the {{CONNECTION}}'s encoding
485and escaping settings, using the escaping syntax for string contexts.
486This does '''NOT''' add surrounding quotes to the string; that's up
487to you to add.
488
489Example:
490
491<enscript highlight=scheme>
492;; This prevents people from changing a query's parse tree.
493;; For example, they could try to turn a query like
494;; SELECT * FROM USERS WHERE id='x'
495;;    into
496;; SELECT * FROM USERS WHERE id='1' OR '1'='1'
497;; by quoting the value for X, you get the intended parse tree:
498;; SELECT * FROM USERS WHERE id='1''' OR ''1''=''1'
499(escape-string conn "1' OR '1'='1") => "1'' OR ''1''=''1"
500
501;; Depending on the value of standard_conforming_strings you might also get
502(escape-string conn "1' OR '1'='1") => "1\\' OR \\'1\\'=\\'1"
503
504;; Of course, when using these strings you still need to surround
505;; the output of escape-string with single quotes
506</enscript>
507
508<procedure>(quote-identifier CONNECTION STRING)</procedure>
509
510Escapes special characters in {{STRING}} which are otherwise
511interpreted by the SQL parser, obeying the {{CONNECTION}}'s encoding
512settings and escaping settings, using the escaping syntax for
513identifier context.  Identifiers are table names, aliases etc.
514Surrounding double quotes will be added.
515
516This procedure corresponds to PQescapeIdentifier, but the name was
517changed to reflect the fact that it performs escaping ''and'' adds
518quotation marks around the string.
519
520'''NOTE''': This procedure is only available when the egg is built
521against the libpq from PostgreSQL 9.0 or later.  If you are using an
522older version, this will raise a {{(exn postgresql unsupported-version)}}
523error with an upgrade message.
524
525Example:
526
527<enscript highlight=scheme>
528;; Spaces are normally not allowed in table names, but when you
529;; quote them, they are allowed
530(quote-identifier conn "a table with spaces") => "\"a table with spaces\""
531
532;; Can't use a column or table called order because it is a
533;; reserved word. However, escaping it makes it usable.
534(quote-identifier conn "order") => "\"order\""
535
536;; Table names are case-insensitive and always implicitly downcased.
537;; If you need to access a table with a capital in its name,
538;; quoting the table also helps:
539(quote-identifier conn "Foo") => "\"Foo\""
540</enscript>
541
542<procedure>(escape-bytea CONNECTION OBJ)</procedure>
543
544Quotes special characters in {{OBJ}} (a string, blob or srfi-4
545vector), which would otherwise be interpreted by the SQL parser.  This
546differs from {{escape-string}} in that some bytes are doubly encoded
547so they can be used for bytea columns.
548
549This is required because of a technicality; PostgreSQL first parses
550the string value as a string, and then casts this string to bytea,
551interpreting another layer of escape codes.
552
553For example, {{E'a\\000bcd'}} is first converted to {{'a\000bcd'}} by
554the text parser, and then interpreted by the bytea parser as an "a"
555followed by a NUL byte, followed by "bcd".  In Scheme, the value
556returned by {{(escape-bytea conn "a\x00bcd")}} is {{"a\\\\000bcd"}}.
557Yes, that's a lot of backslashes :)
558
559<procedure>(unescape-bytea STRING)</procedure>
560
561This unescapes a bytea '''result''' from the server.  It is '''not the
562inverse of {{escape-bytea}}''', because string values returned by the
563server are not escaped for the text-parser. (ie, step one in the encoding
564process described under {{escape-bytea}} is skipped)
565
566The result type of {{unescape-bytea}} is a u8vector (in earlier
567versions, this was a string).
568
569==== COPY support
570
571===== High-level COPY API
572
573This API is ''experimental'' and as such should be expected to change.
574If you have suggestions on how to improve the API, please let me know!
575
576<procedure>(copy-query-fold KONS KNIL CONNECTION QUERY . PARAMS)</procedure><br>
577<procedure>(copy-query*-fold KONS KNIL CONNECTION QUERY [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
578
579This is the fundamental {{COPY TO STDOUT}} iterator. It calls
580{{(kons data seed)}} for every row of COPY data returned by {{QUERY}},
581where {{data}} is either a string or a blob depending on whether
582the {{COPY}} query asked for binary or text data and {{seed}} is the
583accumulated result from previous calls (initially {{knil}}), ie its
584pattern looks like {{(KONS DATAN ... (KONS DATA2 (KONS DATA1 KNIL)))}}.
585It returns the final accumulated result.
586
587The starred and nonstarred version are analogous to {{query}} and {{query*}}.
588
589<enscript highlight="scheme">
590(use postgresql)
591
592(let ((conn (connect '((dbname . test)))))
593  (copy-query-fold
594    (lambda (data sum)
595      ;; Note the string-drop-right is necessary because the default
596      ;; COPY format uses newlines to indicate row endings
597      (+ (string->number (string-drop-right data 1)) sum))
598    0 conn "COPY (SELECT 1 UNION SELECT 2) TO STDOUT"))
599 => 3
600</enscript>
601
602<procedure>(copy-query-fold-right KONS KNIL CONNECTION . PARAMS)</procedure><br>
603<procedure>(copy-query*-fold-right KONS KNIL CONNECTION [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
604
605The fundamental {{COPY TO STDOUT}} recursion operator;  Calls
606{{(KONS DATA1 (KONS DATA2 (KONS ... KNIL)))}} instead of
607{{(KONS DATAN ... (KONS DATA2 (KONS DATA1 KNIL)))}}.
608
609'''Warning''': It is not recommended to use this when the returned
610data is very big.  It is usually much cheaper (memory-wise) to use
611{{copy-query-fold}} and reverse the result object, if the object's
612type supports that.
613
614The starred and nonstarred version are analogous to {{query}} and {{query*}}.
615
616<procedure>(copy-query-map PROC CONNECTION . PARAMS)</procedure><br>
617<procedure>(copy-query*-map PROC CONNECTION [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
618
619Maps {{COPY TO STDOUT}} output rows from {{QUERY}} to lists by calling
620{{PROC}} on each data row returned by the server.  If the {{QUERY}}
621asked for binary data, the data supplied to {{PROC}} will be in
622blob form.  Otherwise, the data will be provided as strings.
623
624The starred and nonstarred version are analogous to {{query}} and {{query*}}.
625
626<enscript highlight="scheme">
627(use postgresql)
628
629(let ((conn (connect '((dbname . test)))))
630   (copy-map (lambda (x) (apply + (map string->number (string-split x))))
631             conn "COPY (SELECT 1, 100 UNION SELECT 2, 200) TO STDOUT"))
632 => (101 202)
633</enscript>
634
635<procedure>(copy-query-for-each PROC CONNECTION . PARAMS)</procedure><br>
636<procedure>(copy-query*-for-each PROC CONNECTION [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
637
638Simple {{for-each}}, calling the {{(PROC data)}} on each row of
639{{COPY TO STDOUT}} data returned by {{QUERY}}, in turn, only for the
640purpose of its side-effects.
641
642The starred and nonstarred version are analogous to {{query}} and {{query*}}.
643
644<procedure>(with-output-to-copy-query THUNK CONNECTION QUERY . PARAMS)</procedure><br>
645<procedure>(with-output-to-copy-query* THUNK CONNECTION QUERY [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
646
647Call {{THUNK}} with {{CURRENT-OUTPUT-PORT}} parameterized to a port
648that writes {{COPY FROM STDIN}} data to the database connection
649{{CONN}} for {{QUERY}}.
650
651Returns the final result of {{THUNK}}.
652
653The starred and nonstarred version are analogous to {{query}} and {{query*}}.
654
655<enscript highlight="scheme">
656(use postgresql)
657
658(with-output-to-copy-query
659  (lambda () (print "one\t1") (print "two\t2") "That's a wrap")
660  conn "COPY my_table (textcol, numbercol) FROM STDIN")
661 => "That's a wrap"
662</enscript>
663
664<procedure>(call-with-output-copy-query PROC CONNECTION QUERY . PARAMS)</procedure><br>
665<procedure>(call-with-output-copy-query* PROC CONNECTION QUERY [PARAMS] [format: FORMAT] [raw: RAW?])</procedure><br>
666
667Like {{with-output-to-copy-query}}, except it calls {{PROC}} with one
668argument (the copy port) instead of parameterizing {{CURRENT-OUTPUT-PORT}}.
669
670
671===== Low-level COPY API
672
673This API is close to the C API.  It requires you to first execute a
674COPY query (using the {{query}} procedure), and then you can put or
675get data from the connection.  You cannot run other queries while the
676connection is in COPY state.
677
678<procedure>(put-copy-data CONNECTION DATA)</procedure>
679
680Put copy data on the {{CONNECTION}}. {{DATA}} is either a string, a
681blob or a srfi-4 vector and should be in the format expected by the server.
682
683<procedure>(put-copy-end CONNECTION [ERROR-MESSAGE])</procedure>
684
685This ends the COPY process.  If {{ERROR-MESSAGE}} is supplied and not
686{{#f}}, the data sent up till now is discarded by the server and an
687error message is triggered on the server.  If {{ERROR-MESSAGE}} is not
688supplied or {{#f}}, the server will commit the copied data to the
689target table and succeed.
690
691A result object is returned upon success.  This result object
692is currently not useful.
693
694<procedure>(get-copy-data CONNECTION [format: FORMAT])</procedure>
695
696Obtain one row of COPY data from the server.  The data's contents will
697be in the format indicated by the server.  If FORMAT is {{'text}}, it
698the data will be returned as a string, if it is {{'binary}}, it will
699be returned as a blob.  The user is responsible for providing the
700right format to match the output format of the query sent earlier.
701
702After the last row is received, this procedure returns a result object
703(which can be detected by calling {{result?}} on it).
704
705==== Constants
706
707<constant>invalid-oid</constant>
708
709Represents the numeric value of the invalid Oid.  Rarely useful,
710except perhaps when doing low-level operations in the system catalog.
711
712==== Error handling
713
714  condition: postgresql
715
716A condition of kind {{(exn <subtype> postgresql)}} is signaled when an
717error occurs.  The {{<subtype>}} is one of the following:
718
719; {{query}} : There was an error while executing a statement or query.
720; {{parse}} : Something went wrong in a parser.
721; {{unparse}} : Ssomething went wrong in an unparser.
722; {{i/o}} : Something went wrong while trying to read from or write to the connection.
723; {{connect}} : Something went wrong during (re)connections.  This includes errors during connection reset.
724; {{bounds}} : An out of bounds error happened (e.g., trying to read from a nonexistant column or row index).
725; {{type}} : Invalid type was passed by the user.
726; {{domain}} : A value was passed in an inappropriate context.
727; {{unsupported-version}} : An operation was performed which is not supported by the client library.
728; {{internal}} : A truly unexpected error occurred (unrecognised status codes, etc).
729
730There will always be a subtype.  If the condition contains a {{query}}
731condition, this holds a lot of extra properties which you can extract
732to gain more information about the cause of the error, or to display
733to the user.
734
735You'll always find all of these properties in the {{query}} component
736of the condition, but most may have a {{#f}} value.
737
738; {{severity}} : One of the symbols {{error}}, {{fatal}}, {{panic}}, {{warning}}, {{notice}}, {{debug}}, {{info}}, {{log}} (unfortunately, this symbol may also be translated/localised, so you should not dispatch on them in code: use error-class and error-code for that). Always present in {{query}} type subconditions.
739; {{error-class}} : A string representing a Postgresql error class (the first two characters of {{error-code}}).  Always present in {{query}} type subconditions.
740; {{error-code}} :  A string representing the full Postgresql error code (including the code class prefix).  See the [[http://www.postgresql.org/docs/8.3/static/errcodes-appendix.html|Postgresql documentation]] for a description of error codes and error classes. Always present in {{query}} type subconditions.
741; {{message-primary}} : The main error message.  Always present in {{query}} type subconditions.  The {{exn}} message will be a combination of this plus the optional detail and hint.
742; {{message-detail}} :  A secondary message with extra detail about the problem.
743; {{message-hint}} :  A string with a suggestion about what to do about the problem.
744; {{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.
745; {{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.
746; {{source-file}} :  The file name of the Postgresql source-code location where the error was reported.
747; {{source-line}} :  The line number in {{source-file}} where the error was reported (integer).
748; {{source-function}} :  The name of the source-code function reporting the error.
749; {{internal-query}} : A string containing the source text of an "internally generated" command where the error occurred (for example when you called a PL/PGSQL function which generates a query).
750; {{internal-position}} : An integer indicating the position in {{internal-query}} where the error occurred.
751; {{schema-name}}: The name of the schema ("database") in which the error occurred.  This is only available when the error is associated with a specific database object.
752; {{table-name}}: The name of the table in which the error occurred.  This is only available when the error is associated with a specific table.
753; {{column-name}}: The name of the column on which the error occurred.  This is only available when the error is associated with a specific column.
754; {{datatype-name}}: The name of the data type on which the error occurred.  This is only available when the error is associated with a specific data type (eg, a custom domain).
755; {{constraint-name}}: The name of the constraint which was violated, if any.
756
757==== Type conversion
758
759Type information is read from the database the first time you connect
760to it.  Result set values are either text or binary (or sql null).  If
761they are text, they are converted to Scheme objects by type parsers,
762as described below.  If they are binary, they will be returned as
763unprocessed blobs (which you can then convert to u8vectors or strings).
764
765===== Parsers
766
767<parameter>(default-type-parsers [ALIST])</parameter>
768
769Postgres result values are always just strings, but it is possible to
770map these to real Scheme objects.  With this parameter, you can
771map your own custom postgresql datatype to Scheme datatypes.
772
773The alist is a mapping of Postgres type names (strings) to procedures
774accepting a string and returning a Scheme object of the desired type.
775
776The parsers can also be set per connection with the {{TYPE-PARSERS}}
777argument of the {{connect}} procedure.
778
779<enscript highlight="scheme">
780(use postgresql)
781
782(parameterize ((default-type-parsers `(("text" . ,string->symbol))))
783  (let ((conn (connect '((dbname . test)))))
784    (symbol? (value-at (query conn "SELECT 'hello'::text")))))
785 => #t
786</enscript>
787
788The default parsers look like this:
789
790<enscript highlight=scheme>
791`(("text" . ,identity)
792  ("bytea" . ,bytea-parser)
793  ("char" . ,char-parser)
794  ("bpchar" . ,identity)
795  ("bool" . ,bool-parser)
796  ("int8" . ,numeric-parser)
797  ("int4" . ,numeric-parser)
798  ("int2" . ,numeric-parser)
799  ("float4" . ,numeric-parser)
800  ("float8" . ,numeric-parser)
801  ("numeric" . ,numeric-parser)
802  ("oid" . ,numeric-parser)
803  ("record" . ,(make-composite-parser (circular-list identity))))
804</enscript>
805
806These parsers are described below.  For anything where no parser is
807found, the value is returned verbatim (which is always a string, or a
808blob in case of binary data).
809
810Array and composite (row) types are automatically handled; unless a
811type-specific parser is defined, a parser is automatically created by
812combining the parsers for their constituent elements.
813
814<procedure>(update-type-parsers! CONN [TYPE-PARSERS])</procedure>
815
816As described above, type information is extracted from the system
817catalog whenever you initiate a new connection.  However, there is a
818bootstrapping problem when you are defining custom data types.  You
819must first connect before you can define your custom data types.  But
820the type parsers do not have the information for this new type yet, so
821you must update them.
822
823To do this, you can call {{update-type-parsers!}}.  This procedure
824updates all the type parsers originally associated with connection
825{{CONN}}.  By providing the optional {{TYPE-PARSERS}}, you can
826override the existing type parsers for this connection with new ones,
827otherwise the old ones are just refreshed.
828
829<procedure>(bool-parser STR)</procedure>
830
831Returns {{#t}} if the string equals {{"t"}}, {{#f}} otherwise.
832
833<procedure>(bytea-parser STR)</procedure>
834
835Returns a u8vector containing the bytes in STR, after unescaping it
836using {{unescape-bytea}}.
837
838<procedure>(char-parser STR)</procedure>
839
840Returns the first character in STR.
841
842<procedure>(numeric-parser STR)</procedure>
843
844Returns {{STR}} converted to a number using decimal representation.
845If {{STR}} could not be converted to a number, raises an error.
846
847<procedure>(make-array-parser SUBPARSER [DELIMITER])</procedure>
848
849Returns a procedure that can be used to parse arrays containing
850elements that {{SUBPARSER}} parses.  It will split the elements using
851the {{DELIMITER}} character, which defaults to {{#\,}}.
852
853For example, to create a parser for arrays of integers, use
854{{(make-array-parser numeric-parser)}}.
855
856<procedure>(make-composite-parser SUBPARSERS)</procedure>
857
858Returns a procedure that can be used to parse composite values (aka
859"row values").  It will use the list of {{SUBPARSERS}} to parse each
860element in the row by looking up the parser at the matching position
861in the list.  For example, to create a parser for rows containing an
862integer and a boolean, use
863{{(make-composite-parser (list numeric-parser bool-parser))}}.
864
865===== Unparsers
866
867<parameter>(default-type-unparsers [ALIST])</parameter>
868
869Just as PostgreSQL types are converted to Scheme types in result sets,
870Scheme types need to be converted to PostgreSQL types when providing
871positional parameters to queries.  For this, the library uses type
872unparsers.  Just like type parsers, you can override them either
873per-connection using the {{TYPE-UNPARSERS}} parameter to the
874{{connect}} procedure, or globally by changing a parameter.
875
876This alist is a mapping of predicates to unparsers.  Predicates are
877procedures which accept a scheme object and return a true value if the
878object is of the type for which the unparser is intended.  Unparsers
879are procedures which accept two arguments; the connection object and
880the scheme object to unparse.  Unparsers return either a string, a blob
881or an sql-null object to be used in the query.
882
883It is not necessary to reload type unparsers after defining a new data
884type in the database.
885
886Order matters; the type unparser alist is traversed from left to
887right, trying predicates in order and invoking the unparser linked to
888the first predicate that does not return {{#f}}.  If none of the
889predicates match, the type must be of string, blob or sql-null type.
890If not, the query procedure will raise an error.
891
892The default unparsers look like this:
893
894<enscript highlight=scheme>
895`((,string? . ,(lambda (conn s) s))
896  (,u8vector? . ,(lambda (conn v) (u8vector->blob/shared v)))
897  (,char? . ,(lambda (conn c) (string c)))
898  (,boolean? . ,bool-unparser)
899  (,number? . ,(lambda (conn n) (number->string n)))
900  (,vector? . ,vector-unparser)
901  (,pair? . ,list-unparser))
902</enscript>
903
904<procedure>(update-type-unparsers! CONN TYPE-UNPARSERS)</procedure>
905
906Similar to {{update-type-parsers!}}, this procedure allows you to
907update all the type unparsers originally associated with connection
908{{CONN}}.
909
910<procedure>(bool-unparser CONN B)</procedure>
911
912Returns {{"TRUE"}} for true values and {{"FALSE"}} for {{#f}}.
913
914<procedure>(vector-unparser CONN V)</procedure>
915
916Returns a string representing an array containing the objects in the
917vector {{V}}.  The elements of {{V}} are unparsed recursively by their
918respective subparsers.  It is the responsibility of the program to use
919correct values for an array; the elements should all be of the same
920type and, if they are vectors themselves, all vectors should have the
921same length and recursive vector depth.  Otherwise, you will get an
922error from postgresql.
923
924<procedure>(list-unparser CONN L)</procedure>
925
926Returns a string representing a composite object (aka row value)
927containing the objects in the list {{L}}.  The elements of {{L}} are
928unparsed recursively by their respective subparsers.
929
930
931=== Changelog
932
933* 3.9.1 Fix escape-string so it doesn't return strings with trailing whitespace when there's nothing to escape.
934* 3.9 Remove deprecated properties on {{postgresql}} condition. Add support for diagnostic schema info on errors. Add {{connected?}} procedure.
935* 3.8 Rip out bogus test for {{reset-connection}}, thanks to "Herr".  Improve error handling.  Deprecate properties on {{postgresql}} condition (they're now properties on {{query}}, instead).  Several performance improvements.  {{unescape-bytea}} now returns blobs, {{escape-bytea}} accepts blobs, strings and srfi-4 vectors.
936* 3.7.2 Again some tweaks for MacOS X build, thanks to [[/users/kon-lovett|Kon Lovett]].
937* 3.7.1 Fix installation on MacOS (with latest XCode?).  Thanks to [[/users/kon-lovett|Kon Lovett]] for reporting this.
938* 3.7 Add {{quote-identifier}} and use {{PQconnectStartParams}} where available, add basic support for executing prepared statements.  Add transaction isolation and access mode keywords to {{with-transaction}}.
939* 3.6.3 Restore compatibility with PostgreSQL 8.3 and older versions [Thanks to Mario Goulart].
940* 3.6.2 Fix tests for PostgreSQL 9.0, prefer one null column over zero columns when faced with ambiguous composite value output syntax "{{()}}".
941* 3.6.1 Fix a small problem in the way composite values are parsed so that trailing NULL values are properly deserialized.
942* 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).
943* 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]
944* 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.
945* 3.4.1 Improve COPY interface
946* 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}}.
947* 3.3 - Fix connection over TCP/IP problem
948* 3.2 - Add transaction support.
949* 3.1 - Small (but backwards incompatible) interface improvement: make row and column arguments optional.
950* 3.0 - Port to Chicken 4.  Major code overhaul, backwards incompatible.
951* 2.0.14 - Add PQescapeStringConn functionality to pg:escape-string. Fixed weird compilation issue with SRFI-69 by removing it from USES list.
952* 2.0.12 - added {{pg:named-tuples}} parameter [Contributed by Graham Fawcett]
953* 2.0.11 - added syntax-case requirements to .meta file [Thanks to Michele Simionato]
954* 2.0.10 - adapted to new easyffi usage [Thanks to rreal]
955* 2.0.9 - Yet more improvements to error reporting
956* 2.0.8 - More detailed error information, export {{pg:sql-null-object}}
957* 2.0.7 - Added missing error-function [Thanks to Reed Sheridan]
958* 2.0.6 - Removed dependency on format [Thanks to Reed Sheridan]
959* 2.0.5 - Some bugfixes and {{pq:escape-string}} by Reed Sheridan; adapted to SRFI-69 hash-tables
960* 2.0.4 - Changed usage of hygienic macros in setup script
961* 2.0.3 - Bugfixes.
962* 2.0.0 - Interface improvements.  (Backward-incompatible.)
963* 1.2.1 - Non-blocking queries.
964* 1.2.0 - Optimizations, minor fixes and cleanups.
965
966=== License
967
968  Copyright (C) 2008-2014 Peter Bex
969  Copyright (C) 2004 Johannes GrÞdem <johs@copyleft.no>
970  Redistribution and use in source and binary forms, with or without
971  modification, is permitted.
972 
973  THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS
974  OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
975  WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
976  ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE
977  LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
978  CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
979  OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
980  BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
981  LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
982  (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
983  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
984  DAMAGE.
Note: See TracBrowser for help on using the repository browser.