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