|
442
|
1 @c -*-texinfo-*-
|
|
|
2 @c This is part of the XEmacs Lisp Reference Manual.
|
|
|
3 @c Copyright (C) 2000 Electrotechnical Laboratory, JAPAN
|
|
|
4 @c Licensed to the Free Software Foundation
|
|
|
5 @c See the file lispref.texi for copying conditions.
|
|
|
6 @c Thank you Oscar Figueiredo! This file was shamelessly cloned from
|
|
|
7 @c ldap.texi.
|
|
|
8 @setfilename ../../info/postgresql.info
|
|
|
9 @node PostgreSQL Support, Internationalization, LDAP Support, top
|
|
|
10 @chapter PostgreSQL Support
|
|
|
11 @cindex PostgreSQL
|
|
|
12
|
|
|
13 XEmacs can be linked with PostgreSQL libpq run-time support to provide
|
|
|
14 relational database access from Emacs Lisp code.
|
|
|
15
|
|
|
16 @menu
|
|
|
17 * Building XEmacs with PostgreSQL support::
|
|
|
18 * XEmacs PostgreSQL libpq API::
|
|
|
19 * XEmacs PostgreSQL libpq Examples::
|
|
|
20 @end menu
|
|
|
21
|
|
|
22 @node Building XEmacs with PostgreSQL support, XEmacs PostgreSQL libpq API, ,PostgreSQL Support
|
|
|
23 @comment node-name, next, previous, up
|
|
|
24 @section Building XEmacs with PostgreSQL support
|
|
|
25
|
|
|
26 XEmacs PostgreSQL support requires linking to the PostgreSQL libpq
|
|
|
27 library. Describing how to build and install PostgreSQL is beyond the
|
|
|
28 scope of this document. See the PostgreSQL manual for details.
|
|
|
29
|
|
|
30 If you have installed XEmacs from one of the binary kits on
|
|
|
31 (@url{ftp://ftp.xemacs.org/}), or are using an XEmacs binary from a CD
|
|
|
32 ROM, you may have XEmacs PostgreSQL support by default. @code{M-x
|
|
|
33 describe-installation} will tell you if you do.
|
|
|
34
|
|
|
35 If you are building XEmacs from source, you need to install PostgreSQL
|
|
|
36 first. On some systems, PostgreSQL will come pre-installed in /usr. In
|
|
|
37 this case, it should be autodetected when you run configure. If
|
|
|
38 PostgreSQL is installed into its default location,
|
|
|
39 @file{/usr/local/pgsql}, you must specify
|
|
|
40 @code{--site-prefixes=/usr/local/pgsql} when you run configure. If
|
|
|
41 PostgreSQL is installed into another location, use that instead of
|
|
|
42 @file{/usr/local/pgsql} when specifying @code{--site-prefixes}.
|
|
|
43
|
|
|
44 As of XEmacs 21.2, PostgreSQL versions 6.5.3 and 7.0 are supported.
|
|
|
45 XEmacs Lisp support for V7.0 is somewhat more extensive than support for
|
|
|
46 V6.5. In particular, asynchronous queries are supported.
|
|
|
47
|
|
|
48 @node XEmacs PostgreSQL libpq API, XEmacs PostgreSQL libpq Examples, Building XEmacs with PostgreSQL support, PostgreSQL Support
|
|
|
49 @comment node-name, next, previous, up
|
|
|
50 @section XEmacs PostgreSQL libpq API
|
|
|
51
|
|
|
52 The XEmacs PostgreSQL API is intended to be a policy-free, low-level
|
|
|
53 binding to libpq. The intent is to provide all the basic functionality
|
|
|
54 and then let high level Lisp code decide its own policies.
|
|
|
55
|
|
|
56 This documentation assumes that the reader has knowledge of SQL, but
|
|
|
57 requires no prior knowledge of libpq.
|
|
|
58
|
|
|
59 There are many examples in this manual and some setup will be required.
|
|
|
60 In order to run most of the following examples, the following code needs
|
|
|
61 to be executed. In addition to the data is in this table, nearly all of
|
|
|
62 the examples will assume that the free variable @code{P} refers to this
|
|
|
63 database connection. The examples in the original edition of this
|
|
|
64 manual were run against Postgres 7.0beta1.
|
|
|
65
|
|
|
66 @example
|
|
|
67 (progn
|
|
|
68 (setq P (pq-connectdb ""))
|
|
|
69 ;; id is the primary key, shikona is a Japanese word that
|
|
|
70 ;; means `the professional name of a Sumo wrestler', and
|
|
|
71 ;; rank is the Sumo rank name.
|
|
|
72 (pq-exec P (concat "CREATE TABLE xemacs_test"
|
|
|
73 " (id int, shikona text, rank text);"))
|
|
|
74 (pq-exec P "COPY xemacs_test FROM stdin;")
|
|
|
75 (pq-put-line P "1\tMusashimaru\tYokuzuna\n")
|
|
|
76 (pq-put-line P "2\tDejima\tOozeki\n")
|
|
|
77 (pq-put-line P "3\tMusoyama\tSekiwake\n")
|
|
|
78 (pq-put-line P "4\tMiyabiyama\tSekiwake\n")
|
|
|
79 (pq-put-line P "5\tWakanoyama\tMaegashira\n")
|
|
|
80 (pq-put-line P "\\.\n")
|
|
|
81 (pq-end-copy P))
|
|
|
82 @result{} nil
|
|
|
83 @end example
|
|
|
84
|
|
|
85 @menu
|
|
|
86 * libpq Lisp Variables::
|
|
|
87 * libpq Lisp Symbols and DataTypes::
|
|
|
88 * Synchronous Interface Functions::
|
|
|
89 * Asynchronous Interface Functions::
|
|
|
90 * Large Object Support::
|
|
|
91 * Other libpq Functions::
|
|
|
92 * Unimplemented libpq Functions::
|
|
|
93 @end menu
|
|
|
94
|
|
|
95 @node libpq Lisp Variables, libpq Lisp Symbols and DataTypes, XEmacs PostgreSQL libpq API, XEmacs PostgreSQL libpq API
|
|
|
96 @comment node-name, next, previous, up
|
|
|
97 @subsection libpq Lisp Variables
|
|
|
98
|
|
|
99 Various Unix environment variables are used by libpq to provide defaults
|
|
|
100 to the many different parameters. In the XEmacs Lisp API, these
|
|
|
101 environment variables are bound to Lisp variables to provide more
|
|
|
102 convenient access to Lisp Code. These variables are passed to the
|
|
|
103 backend database server during the establishment of a database
|
|
|
104 connection and when the @code{pq-setenv} call is made.
|
|
|
105
|
|
|
106 @defvar pg:host
|
|
1738
|
107 Initialized from the @code{PGHOST} environment variable. The default
|
|
442
|
108 host to connect to.
|
|
|
109 @end defvar
|
|
|
110
|
|
|
111 @defvar pg:user
|
|
1738
|
112 Initialized from the @code{PGUSER} environment variable. The default
|
|
442
|
113 database user name.
|
|
|
114 @end defvar
|
|
|
115
|
|
|
116 @defvar pg:options
|
|
1738
|
117 Initialized from the @code{PGOPTIONS} environment variable. Default
|
|
442
|
118 additional server options.
|
|
|
119 @end defvar
|
|
|
120
|
|
|
121 @defvar pg:port
|
|
1738
|
122 Initialized from the @code{PGPORT} environment variable. The default
|
|
|
123 TCP port to connect to.
|
|
442
|
124 @end defvar
|
|
|
125
|
|
|
126 @defvar pg:tty
|
|
1738
|
127 Initialized from the @code{PGTTY} environment variable. The default
|
|
442
|
128 debugging TTY.
|
|
|
129
|
|
|
130 Compatibility note: Debugging TTYs are turned off in the XEmacs Lisp
|
|
|
131 binding.
|
|
|
132 @end defvar
|
|
|
133
|
|
|
134 @defvar pg:database
|
|
1738
|
135 Initialized from the @code{PGDATABASE} environment variable. The
|
|
|
136 default database to connect to.
|
|
442
|
137 @end defvar
|
|
|
138
|
|
|
139 @defvar pg:realm
|
|
1738
|
140 Initialized from the @code{PGREALM} environment variable. The default
|
|
442
|
141 Kerberos realm.
|
|
|
142 @end defvar
|
|
|
143
|
|
|
144 @defvar pg:client-encoding
|
|
1738
|
145 Initialized from the @code{PGCLIENTENCODING} environment variable. The
|
|
442
|
146 default client encoding.
|
|
|
147
|
|
|
148 Compatibility note: This variable is not present in non-Mule XEmacsen.
|
|
|
149 This variable is not present in versions of libpq prior to 7.0.
|
|
|
150 In the current implementation, client encoding is equivalent to the
|
|
|
151 @code{file-name-coding-system} format.
|
|
|
152 @end defvar
|
|
|
153
|
|
|
154 @c unused
|
|
|
155 @defvar pg:authtype
|
|
1738
|
156 Initialized from the @code{PGAUTHTYPE} environment variable. The
|
|
|
157 default authentication scheme used.
|
|
442
|
158
|
|
|
159 Compatibility note: This variable is unused in versions of libpq after
|
|
|
160 6.5. It is not implemented at all in the XEmacs Lisp binding.
|
|
|
161 @end defvar
|
|
|
162
|
|
|
163 @defvar pg:geqo
|
|
1738
|
164 Initialized from the @code{PGGEQO} environment variable. Genetic
|
|
442
|
165 optimizer options.
|
|
|
166 @end defvar
|
|
|
167
|
|
|
168 @defvar pg:cost-index
|
|
1738
|
169 Initialized from the @code{PGCOSTINDEX} environment variable. Cost
|
|
|
170 index options.
|
|
442
|
171 @end defvar
|
|
|
172
|
|
|
173 @defvar pg:cost-heap
|
|
1738
|
174 Initialized from the @code{PGCOSTHEAP} environment variable. Cost heap
|
|
442
|
175 options.
|
|
|
176 @end defvar
|
|
|
177
|
|
|
178 @defvar pg:tz
|
|
1738
|
179 Initialized from the @code{PGTZ} environment variable. Default
|
|
442
|
180 timezone.
|
|
|
181 @end defvar
|
|
|
182
|
|
|
183 @defvar pg:date-style
|
|
1738
|
184 Initialized from the @code{PGDATESTYLE} environment variable. Default
|
|
442
|
185 date style in returned date objects.
|
|
|
186 @end defvar
|
|
|
187
|
|
|
188 @defvar pg-coding-system
|
|
|
189 This is a variable controlling which coding system is used to encode
|
|
|
190 non-ASCII strings sent to the database.
|
|
|
191
|
|
|
192 Compatibility Note: This variable is not present in InfoDock.
|
|
|
193 @end defvar
|
|
|
194
|
|
|
195 @node libpq Lisp Symbols and DataTypes, Synchronous Interface Functions, libpq Lisp Variables, XEmacs PostgreSQL libpq API
|
|
|
196 @comment node-name, next, previous, up
|
|
|
197 @subsection libpq Lisp Symbols and Datatypes
|
|
|
198
|
|
|
199 The following set of symbols are used to represent the intermediate
|
|
|
200 states involved in the asynchronous interface.
|
|
|
201
|
|
|
202 @defvr {Symbol} pgres::polling-failed
|
|
|
203 Undocumented. A fatal error has occurred during processing of an
|
|
|
204 asynchronous operation.
|
|
|
205 @end defvr
|
|
|
206
|
|
|
207 @defvr {Symbol} pgres::polling-reading
|
|
|
208 An intermediate status return during an asynchronous operation. It
|
|
|
209 indicates that one may use @code{select} before polling again.
|
|
|
210 @end defvr
|
|
|
211
|
|
|
212 @defvr {Symbol} pgres::polling-writing
|
|
|
213 An intermediate status return during an asynchronous operation. It
|
|
|
214 indicates that one may use @code{select} before polling again.
|
|
|
215 @end defvr
|
|
|
216
|
|
|
217 @defvr {Symbol} pgres::polling-ok
|
|
|
218 An asynchronous operation has successfully completed.
|
|
|
219 @end defvr
|
|
|
220
|
|
|
221 @defvr {Symbol} pgres::polling-active
|
|
|
222 An intermediate status return during an asynchronous operation. One can
|
|
|
223 call the poll function again immediately.
|
|
|
224 @end defvr
|
|
|
225
|
|
|
226 @defun pq-pgconn conn field
|
|
|
227 @var{conn} A database connection object.
|
|
|
228 @var{field} A symbol indicating which field of PGconn to fetch. Possible
|
|
|
229 values are shown in the following table.
|
|
|
230 @table @code
|
|
|
231 @item pq::db
|
|
|
232 Database name
|
|
|
233 @item pq::user
|
|
|
234 Database user name
|
|
|
235 @item pq::pass
|
|
|
236 Database user's password
|
|
|
237 @item pq::host
|
|
|
238 Hostname database server is running on
|
|
|
239 @item pq::port
|
|
|
240 TCP port number used in the connection
|
|
|
241 @item pq::tty
|
|
|
242 Debugging TTY
|
|
|
243
|
|
|
244 Compatibility note: Debugging TTYs are not used in the XEmacs Lisp API.
|
|
|
245 @item pq::options
|
|
|
246 Additional server options
|
|
|
247 @item pq::status
|
|
|
248 Connection status. Possible return values are shown in the following
|
|
|
249 table.
|
|
|
250 @table @code
|
|
|
251 @item pg::connection-ok
|
|
|
252 The normal, connected status.
|
|
|
253 @item pg::connection-bad
|
|
|
254 The connection is not open and the PGconn object needs to be deleted by
|
|
|
255 @code{pq-finish}.
|
|
|
256 @item pg::connection-started
|
|
|
257 An asynchronous connection has been started, but is not yet complete.
|
|
|
258 @item pg::connection-made
|
|
|
259 An asynchronous connect has been made, and there is data waiting to be sent.
|
|
|
260 @item pg::connection-awaiting-response
|
|
|
261 Awaiting data from the backend during an asynchronous connection.
|
|
|
262 @item pg::connection-auth-ok
|
|
|
263 Received authentication, waiting for the backend to start up.
|
|
|
264 @item pg::connection-setenv
|
|
|
265 Negotiating environment during an asynchronous connection.
|
|
|
266 @end table
|
|
|
267 @item pq::error-message
|
|
|
268 The last error message that was delivered to this connection.
|
|
|
269 @item pq::backend-pid
|
|
|
270 The process ID of the backend database server.
|
|
|
271 @end table
|
|
|
272 @end defun
|
|
|
273
|
|
|
274 The @code{PGresult} object is used by libpq to encapsulate the results
|
|
|
275 of queries. The printed representation takes on four forms. When the
|
|
|
276 PGresult object contains tuples from an SQL @code{SELECT} it will look
|
|
|
277 like:
|
|
|
278
|
|
|
279 @example
|
|
|
280 (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
|
|
|
281 @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
|
|
|
282 @end example
|
|
|
283
|
|
|
284 The number in brackets indicates how many rows of data are available.
|
|
|
285 When the PGresult object is the result of a command query that doesn't
|
|
|
286 return anything, it will look like:
|
|
|
287
|
|
|
288 @example
|
|
|
289 (pq-exec P "CREATE TABLE a_new_table (i int);")
|
|
|
290 @result{} #<PGresult PGRES_COMMAND_OK - CREATE>
|
|
|
291 @end example
|
|
|
292
|
|
|
293 When either the query is a command-type query that can affect a number
|
|
|
294 of different rows, but doesn't return any of them it will look like:
|
|
|
295
|
|
|
296 @example
|
|
|
297 (progn
|
|
|
298 (pq-exec P "INSERT INTO a_new_table VALUES (1);")
|
|
|
299 (pq-exec P "INSERT INTO a_new_table VALUES (2);")
|
|
|
300 (pq-exec P "INSERT INTO a_new_table VALUES (3);")
|
|
|
301 (setq R (pq-exec P "DELETE FROM a_new_table;")))
|
|
|
302 @result{} #<PGresult PGRES_COMMAND_OK[3] - DELETE 3>
|
|
|
303 @end example
|
|
|
304
|
|
|
305 Lastly, when the underlying PGresult object has been deallocated
|
|
|
306 directly by @code{pq-clear} the printed representation will look like:
|
|
|
307
|
|
|
308 @example
|
|
|
309 (progn
|
|
|
310 (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
|
|
|
311 (pq-clear R)
|
|
|
312 R)
|
|
|
313 @result{} #<PGresult DEAD>
|
|
|
314 @end example
|
|
|
315
|
|
|
316 The following set of functions are accessors to various data in the PGresult
|
|
|
317 object.
|
|
|
318
|
|
|
319 @defun pq-result-status result
|
|
|
320 Return status of a query result.
|
|
|
321 @var{result} is a PGresult object. The return value is one of the
|
|
|
322 symbols in the following table.
|
|
|
323 @table @code
|
|
|
324 @item pgres::empty-query
|
|
|
325 A query contained no text. This is usually the result of a recoverable
|
|
|
326 error, or a minor programming error.
|
|
|
327 @item pgres::command-ok
|
|
|
328 A query command that doesn't return anything was executed properly by
|
|
|
329 the backend.
|
|
|
330 @item pgres::tuples-ok
|
|
|
331 A query command that returns tuples was executed properly by the
|
|
|
332 backend.
|
|
|
333 @item pgres::copy-out
|
|
|
334 Copy Out data transfer is in progress.
|
|
|
335 @item pgres::copy-in
|
|
|
336 Copy In data transfer is in progress.
|
|
|
337 @item pgres::bad-response
|
|
|
338 An unexpected response was received from the backend.
|
|
|
339 @item pgres::nonfatal-error
|
|
|
340 Undocumented. This value is returned when the libpq function
|
|
1738
|
341 @code{PQresultStatus} is called with a @code{NULL} pointer.
|
|
442
|
342 @item pgres::fatal-error
|
|
|
343 Undocumented. An error has occurred in processing the query and the
|
|
|
344 operation was not completed.
|
|
|
345 @end table
|
|
|
346 @end defun
|
|
|
347
|
|
|
348 @defun pq-res-status result
|
|
|
349 Return the query result status as a string, not a symbol.
|
|
|
350 @var{result} is a PGresult object.
|
|
|
351
|
|
|
352 @example
|
|
|
353 (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
|
|
|
354 @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
|
|
|
355 (pq-res-status R)
|
|
|
356 @result{} "PGRES_TUPLES_OK"
|
|
|
357 @end example
|
|
|
358 @end defun
|
|
|
359
|
|
|
360 @defun pq-result-error-message result
|
|
|
361 Return an error message generated by the query, if any.
|
|
|
362 @var{result} is a PGresult object.
|
|
|
363
|
|
|
364 @example
|
|
|
365 (setq R (pq-exec P "SELECT * FROM xemacs-test;"))
|
|
|
366 @result{} <A fatal error is signaled in the echo area>
|
|
|
367 (pq-result-error-message R)
|
|
|
368 @result{} "ERROR: parser: parse error at or near \"-\"
|
|
|
369 "
|
|
|
370 @end example
|
|
|
371 @end defun
|
|
|
372
|
|
|
373 @defun pq-ntuples result
|
|
|
374 Return the number of tuples in the query result.
|
|
|
375 @var{result} is a PGresult object.
|
|
|
376
|
|
|
377 @example
|
|
|
378 (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
|
|
|
379 @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
|
|
|
380 (pq-ntuples R)
|
|
|
381 @result{} 5
|
|
|
382 @end example
|
|
|
383 @end defun
|
|
|
384
|
|
|
385 @defun pq-nfields result
|
|
|
386 Return the number of fields in each tuple of the query result.
|
|
|
387 @var{result} is a PGresult object.
|
|
|
388
|
|
|
389 @example
|
|
|
390 (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
|
|
|
391 @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
|
|
|
392 (pq-nfields R)
|
|
|
393 @result{} 3
|
|
|
394 @end example
|
|
|
395 @end defun
|
|
|
396
|
|
|
397 @defun pq-binary-tuples result
|
|
|
398 Returns t if binary tuples are present in the results, nil otherwise.
|
|
|
399 @var{result} is a PGresult object.
|
|
|
400
|
|
|
401 @example
|
|
|
402 (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
|
|
|
403 @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
|
|
|
404 (pq-binary-tuples R)
|
|
|
405 @result{} nil
|
|
|
406 @end example
|
|
|
407 @end defun
|
|
|
408
|
|
|
409 @defun pq-fname result field-index
|
|
|
410 Returns the name of a specific field.
|
|
|
411 @var{result} is a PGresult object.
|
|
|
412 @var{field-index} is the number of the column to select from. The first
|
|
|
413 column is number zero.
|
|
|
414
|
|
|
415 @example
|
|
|
416 (let (i l)
|
|
|
417 (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
|
|
|
418 (setq i (pq-nfields R))
|
|
|
419 (while (>= (decf i) 0)
|
|
|
420 (push (pq-fname R i) l))
|
|
|
421 l)
|
|
|
422 @result{} ("id" "shikona" "rank")
|
|
|
423 @end example
|
|
|
424 @end defun
|
|
|
425
|
|
|
426 @defun pq-fnumber result field-name
|
|
|
427 Return the field number corresponding to the given field name.
|
|
|
428 -1 is returned on a bad field name.
|
|
|
429 @var{result} is a PGresult object.
|
|
|
430 @var{field-name} is a string representing the field name to find.
|
|
|
431 @example
|
|
|
432 (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
|
|
|
433 @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
|
|
|
434 (pq-fnumber R "id")
|
|
|
435 @result{} 0
|
|
|
436 (pq-fnumber R "Not a field")
|
|
|
437 @result{} -1
|
|
|
438 @end example
|
|
|
439 @end defun
|
|
|
440
|
|
|
441 @defun pq-ftype result field-num
|
|
|
442 Return an integer code representing the data type of the specified column.
|
|
|
443 @var{result} is a PGresult object.
|
|
|
444 @var{field-num} is the field number.
|
|
|
445
|
|
|
446 The return value of this function is the Object ID (Oid) in the database
|
|
|
447 of the type. Further queries need to be made to various system tables
|
|
|
448 in order to convert this value into something useful.
|
|
|
449 @end defun
|
|
|
450
|
|
|
451 @defun pq-fmod result field-num
|
|
|
452 Return the type modifier code associated with a field. Field numbers
|
|
|
453 start at zero.
|
|
|
454 @var{result} is a PGresult object.
|
|
|
455 @var{field-index} selects which field to use.
|
|
|
456 @end defun
|
|
|
457
|
|
|
458 @defun pq-fsize result field-index
|
|
|
459 Return size of the given field.
|
|
|
460 @var{result} is a PGresult object.
|
|
|
461 @var{field-index} selects which field to use.
|
|
|
462
|
|
|
463 @example
|
|
|
464 (let (i l)
|
|
|
465 (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
|
|
|
466 (setq i (pq-nfields R))
|
|
|
467 (while (>= (decf i) 0)
|
|
|
468 (push (list (pq-ftype R i) (pq-fsize R i)) l))
|
|
|
469 l)
|
|
|
470 @result{} ((23 23) (25 25) (25 25))
|
|
|
471 @end example
|
|
|
472 @end defun
|
|
|
473
|
|
|
474 @defun pq-get-value result tup-num field-num
|
|
|
475 Retrieve a return value.
|
|
|
476 @var{result} is a PGresult object.
|
|
|
477 @var{tup-num} selects which tuple to fetch from.
|
|
|
478 @var{field-num} selects which field to fetch from.
|
|
|
479
|
|
|
480 Both tuples and fields are numbered from zero.
|
|
|
481
|
|
|
482 @example
|
|
|
483 (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
|
|
|
484 @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
|
|
|
485 (pq-get-value R 0 1)
|
|
|
486 @result{} "Musashimaru"
|
|
|
487 (pq-get-value R 1 1)
|
|
|
488 @result{} "Dejima"
|
|
|
489 (pq-get-value R 2 1)
|
|
|
490 @result{} "Musoyama"
|
|
|
491 @end example
|
|
|
492 @end defun
|
|
|
493
|
|
|
494 @defun pq-get-length result tup-num field-num
|
|
|
495 Return the length of a specific value.
|
|
|
496 @var{result} is a PGresult object.
|
|
|
497 @var{tup-num} selects which tuple to fetch from.
|
|
|
498 @var{field-num} selects which field to fetch from.
|
|
|
499
|
|
|
500 @example
|
|
|
501 (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
|
|
|
502 @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
|
|
|
503 (pq-get-length R 0 1)
|
|
|
504 @result{} 11
|
|
|
505 (pq-get-length R 1 1)
|
|
|
506 @result{} 6
|
|
|
507 (pq-get-length R 2 1)
|
|
|
508 @result{} 8
|
|
|
509 @end example
|
|
|
510 @end defun
|
|
|
511
|
|
|
512 @defun pq-get-is-null result tup-num field-num
|
|
1738
|
513 Return t if the specific value is the SQL @code{NULL}.
|
|
442
|
514 @var{result} is a PGresult object.
|
|
|
515 @var{tup-num} selects which tuple to fetch from.
|
|
|
516 @var{field-num} selects which field to fetch from.
|
|
|
517 @end defun
|
|
|
518
|
|
|
519 @defun pq-cmd-status result
|
|
|
520 Return a summary string from the query.
|
|
|
521 @var{result} is a PGresult object.
|
|
|
522 @example
|
|
|
523 @comment This example was written on day 3 of the 2000 Haru Basho.
|
|
464
|
524 (setq R (pq-exec P "INSERT INTO xemacs_test
|
|
|
525 VALUES (6, 'Wakanohana', 'Yokozuna');"))
|
|
442
|
526 @result{} #<PGresult PGRES_COMMAND_OK[1] - INSERT 542086 1>
|
|
|
527 (pq-cmd-status R)
|
|
|
528 @result{} "INSERT 542086 1"
|
|
|
529 (setq R (pq-exec P "UPDATE xemacs_test SET rank='retired'
|
|
|
530 WHERE shikona='Wakanohana';"))
|
|
|
531 @result{} #<PGresult PGRES_COMMAND_OK[1] - UPDATE 1>
|
|
|
532 (pq-cmd-status R)
|
|
|
533 @result{} "UPDATE 1"
|
|
|
534 @end example
|
|
|
535
|
|
|
536 Note that the first number returned from an insertion, like in the
|
|
|
537 example, is an object ID number and will almost certainly vary from
|
|
|
538 system to system since object ID numbers in Postgres must be unique
|
|
|
539 across all databases.
|
|
|
540 @end defun
|
|
|
541
|
|
|
542 @defun pq-cmd-tuples result
|
|
|
543 Return the number of tuples if the last command was an INSERT/UPDATE/DELETE.
|
|
|
544 If the last command was something else, the empty string is returned.
|
|
|
545 @var{result} is a PGresult object.
|
|
|
546
|
|
|
547 @example
|
|
|
548 (setq R (pq-exec P "INSERT INTO xemacs_test VALUES
|
|
|
549 (7, 'Takanohana', 'Yokuzuna');"))
|
|
|
550 @result{} #<PGresult PGRES_COMMAND_OK[1] - INSERT 38688 1>
|
|
|
551 (pq-cmd-tuples R)
|
|
|
552 @result{} "1"
|
|
|
553 (setq R (pq-exec P "SELECT * from xemacs_test;"))
|
|
|
554 @result{} #<PGresult PGRES_TUPLES_OK[7] - SELECT>
|
|
|
555 (pq-cmd-tuples R)
|
|
|
556 @result{} ""
|
|
|
557 (setq R (pq-exec P "DELETE FROM xemacs_test
|
|
|
558 WHERE shikona LIKE '%hana';"))
|
|
|
559 @result{} #<PGresult PGRES_COMMAND_OK[2] - DELETE 2>
|
|
|
560 (pq-cmd-tuples R)
|
|
|
561 @result{} "2"
|
|
|
562 @end example
|
|
|
563 @end defun
|
|
|
564
|
|
|
565 @defun pq-oid-value result
|
|
|
566 Return the object id of the insertion if the last command was an INSERT.
|
|
|
567 0 is returned if the last command was not an insertion.
|
|
|
568 @var{result} is a PGresult object.
|
|
|
569
|
|
|
570 In the first example, the numbers you will see on your local system will
|
|
|
571 almost certainly be different, however the second number from the right
|
|
|
572 in the unprintable PGresult object and the number returned by
|
|
|
573 @code{pq-oid-value} should match.
|
|
|
574 @example
|
|
|
575 (setq R (pq-exec P "INSERT INTO xemacs_test VALUES
|
|
|
576 (8, 'Terao', 'Maegashira');"))
|
|
|
577 @result{} #<PGresult PGRES_COMMAND_OK[1] - INSERT 542089 1>
|
|
|
578 (pq-oid-value R)
|
|
|
579 @result{} 542089
|
|
|
580 (setq R (pq-exec P "SELECT shikona FROM xemacs_test
|
|
|
581 WHERE rank='Maegashira';"))
|
|
|
582 @result{} #<PGresult PGRES_TUPLES_OK[2] - SELECT>
|
|
|
583 (pq-oid-value R)
|
|
|
584 @result{} 0
|
|
|
585 @end example
|
|
|
586 @end defun
|
|
|
587
|
|
|
588 @defun pq-make-empty-pgresult conn status
|
|
|
589 Create an empty pgresult with the given status.
|
|
|
590 @var{conn} a database connection object
|
|
|
591 @var{status} a value that can be returned by @code{pq-result-status}.
|
|
|
592
|
|
|
593 The caller is responsible for making sure the return value gets properly
|
|
|
594 freed.
|
|
|
595 @end defun
|
|
|
596
|
|
|
597 @node Synchronous Interface Functions, Asynchronous Interface Functions, libpq Lisp Symbols and DataTypes, XEmacs PostgreSQL libpq API
|
|
|
598 @comment node-name, next, previous, up
|
|
|
599 @subsection Synchronous Interface Functions
|
|
|
600
|
|
|
601 @defun pq-connectdb conninfo
|
|
|
602 Establish a (synchronous) database connection.
|
|
|
603 @var{conninfo} A string of blank separated options. Options are of the
|
|
|
604 form ``@var{option} = @var{value}''. If @var{value} contains blanks, it
|
|
|
605 must be single quoted. Blanks around the equal sign are optional.
|
|
|
606 Multiple option assignments are blank separated.
|
|
|
607 @example
|
|
|
608 (pq-connectdb "dbname=japanese port = 25432")
|
|
|
609 @result{} #<PGconn localhost:25432 steve/japanese>
|
|
|
610 @end example
|
|
|
611 The printed representation of a database connection object has four
|
|
|
612 fields. The first field is the hostname where the database server is
|
|
|
613 running (in this case localhost), the second field is the port number,
|
|
|
614 the third field is the database user name, and the fourth field is the
|
|
|
615 name of the database.
|
|
|
616
|
|
|
617 Database connection objects which have been disconnected and will
|
|
|
618 generate an immediate error if they are used look like:
|
|
|
619 @example
|
|
|
620 #<PGconn BAD>
|
|
|
621 @end example
|
|
|
622 Bad connections can be reestablished with @code{pq-reset}, or deleted
|
|
|
623 entirely with @code{pq-finish}.
|
|
|
624
|
|
|
625 A database connection object that has been deleted looks like:
|
|
|
626 @example
|
|
|
627 (let ((P1 (pq-connectdb "")))
|
|
|
628 (pq-finish P1)
|
|
|
629 P1)
|
|
|
630 @result{} #<PGconn DEAD>
|
|
|
631 @end example
|
|
|
632
|
|
|
633 Note that database connection objects are the most heavy weight objects
|
|
|
634 in XEmacs Lisp at this writing, usually representing as much as several
|
|
|
635 megabytes of virtual memory on the machine the database server is
|
|
|
636 running on. It is wisest to explicitly delete them when you are
|
|
|
637 finished with them, rather than letting garbage collection do it. An
|
|
|
638 example idiom is:
|
|
|
639
|
|
|
640 @example
|
|
|
641 (let ((P (pq-connectiondb "")))
|
|
|
642 (unwind-protect
|
|
|
643 (progn
|
|
|
644 (...)) ; access database here
|
|
|
645 (pq-finish P)))
|
|
|
646 @end example
|
|
|
647
|
|
|
648 The following options are available in the options string:
|
|
|
649 @table @code
|
|
|
650 @item authtype
|
|
1738
|
651 Authentication type. Same as @code{PGAUTHTYPE}. This is no longer used.
|
|
442
|
652 @item user
|
|
1738
|
653 Database user name. Same as @code{PGUSER}.
|
|
442
|
654 @item password
|
|
|
655 Database password.
|
|
|
656 @item dbname
|
|
1738
|
657 Database name. Same as @code{PGDATABASE}
|
|
442
|
658 @item host
|
|
1738
|
659 Symbolic hostname. Same as @code{PGHOST}.
|
|
442
|
660 @item hostaddr
|
|
|
661 Host address as four octets (eg. like 192.168.1.1).
|
|
|
662 @item port
|
|
1738
|
663 TCP port to connect to. Same as @code{PGPORT}.
|
|
442
|
664 @item tty
|
|
1738
|
665 Debugging TTY. Same as @code{PGTTY}. This value is suppressed in the
|
|
442
|
666 XEmacs Lisp API.
|
|
|
667 @item options
|
|
1738
|
668 Extra backend database options. Same as @code{PGOPTIONS}.
|
|
442
|
669 @end table
|
|
|
670 A database connection object is returned regardless of whether a
|
|
|
671 connection was established or not.
|
|
|
672 @end defun
|
|
|
673
|
|
|
674 @defun pq-reset conn
|
|
|
675 Reestablish database connection.
|
|
|
676 @var{conn} A database connection object.
|
|
|
677
|
|
|
678 This function reestablishes a database connection using the original
|
|
|
679 connection parameters. This is useful if something has happened to the
|
|
|
680 TCP link and it has become broken.
|
|
|
681 @end defun
|
|
|
682
|
|
|
683 @defun pq-exec conn query
|
|
|
684 Make a synchronous database query.
|
|
|
685 @var{conn} A database connection object.
|
|
|
686 @var{query} A string containing an SQL query.
|
|
|
687 A PGresult object is returned, which in turn may be queried by its many
|
|
|
688 accessor functions to retrieve state out of it. If the query string
|
|
|
689 contains multiple SQL commands, only results from the final command are
|
|
|
690 returned.
|
|
|
691
|
|
|
692 @example
|
|
|
693 (setq R (pq-exec P "SELECT * FROM xemacs_test;
|
|
|
694 DELETE FROM xemacs_test WHERE id=8;"))
|
|
|
695 @result{} #<PGresult PGRES_COMMAND_OK[1] - DELETE 1>
|
|
|
696 @end example
|
|
|
697 @end defun
|
|
|
698
|
|
|
699 @defun pq-notifies conn
|
|
|
700 Return the latest async notification that has not yet been handled.
|
|
|
701 @var{conn} A database connection object.
|
|
|
702 If there has been a notification, then a list of two elements will be returned.
|
|
|
703 The first element contains the relation name being notified, the second
|
|
|
704 element contains the backend process ID number. nil is returned if there
|
|
|
705 aren't any notifications to process.
|
|
|
706 @end defun
|
|
|
707
|
|
|
708 @defun PQsetenv conn
|
|
|
709 Synchronous transfer of environment variables to a backend
|
|
|
710 @var{conn} A database connection object.
|
|
|
711
|
|
|
712 Environment variable transfer is done as a normal part of database
|
|
|
713 connection.
|
|
|
714
|
|
|
715 Compatibility note: This function was present but not documented in versions
|
|
|
716 of libpq prior to 7.0.
|
|
|
717 @end defun
|
|
|
718
|
|
|
719 @node Asynchronous Interface Functions, Large Object Support, Synchronous Interface Functions, XEmacs PostgreSQL libpq API
|
|
|
720 @comment node-name, next, previous, up
|
|
|
721 @subsection Asynchronous Interface Functions
|
|
|
722
|
|
|
723 Making command by command examples is too complex with the asynchronous
|
|
|
724 interface functions. See the examples section for complete calling
|
|
|
725 sequences.
|
|
|
726
|
|
|
727 @defun pq-connect-start conninfo
|
|
|
728 Begin establishing an asynchronous database connection.
|
|
|
729 @var{conninfo} A string containing the connection options. See the
|
|
|
730 documentation of @code{pq-connectdb} for a listing of all the available
|
|
|
731 flags.
|
|
|
732 @end defun
|
|
|
733
|
|
|
734 @defun pq-connect-poll conn
|
|
|
735 An intermediate function to be called during an asynchronous database
|
|
|
736 connection.
|
|
|
737 @var{conn} A database connection object.
|
|
|
738 The result codes are documented in a previous section.
|
|
|
739 @end defun
|
|
|
740
|
|
|
741 @defun pq-is-busy conn
|
|
|
742 Returns t if @code{pq-get-result} would block waiting for input.
|
|
|
743 @var{conn} A database connection object.
|
|
|
744 @end defun
|
|
|
745
|
|
|
746 @defun pq-consume-input conn
|
|
|
747 Consume any available input from the backend.
|
|
|
748 @var{conn} A database connection object.
|
|
|
749
|
|
|
750 Nil is returned if anything bad happens.
|
|
|
751 @end defun
|
|
|
752
|
|
|
753 @defun pq-reset-start conn
|
|
|
754 Reset connection to the backend asynchronously.
|
|
|
755 @var{conn} A database connection object.
|
|
|
756 @end defun
|
|
|
757
|
|
|
758 @defun pq-reset-poll conn
|
|
|
759 Poll an asynchronous reset for completion
|
|
|
760 @var{conn} A database connection object.
|
|
|
761 @end defun
|
|
|
762
|
|
|
763 @defun pq-reset-cancel conn
|
|
|
764 Attempt to request cancellation of the current operation.
|
|
|
765 @var{conn} A database connection object.
|
|
|
766
|
|
|
767 The return value is t if the cancel request was successfully
|
|
|
768 dispatched, nil if not (in which case conn->errorMessage is set).
|
|
|
769 Note: successful dispatch is no guarantee that there will be any effect at
|
|
|
770 the backend. The application must read the operation result as usual.
|
|
|
771 @end defun
|
|
|
772
|
|
|
773 @defun pq-send-query conn query
|
|
|
774 Submit a query to Postgres and don't wait for the result.
|
|
|
775 @var{conn} A database connection object.
|
|
|
776 Returns: t if successfully submitted
|
|
|
777 nil if error (conn->errorMessage is set)
|
|
|
778 @end defun
|
|
|
779
|
|
|
780 @defun pq-get-result conn
|
|
|
781 Retrieve an asynchronous result from a query.
|
|
|
782 @var{conn} A database connection object.
|
|
|
783
|
|
444
|
784 @code{nil} is returned when no more query work remains.
|
|
442
|
785 @end defun
|
|
|
786
|
|
|
787 @defun pq-set-nonblocking conn arg
|
|
|
788 Sets the PGconn's database connection non-blocking if the arg is TRUE
|
|
|
789 or makes it non-blocking if the arg is FALSE, this will not protect
|
|
|
790 you from PQexec(), you'll only be safe when using the non-blocking API.
|
|
|
791 @var{conn} A database connection object.
|
|
|
792 @end defun
|
|
|
793
|
|
|
794 @defun pq-is-nonblocking conn
|
|
|
795 Return the blocking status of the database connection
|
|
|
796 @var{conn} A database connection object.
|
|
|
797 @end defun
|
|
|
798
|
|
|
799 @defun pq-flush conn
|
|
|
800 Force the write buffer to be written (or at least try)
|
|
|
801 @var{conn} A database connection object.
|
|
|
802 @end defun
|
|
|
803
|
|
|
804 @defun PQsetenvStart conn
|
|
|
805 Start asynchronously passing environment variables to a backend.
|
|
|
806 @var{conn} A database connection object.
|
|
|
807
|
|
|
808 Compatibility note: this function is only available with libpq-7.0.
|
|
|
809 @end defun
|
|
|
810
|
|
|
811 @defun PQsetenvPoll conn
|
|
|
812 Check an asynchronous environment variables transfer for completion.
|
|
|
813 @var{conn} A database connection object.
|
|
|
814
|
|
|
815 Compatibility note: this function is only available with libpq-7.0.
|
|
|
816 @end defun
|
|
|
817
|
|
|
818 @defun PQsetenvAbort conn
|
|
|
819 Attempt to terminate an asynchronous environment variables transfer.
|
|
|
820 @var{conn} A database connection object.
|
|
|
821
|
|
|
822 Compatibility note: this function is only available with libpq-7.0.
|
|
|
823 @end defun
|
|
|
824
|
|
|
825 @node Large Object Support, Other libpq Functions, Asynchronous Interface Functions, XEmacs PostgreSQL libpq API
|
|
|
826 @comment node-name, next, previous, up
|
|
|
827 @subsection Large Object Support
|
|
|
828
|
|
|
829 @defun pq-lo-import conn filename
|
|
|
830 Import a file as a large object into the database.
|
|
|
831 @var{conn} a database connection object
|
|
|
832 @var{filename} filename to import
|
|
|
833
|
|
|
834 On success, the object id is returned.
|
|
|
835 @end defun
|
|
|
836
|
|
|
837 @defun pq-lo-export conn oid filename
|
|
|
838 Copy a large object in the database into a file.
|
|
|
839 @var{conn} a database connection object.
|
|
|
840 @var{oid} object id number of a large object.
|
|
|
841 @var{filename} filename to export to.
|
|
|
842 @end defun
|
|
|
843
|
|
|
844 @node Other libpq Functions, Unimplemented libpq Functions, Large Object Support, XEmacs PostgreSQL libpq API
|
|
|
845 @comment node-name, next, previous, up
|
|
|
846 @subsection Other libpq Functions
|
|
|
847
|
|
|
848 @defun pq-finish conn
|
|
|
849 Destroy a database connection object by calling free on it.
|
|
|
850 @var{conn} a database connection object
|
|
|
851
|
|
|
852 It is possible to not call this routine because the usual XEmacs garbage
|
|
|
853 collection mechanism will call the underlying libpq routine whenever it
|
|
|
854 is releasing stale @code{PGconn} objects. However, this routine is
|
|
|
855 useful in @code{unwind-protect} clauses to make connections go away
|
|
|
856 quickly when unrecoverable errors have occurred.
|
|
|
857
|
|
|
858 After calling this routine, the printed representation of the XEmacs
|
|
|
859 wrapper object will contain the string ``DEAD''.
|
|
|
860 @end defun
|
|
|
861
|
|
|
862 @defun pq-client-encoding conn
|
|
|
863 Return the client encoding as an integer code.
|
|
|
864 @var{conn} a database connection object
|
|
|
865
|
|
|
866 @example
|
|
|
867 (pq-client-encoding P)
|
|
|
868 @result{} 1
|
|
|
869 @end example
|
|
|
870
|
|
|
871 Compatibility note: This function did not exist prior to libpq-7.0 and
|
|
|
872 does not exist in a non-Mule XEmacs.
|
|
|
873 @end defun
|
|
|
874
|
|
|
875 @defun pq-set-client-encoding conn encoding
|
|
|
876 Set client coding system.
|
|
|
877 @var{conn} a database connection object
|
|
|
878 @var{encoding} a string representing the desired coding system
|
|
|
879
|
|
|
880 @example
|
|
|
881 (pq-set-client-encoding P "EUC_JP")
|
|
|
882 @result{} 0
|
|
|
883 @end example
|
|
|
884
|
|
|
885 The current idiom for ensuring proper coding system conversion is the
|
|
|
886 following (illustrated for EUC Japanese encoding):
|
|
|
887 @example
|
|
|
888 (setq P (pq-connectdb "..."))
|
|
|
889 (let ((file-name-coding-system 'euc-jp)
|
|
|
890 (pg-coding-system 'euc-jp))
|
|
|
891 (pq-set-client-encoding "EUC_JP")
|
|
|
892 ...)
|
|
|
893 (pq-finish P)
|
|
|
894 @end example
|
|
|
895 Compatibility note: This function did not exist prior to libpq-7.0 and
|
|
|
896 does not exist in a non-Mule XEmacs.
|
|
|
897 @end defun
|
|
|
898
|
|
|
899 @defun pq-env-2-encoding
|
|
1738
|
900 Return the integer code representing the coding system in
|
|
|
901 @code{PGCLIENTENCODING}.
|
|
442
|
902
|
|
|
903 @example
|
|
|
904 (pq-env-2-encoding)
|
|
|
905 @result{} 0
|
|
|
906 @end example
|
|
|
907 Compatibility note: This function did not exist prior to libpq-7.0 and
|
|
|
908 does not exist in a non-Mule XEmacs.
|
|
|
909 @end defun
|
|
|
910
|
|
|
911 @defun pq-clear res
|
|
|
912 Destroy a query result object by calling free() on it.
|
|
|
913 @var{res} a query result object
|
|
|
914
|
|
|
915 Note: The memory allocation systems of libpq and XEmacs are different.
|
|
|
916 The XEmacs representation of a query result object will have both the
|
|
|
917 XEmacs version and the libpq version freed at the next garbage collection
|
|
|
918 when the object is no longer being referenced. Calling this function does
|
|
|
919 not release the XEmacs object, it is still subject to the usual rules for
|
|
|
920 Lisp objects. The printed representation of the XEmacs object will contain
|
|
|
921 the string ``DEAD'' after this routine is called indicating that it is no
|
|
|
922 longer useful for anything.
|
|
|
923 @end defun
|
|
|
924
|
|
|
925 @defun pq-conn-defaults
|
|
|
926 Return a data structure that represents the connection defaults.
|
|
|
927 The data is returned as a list of lists, where each sublist contains
|
|
|
928 info regarding a single option.
|
|
|
929 @end defun
|
|
|
930
|
|
|
931 @node Unimplemented libpq Functions, , Other libpq Functions, XEmacs PostgreSQL libpq API
|
|
|
932 @comment node-name, next, previous, up
|
|
|
933 @subsection Unimplemented libpq Functions
|
|
|
934
|
|
|
935 @deftypefn {Unimplemented Function} PGconn *PQsetdbLogin (char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName, char *login, char *pwd)
|
|
|
936 Synchronous database connection.
|
|
|
937 @var{pghost} is the hostname of the PostgreSQL backend to connect to.
|
|
|
938 @var{pgport} is the TCP port number to use.
|
|
|
939 @var{pgoptions} specifies other backend options.
|
|
|
940 @var{pgtty} specifies the debugging tty to use.
|
|
|
941 @var{dbName} specifies the database name to use.
|
|
|
942 @var{login} specifies the database user name.
|
|
|
943 @var{pwd} specifies the database user's password.
|
|
|
944
|
|
|
945 This routine is deprecated as of libpq-7.0, and its functionality can be
|
|
|
946 replaced by external Lisp code if needed.
|
|
|
947 @end deftypefn
|
|
|
948
|
|
|
949 @deftypefn {Unimplemented Function} PGconn *PQsetdb (char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName)
|
|
|
950 Synchronous database connection.
|
|
|
951 @var{pghost} is the hostname of the PostgreSQL backend to connect to.
|
|
|
952 @var{pgport} is the TCP port number to use.
|
|
|
953 @var{pgoptions} specifies other backend options.
|
|
|
954 @var{pgtty} specifies the debugging tty to use.
|
|
|
955 @var{dbName} specifies the database name to use.
|
|
|
956
|
|
|
957 This routine was deprecated in libpq-6.5.
|
|
|
958 @end deftypefn
|
|
|
959
|
|
|
960 @deftypefn {Unimplemented Function} int PQsocket (PGconn *conn)
|
|
|
961 Return socket file descriptor to a backend database process.
|
|
|
962 @var{conn} database connection object.
|
|
|
963 @end deftypefn
|
|
|
964
|
|
|
965 @deftypefn {Unimplemented Function} void PQprint (FILE *fout, PGresult *res, PGprintOpt *ps)
|
|
|
966 Print out the results of a query to a designated C stream.
|
|
|
967 @var{fout} C stream to print to
|
|
|
968 @var{res} the query result object to print
|
|
|
969 @var{ps} the print options structure.
|
|
|
970
|
|
|
971 This routine is deprecated as of libpq-7.0 and cannot be sensibly exported
|
|
|
972 to XEmacs Lisp.
|
|
|
973 @end deftypefn
|
|
|
974
|
|
|
975 @deftypefn {Unimplemented Function} void PQdisplayTuples (PGresult *res, FILE *fp, int fillAlign, char *fieldSep, int printHeader, int quiet)
|
|
|
976 @var{res} query result object to print
|
|
|
977 @var{fp} C stream to print to
|
|
|
978 @var{fillAlign} pad the fields with spaces
|
|
|
979 @var{fieldSep} field separator
|
|
|
980 @var{printHeader} display headers?
|
|
|
981 @var{quiet}
|
|
|
982
|
|
|
983 This routine was deprecated in libpq-6.5.
|
|
|
984 @end deftypefn
|
|
|
985
|
|
|
986 @deftypefn {Unimplemented Function} void PQprintTuples (PGresult *res, FILE *fout, int printAttName, int terseOutput, int width)
|
|
|
987 @var{res} query result object to print
|
|
|
988 @var{fout} C stream to print to
|
|
|
989 @var{printAttName} print attribute names
|
|
|
990 @var{terseOutput} delimiter bars
|
|
|
991 @var{width} width of column, if 0, use variable width
|
|
|
992
|
|
|
993 This routine was deprecated in libpq-6.5.
|
|
|
994 @end deftypefn
|
|
|
995
|
|
|
996 @deftypefn {Unimplemented Function} int PQmblen (char *s, int encoding)
|
|
|
997 Determine length of a multibyte encoded char at @code{*s}.
|
|
|
998 @var{s} encoded string
|
|
|
999 @var{encoding} type of encoding
|
|
|
1000
|
|
|
1001 Compatibility note: This function was introduced in libpq-7.0.
|
|
|
1002 @end deftypefn
|
|
|
1003
|
|
|
1004 @deftypefn {Unimplemented Function} void PQtrace (PGconn *conn, FILE *debug_port)
|
|
|
1005 Enable tracing on @code{debug_port}.
|
|
|
1006 @var{conn} database connection object.
|
|
|
1007 @var{debug_port} C output stream to use.
|
|
|
1008 @end deftypefn
|
|
|
1009
|
|
|
1010 @deftypefn {Unimplemented Function} void PQuntrace (PGconn *conn)
|
|
|
1011 Disable tracing.
|
|
|
1012 @var{conn} database connection object.
|
|
|
1013 @end deftypefn
|
|
|
1014
|
|
|
1015 @deftypefn {Unimplemented Function} char *PQoidStatus (PGconn *conn)
|
|
|
1016 Return the object id as a string of the last tuple inserted.
|
|
|
1017 @var{conn} database connection object.
|
|
|
1018
|
|
|
1019 Compatibility note: This function is deprecated in libpq-7.0, however it
|
|
|
1020 is used internally by the XEmacs binding code when linked against versions
|
|
|
1021 prior to 7.0.
|
|
|
1022 @end deftypefn
|
|
|
1023
|
|
|
1024 @deftypefn {Unimplemented Function} PGresult *PQfn (PGconn *conn, int fnid, int *result_buf, int *result_len, int result_is_int, PQArgBlock *args, int nargs)
|
|
|
1025 ``Fast path'' interface --- not really recommended for application use
|
|
|
1026 @var{conn} A database connection object.
|
|
|
1027 @var{fnid}
|
|
|
1028 @var{result_buf}
|
|
|
1029 @var{result_len}
|
|
|
1030 @var{result_is_int}
|
|
|
1031 @var{args}
|
|
|
1032 @var{nargs}
|
|
|
1033 @end deftypefn
|
|
|
1034
|
|
|
1035 The following set of very low level large object functions aren't
|
|
|
1036 appropriate to be exported to Lisp.
|
|
|
1037
|
|
|
1038 @deftypefn {Unimplemented Function} int pq-lo-open (PGconn *conn, int lobjid, int mode)
|
|
|
1039 @var{conn} a database connection object.
|
|
|
1040 @var{lobjid} a large object ID.
|
|
|
1041 @var{mode} opening modes.
|
|
|
1042 @end deftypefn
|
|
|
1043
|
|
|
1044 @deftypefn {Unimplemented Function} int pq-lo-close (PGconn *conn, int fd)
|
|
|
1045 @var{conn} a database connection object.
|
|
|
1046 @var{fd} a large object file descriptor
|
|
|
1047 @end deftypefn
|
|
|
1048
|
|
|
1049 @deftypefn {Unimplemented Function} int pq-lo-read (PGconn *conn, int fd, char *buf, int len)
|
|
|
1050 @var{conn} a database connection object.
|
|
|
1051 @var{fd} a large object file descriptor.
|
|
|
1052 @var{buf} buffer to read into.
|
|
|
1053 @var{len} size of buffer.
|
|
|
1054 @end deftypefn
|
|
|
1055
|
|
|
1056 @deftypefn {Unimplemented Function} int pq-lo-write (PGconn *conn, int fd, char *buf, size_t len)
|
|
|
1057 @var{conn} a database connection object.
|
|
|
1058 @var{fd} a large object file descriptor.
|
|
|
1059 @var{buf} buffer to write from.
|
|
|
1060 @var{len} size of buffer.
|
|
|
1061 @end deftypefn
|
|
|
1062
|
|
|
1063 @deftypefn {Unimplemented Function} int pq-lo-lseek (PGconn *conn, int fd, int offset, int whence)
|
|
|
1064 @var{conn} a database connection object.
|
|
|
1065 @var{fd} a large object file descriptor.
|
|
|
1066 @var{offset}
|
|
|
1067 @var{whence}
|
|
|
1068 @end deftypefn
|
|
|
1069
|
|
|
1070 @deftypefn {Unimplemented Function} int pq-lo-creat (PGconn *conn, int mode)
|
|
|
1071 @var{conn} a database connection object.
|
|
|
1072 @var{mode} opening modes.
|
|
|
1073 @end deftypefn
|
|
|
1074
|
|
|
1075 @deftypefn {Unimplemented Function} int pq-lo-tell (PGconn *conn, int fd)
|
|
|
1076 @var{conn} a database connection object.
|
|
|
1077 @var{fd} a large object file descriptor.
|
|
|
1078 @end deftypefn
|
|
|
1079
|
|
|
1080 @deftypefn {Unimplemented Function} int pq-lo-unlink (PGconn *conn, int lobjid)
|
|
|
1081 @var{conn} a database connection object.
|
|
|
1082 @var{lbojid} a large object ID.
|
|
|
1083 @end deftypefn
|
|
|
1084
|
|
|
1085 @node XEmacs PostgreSQL libpq Examples, , XEmacs PostgreSQL libpq API, PostgreSQL Support
|
|
|
1086 @comment node-name, next, previous, up
|
|
|
1087 @section XEmacs PostgreSQL libpq Examples
|
|
|
1088
|
|
|
1089 This is an example of one method of establishing an asynchronous
|
|
|
1090 connection.
|
|
|
1091
|
|
|
1092 @example
|
|
|
1093 (defun database-poller (P)
|
|
|
1094 (message "%S before poll" (pq-pgconn P 'pq::status))
|
|
|
1095 (pq-connect-poll P)
|
|
|
1096 (message "%S after poll" (pq-pgconn P 'pq::status))
|
|
|
1097 (if (eq (pq-pgconn P 'pq::status) 'pg::connection-ok)
|
|
|
1098 (message "Done!")
|
|
|
1099 (add-timeout .1 'database-poller P)))
|
|
|
1100 @result{} database-poller
|
|
|
1101 (progn
|
|
|
1102 (setq P (pq-connect-start ""))
|
|
|
1103 (add-timeout .1 'database-poller P))
|
|
|
1104 @result{} pg::connection-started before poll
|
|
|
1105 @result{} pg::connection-made after poll
|
|
|
1106 @result{} pg::connection-made before poll
|
|
|
1107 @result{} pg::connection-awaiting-response after poll
|
|
|
1108 @result{} pg::connection-awaiting-response before poll
|
|
|
1109 @result{} pg::connection-auth-ok after poll
|
|
|
1110 @result{} pg::connection-auth-ok before poll
|
|
|
1111 @result{} pg::connection-setenv after poll
|
|
|
1112 @result{} pg::connection-setenv before poll
|
|
|
1113 @result{} pg::connection-ok after poll
|
|
|
1114 @result{} Done!
|
|
|
1115 P
|
|
|
1116 @result{} #<PGconn localhost:25432 steve/steve>
|
|
|
1117 @end example
|
|
|
1118
|
|
|
1119 Here is an example of one method of doing an asynchronous reset.
|
|
|
1120
|
|
|
1121 @example
|
|
|
1122 (defun database-poller (P)
|
|
|
1123 (let (PS)
|
|
|
1124 (message "%S before poll" (pq-pgconn P 'pq::status))
|
|
|
1125 (setq PS (pq-reset-poll P))
|
|
|
1126 (message "%S after poll [%S]" (pq-pgconn P 'pq::status) PS)
|
|
|
1127 (if (eq (pq-pgconn P 'pq::status) 'pg::connection-ok)
|
|
|
1128 (message "Done!")
|
|
|
1129 (add-timeout .1 'database-poller P))))
|
|
|
1130 @result{} database-poller
|
|
|
1131 (progn
|
|
|
1132 (pq-reset-start P)
|
|
|
1133 (add-timeout .1 'database-poller P))
|
|
|
1134 @result{} pg::connection-started before poll
|
|
|
1135 @result{} pg::connection-made after poll [pgres::polling-writing]
|
|
|
1136 @result{} pg::connection-made before poll
|
|
|
1137 @result{} pg::connection-awaiting-response after poll [pgres::polling-reading]
|
|
|
1138 @result{} pg::connection-awaiting-response before poll
|
|
|
1139 @result{} pg::connection-setenv after poll [pgres::polling-reading]
|
|
|
1140 @result{} pg::connection-setenv before poll
|
|
|
1141 @result{} pg::connection-ok after poll [pgres::polling-ok]
|
|
|
1142 @result{} Done!
|
|
|
1143 P
|
|
|
1144 @result{} #<PGconn localhost:25432 steve/steve>
|
|
|
1145 @end example
|
|
|
1146
|
|
|
1147 And finally, an asynchronous query.
|
|
|
1148
|
|
|
1149 @example
|
|
|
1150 (defun database-poller (P)
|
|
|
1151 (let (R)
|
|
|
1152 (pq-consume-input P)
|
|
|
1153 (if (pq-is-busy P)
|
|
|
1154 (add-timeout .1 'database-poller P)
|
|
|
1155 (setq R (pq-get-result P))
|
|
|
1156 (if R
|
|
|
1157 (progn
|
|
|
1158 (push R result-list)
|
|
|
1159 (add-timeout .1 'database-poller P))))))
|
|
|
1160 @result{} database-poller
|
|
|
1161 (when (pq-send-query P "SELECT * FROM xemacs_test;")
|
|
|
1162 (setq result-list nil)
|
|
|
1163 (add-timeout .1 'database-poller P))
|
|
|
1164 @result{} 885
|
|
|
1165 ;; wait a moment
|
|
|
1166 result-list
|
|
|
1167 @result{} (#<PGresult PGRES_TUPLES_OK - SELECT>)
|
|
|
1168 @end example
|
|
|
1169
|
|
|
1170 Here is an example showing how multiple SQL statements in a single query
|
|
|
1171 can have all their results collected.
|
|
|
1172 @example
|
|
|
1173 ;; Using the same @code{database-poller} function from the previous example
|
|
|
1174 (when (pq-send-query P "SELECT * FROM xemacs_test;
|
|
|
1175 SELECT * FROM pg_database;
|
|
|
1176 SELECT * FROM pg_user;")
|
|
|
1177 (setq result-list nil)
|
|
|
1178 (add-timeout .1 'database-poller P))
|
|
|
1179 @result{} 1782
|
|
|
1180 ;; wait a moment
|
|
|
1181 result-list
|
|
|
1182 @result{} (#<PGresult PGRES_TUPLES_OK - SELECT> #<PGresult PGRES_TUPLES_OK - SELECT> #<PGresult PGRES_TUPLES_OK - SELECT>)
|
|
|
1183 @end example
|
|
|
1184
|
|
|
1185 Here is an example which illustrates collecting all data from a query,
|
|
|
1186 including the field names.
|
|
|
1187
|
|
|
1188 @example
|
|
|
1189 (defun pg-util-query-results (results)
|
|
|
1190 "Retrieve results of last SQL query into a list structure."
|
|
|
1191 (let ((i (1- (pq-ntuples R)))
|
|
|
1192 j l1 l2)
|
|
|
1193 (while (>= i 0)
|
|
|
1194 (setq j (1- (pq-nfields R)))
|
|
|
1195 (setq l2 nil)
|
|
|
1196 (while (>= j 0)
|
|
|
1197 (push (pq-get-value R i j) l2)
|
|
|
1198 (decf j))
|
|
|
1199 (push l2 l1)
|
|
|
1200 (decf i))
|
|
|
1201 (setq j (1- (pq-nfields R)))
|
|
|
1202 (setq l2 nil)
|
|
|
1203 (while (>= j 0)
|
|
|
1204 (push (pq-fname R j) l2)
|
|
|
1205 (decf j))
|
|
|
1206 (push l2 l1)
|
|
|
1207 l1))
|
|
|
1208 @result{} pg-util-query-results
|
|
|
1209 (setq R (pq-exec P "SELECT * FROM xemacs_test ORDER BY field2 DESC;"))
|
|
|
1210 @result{} #<PGresult PGRES_TUPLES_OK - SELECT>
|
|
|
1211 (pg-util-query-results R)
|
|
|
1212 @result{} (("f1" "field2") ("a" "97") ("b" "97") ("stuff" "42") ("a string" "12") ("foo" "10") ("string" "2") ("text" "1"))
|
|
|
1213 @end example
|
|
|
1214
|
|
|
1215 Here is an example of a query that uses a database cursor.
|
|
|
1216
|
|
|
1217 @example
|
|
|
1218 (let (data R)
|
|
|
1219 (setq R (pq-exec P "BEGIN;"))
|
|
|
1220 (setq R (pq-exec P "DECLARE k_cursor CURSOR FOR SELECT * FROM xemacs_test ORDER BY f1 DESC;"))
|
|
|
1221
|
|
|
1222 (setq R (pq-exec P "FETCH k_cursor;"))
|
|
|
1223 (while (eq (pq-ntuples R) 1)
|
|
|
1224 (push (list (pq-get-value R 0 0) (pq-get-value R 0 1)) data)
|
|
|
1225 (setq R (pq-exec P "FETCH k_cursor;")))
|
|
|
1226 (setq R (pq-exec P "END;"))
|
|
|
1227 data)
|
|
|
1228 @result{} (("a" "97") ("a string" "12") ("b" "97") ("foo" "10") ("string" "2") ("stuff" "42") ("text" "1"))
|
|
|
1229 @end example
|
|
|
1230
|
|
|
1231 Here's another example of cursors, this time with a Lisp macro to
|
|
|
1232 implement a mapping function over a table.
|
|
|
1233
|
|
|
1234 @example
|
|
|
1235 (defmacro map-db (P table condition callout)
|
|
|
1236 `(let (R)
|
|
|
1237 (pq-exec ,P "BEGIN;")
|
|
|
1238 (pq-exec ,P (concat "DECLARE k_cursor CURSOR FOR SELECT * FROM "
|
|
|
1239 ,table
|
|
|
1240 " "
|
|
|
1241 ,condition
|
|
|
1242 " ORDER BY f1 DESC;"))
|
|
|
1243 (setq R (pq-exec P "FETCH k_cursor;"))
|
|
|
1244 (while (eq (pq-ntuples R) 1)
|
|
|
1245 (,callout (pq-get-value R 0 0) (pq-get-value R 0 1))
|
|
|
1246 (setq R (pq-exec P "FETCH k_cursor;")))
|
|
|
1247 (pq-exec P "END;")))
|
|
|
1248 @result{} map-db
|
|
|
1249 (defun callback (arg1 arg2)
|
|
|
1250 (message "arg1 = %s, arg2 = %s" arg1 arg2))
|
|
|
1251 @result{} callback
|
|
|
1252 (map-db P "xemacs_test" "WHERE field2 > 10" callback)
|
|
|
1253 @result{} arg1 = stuff, arg2 = 42
|
|
|
1254 @result{} arg1 = b, arg2 = 97
|
|
|
1255 @result{} arg1 = a string, arg2 = 12
|
|
|
1256 @result{} arg1 = a, arg2 = 97
|
|
|
1257 @result{} #<PGresult PGRES_COMMAND_OK - COMMIT>
|
|
|
1258 @end example
|
