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