File: postgresql.texi

package info (click to toggle)
xemacs21 21.4.22-3.1
  • links: PTS
  • area: main
  • in suites: squeeze
  • size: 32,540 kB
  • ctags: 32,511
  • sloc: ansic: 243,759; lisp: 94,063; sh: 7,453; cpp: 5,726; perl: 1,095; makefile: 843; cs: 775; python: 279; asm: 248; lex: 119; yacc: 95; sed: 22; csh: 9
file content (1258 lines) | stat: -rw-r--r-- 42,147 bytes parent folder | download | duplicates (6)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
@c -*-texinfo-*-
@c This is part of the XEmacs Lisp Reference Manual.
@c Copyright (C) 2000 Electrotechnical Laboratory, JAPAN
@c Licensed to the Free Software Foundation
@c See the file lispref.texi for copying conditions.
@c Thank you Oscar Figueiredo!  This file was shamelessly cloned from
@c  ldap.texi.
@setfilename ../../info/postgresql.info
@node PostgreSQL Support, Internationalization, LDAP Support, top
@chapter PostgreSQL Support
@cindex PostgreSQL

XEmacs can be linked with PostgreSQL libpq run-time support to provide
relational database access from Emacs Lisp code.

@menu
* Building XEmacs with PostgreSQL support::
* XEmacs PostgreSQL libpq API::
* XEmacs PostgreSQL libpq Examples::
@end menu

@node Building XEmacs with PostgreSQL support, XEmacs PostgreSQL libpq API,  ,PostgreSQL Support
@comment  node-name,  next,  previous,  up
@section Building XEmacs with PostgreSQL support

XEmacs PostgreSQL support requires linking to the PostgreSQL libpq
library.  Describing how to build and install PostgreSQL is beyond the
scope of this document.  See the PostgreSQL manual for details.

If you have installed XEmacs from one of the binary kits on
(@url{ftp://ftp.xemacs.org/}), or are using an XEmacs binary from a CD
ROM, you may have XEmacs PostgreSQL support by default.  @code{M-x
describe-installation} will tell you if you do.

If you are building XEmacs from source, you need to install PostgreSQL
first.  On some systems, PostgreSQL will come pre-installed in /usr.  In
this case, it should be autodetected when you run configure.  If
PostgreSQL is installed into its default location,
@file{/usr/local/pgsql}, you must specify
@code{--site-prefixes=/usr/local/pgsql} when you run configure.  If
PostgreSQL is installed into another location, use that instead of
@file{/usr/local/pgsql} when specifying @code{--site-prefixes}.

As of XEmacs 21.2, PostgreSQL versions 6.5.3 and 7.0 are supported.
XEmacs Lisp support for V7.0 is somewhat more extensive than support for
V6.5.  In particular, asynchronous queries are supported.

@node XEmacs PostgreSQL libpq API, XEmacs PostgreSQL libpq Examples, Building XEmacs with PostgreSQL support, PostgreSQL Support
@comment  node-name,  next,  previous,  up
@section XEmacs PostgreSQL libpq API

The XEmacs PostgreSQL API is intended to be a policy-free, low-level
binding to libpq.  The intent is to provide all the basic functionality
and then let high level Lisp code decide its own policies.

This documentation assumes that the reader has knowledge of SQL, but
requires no prior knowledge of libpq.

There are many examples in this manual and some setup will be required.
In order to run most of the following examples, the following code needs
to be executed.  In addition to the data is in this table, nearly all of
the examples will assume that the free variable @code{P} refers to this
database connection.  The examples in the original edition of this
manual were run against Postgres 7.0beta1.

@example
(progn
  (setq P (pq-connectdb ""))
  ;; id is the primary key, shikona is a Japanese word that
  ;; means `the professional name of a Sumo wrestler', and
  ;; rank is the Sumo rank name.
  (pq-exec P (concat "CREATE TABLE xemacs_test"
                     " (id int, shikona text, rank text);"))
  (pq-exec P "COPY xemacs_test FROM stdin;")
  (pq-put-line P "1\tMusashimaru\tYokuzuna\n")
  (pq-put-line P "2\tDejima\tOozeki\n")
  (pq-put-line P "3\tMusoyama\tSekiwake\n")
  (pq-put-line P "4\tMiyabiyama\tSekiwake\n")
  (pq-put-line P "5\tWakanoyama\tMaegashira\n")
  (pq-put-line P "\\.\n")
  (pq-end-copy P))
     @result{} nil
@end example

@menu
* libpq Lisp Variables::
* libpq Lisp Symbols and DataTypes::
* Synchronous Interface Functions::
* Asynchronous Interface Functions::
* Large Object Support::
* Other libpq Functions::
* Unimplemented libpq Functions::
@end menu

@node libpq Lisp Variables, libpq Lisp Symbols and DataTypes, XEmacs PostgreSQL libpq API, XEmacs PostgreSQL libpq API
@comment  node-name,  next,  previous,  up
@subsection libpq Lisp Variables

Various Unix environment variables are used by libpq to provide defaults
to the many different parameters.  In the XEmacs Lisp API, these
environment variables are bound to Lisp variables to provide more
convenient access to Lisp Code.  These variables are passed to the
backend database server during the establishment of a database
connection and when the @code{pq-setenv} call is made.

@defvar pg:host
Initialized from the @code{PGHOST} environment variable.  The default
host to connect to.
@end defvar

@defvar pg:user
Initialized from the @code{PGUSER} environment variable.  The default
database user name.
@end defvar

@defvar pg:options
Initialized from the @code{PGOPTIONS} environment variable.  Default
additional server options.
@end defvar

@defvar pg:port
Initialized from the @code{PGPORT} environment variable.  The default
TCP port to connect to.
@end defvar

@defvar pg:tty
Initialized from the @code{PGTTY} environment variable.  The default
debugging TTY.

Compatibility note:  Debugging TTYs are turned off in the XEmacs Lisp
binding.
@end defvar

@defvar pg:database
Initialized from the @code{PGDATABASE} environment variable.  The
default database to connect to.
@end defvar

@defvar pg:realm
Initialized from the @code{PGREALM} environment variable.  The default
Kerberos realm.
@end defvar

@defvar pg:client-encoding
Initialized from the @code{PGCLIENTENCODING} environment variable.  The
default client encoding.

Compatibility note:  This variable is not present in non-Mule XEmacsen.
This variable is not present in versions of libpq prior to 7.0.
In the current implementation, client encoding is equivalent to the
@code{file-name-coding-system} format.
@end defvar

@c unused
@defvar pg:authtype
Initialized from the @code{PGAUTHTYPE} environment variable.  The
default authentication scheme used.

Compatibility note:  This variable is unused in versions of libpq after
6.5.  It is not implemented at all in the XEmacs Lisp binding.
@end defvar

@defvar pg:geqo
Initialized from the @code{PGGEQO} environment variable.  Genetic
optimizer options.
@end defvar

@defvar pg:cost-index
Initialized from the @code{PGCOSTINDEX} environment variable.  Cost
index options.
@end defvar

@defvar pg:cost-heap
Initialized from the @code{PGCOSTHEAP} environment variable.  Cost heap
options.
@end defvar

@defvar pg:tz
Initialized from the @code{PGTZ} environment variable.  Default
timezone.
@end defvar

@defvar pg:date-style
Initialized from the @code{PGDATESTYLE} environment variable.  Default
date style in returned date objects.
@end defvar

@defvar pg-coding-system
This is a variable controlling which coding system is used to encode
non-ASCII strings sent to the database.

Compatibility Note: This variable is not present in InfoDock.
@end defvar

@node libpq Lisp Symbols and DataTypes, Synchronous Interface Functions, libpq Lisp Variables, XEmacs PostgreSQL libpq API
@comment  node-name,  next,  previous,  up
@subsection libpq Lisp Symbols and Datatypes

The following set of symbols are used to represent the intermediate
states involved in the asynchronous interface.

@defvr {Symbol} pgres::polling-failed
Undocumented.  A fatal error has occurred during processing of an
asynchronous operation.
@end defvr

@defvr {Symbol} pgres::polling-reading
An intermediate status return during an asynchronous operation.  It
indicates that one may use @code{select} before polling again.
@end defvr

@defvr {Symbol} pgres::polling-writing
An intermediate status return during an asynchronous operation.  It
indicates that one may use @code{select} before polling again.
@end defvr

@defvr {Symbol} pgres::polling-ok
An asynchronous operation has successfully completed.
@end defvr

@defvr {Symbol} pgres::polling-active
An intermediate status return during an asynchronous operation.  One can
call the poll function again immediately.
@end defvr

@defun pq-pgconn conn field
@var{conn} A database connection object.
@var{field} A symbol indicating which field of PGconn to fetch.  Possible
values are shown in the following table.
@table @code
@item pq::db
Database name
@item pq::user
Database user name
@item pq::pass
Database user's password
@item pq::host
Hostname database server is running on
@item pq::port
TCP port number used in the connection
@item pq::tty
Debugging TTY

Compatibility note:  Debugging TTYs are not used in the XEmacs Lisp API.
@item pq::options
Additional server options
@item pq::status
Connection status.  Possible return values are shown in the following
table.
@table @code
@item pg::connection-ok
The normal, connected status.
@item pg::connection-bad
The connection is not open and the PGconn object needs to be deleted by
@code{pq-finish}.
@item pg::connection-started
An asynchronous connection has been started, but is not yet complete.
@item pg::connection-made
An asynchronous connect has been made, and there is data waiting to be sent.
@item pg::connection-awaiting-response
Awaiting data from the backend during an asynchronous connection.
@item pg::connection-auth-ok
Received authentication, waiting for the backend to start up.
@item pg::connection-setenv
Negotiating environment during an asynchronous connection.
@end table
@item pq::error-message
The last error message that was delivered to this connection.
@item pq::backend-pid
The process ID of the backend database server.
@end table
@end defun

The @code{PGresult} object is used by libpq to encapsulate the results
of queries.  The printed representation takes on four forms.  When the
PGresult object contains tuples from an SQL @code{SELECT} it will look
like:

@example
(setq R (pq-exec P "SELECT * FROM xemacs_test;"))
     @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
@end example

The number in brackets indicates how many rows of data are available.
When the PGresult object is the result of a command query that doesn't
return anything, it will look like:

@example
(pq-exec P "CREATE TABLE a_new_table (i int);")
     @result{} #<PGresult PGRES_COMMAND_OK - CREATE>
@end example

When either the query is a command-type query that can affect a number
of different rows, but doesn't return any of them it will look like:

@example
(progn
  (pq-exec P "INSERT INTO a_new_table VALUES (1);")
  (pq-exec P "INSERT INTO a_new_table VALUES (2);")
  (pq-exec P "INSERT INTO a_new_table VALUES (3);")
  (setq R (pq-exec P "DELETE FROM a_new_table;")))
     @result{} #<PGresult PGRES_COMMAND_OK[3] - DELETE 3>
@end example

Lastly, when the underlying PGresult object has been deallocated
directly by @code{pq-clear} the printed representation will look like:

@example
(progn
  (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
  (pq-clear R)
  R)
     @result{} #<PGresult DEAD>
@end example

The following set of functions are accessors to various data in the PGresult
object.

@defun pq-result-status result
Return status of a query result.
@var{result} is a PGresult object.  The return value is one of the
symbols in the following table.
@table @code
@item pgres::empty-query
A query contained no text.  This is usually the result of a recoverable
error, or a minor programming error.
@item pgres::command-ok
A query command that doesn't return anything was executed properly by
the backend.
@item pgres::tuples-ok
A query command that returns tuples was executed properly by the
backend.
@item pgres::copy-out
Copy Out data transfer is in progress.
@item pgres::copy-in
Copy In data transfer is in progress.
@item pgres::bad-response
An unexpected response was received from the backend.
@item pgres::nonfatal-error
Undocumented.  This value is returned when the libpq function
@code{PQresultStatus} is called with a @code{NULL} pointer.
@item pgres::fatal-error
Undocumented.  An error has occurred in processing the query and the
operation was not completed.
@end table
@end defun

@defun pq-res-status result
Return the query result status as a string, not a symbol.
@var{result} is a PGresult object.

@example
(setq R (pq-exec P "SELECT * FROM xemacs_test;"))
     @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
(pq-res-status R)
     @result{} "PGRES_TUPLES_OK"
@end example
@end defun

@defun pq-result-error-message result
Return an error message generated by the query, if any.
@var{result} is a PGresult object.

@example
(setq R (pq-exec P "SELECT * FROM xemacs-test;"))
     @result{} <A fatal error is signaled in the echo area>
(pq-result-error-message R)
     @result{} "ERROR:  parser: parse error at or near \"-\"
"
@end example
@end defun

@defun pq-ntuples result
Return the number of tuples in the query result.
@var{result} is a PGresult object.

@example
(setq R (pq-exec P "SELECT * FROM xemacs_test;"))
     @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
(pq-ntuples R)
     @result{} 5
@end example
@end defun

@defun pq-nfields result
Return the number of fields in each tuple of the query result.
@var{result} is a PGresult object.

@example
(setq R (pq-exec P "SELECT * FROM xemacs_test;"))
     @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
(pq-nfields R)
     @result{} 3
@end example
@end defun

@defun pq-binary-tuples result
Returns t if binary tuples are present in the results, nil otherwise.
@var{result} is a PGresult object.

@example
(setq R (pq-exec P "SELECT * FROM xemacs_test;"))
     @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
(pq-binary-tuples R)
     @result{} nil
@end example
@end defun

@defun pq-fname result field-index
Returns the name of a specific field.
@var{result} is a PGresult object.
@var{field-index} is the number of the column to select from.  The first
column is number zero.

@example
(let (i l)
  (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
  (setq i (pq-nfields R))
  (while (>= (decf i) 0)
    (push (pq-fname R i) l))
  l)
     @result{} ("id" "shikona" "rank")
@end example
@end defun

@defun pq-fnumber result field-name
Return the field number corresponding to the given field name.
-1 is returned on a bad field name.
@var{result} is a PGresult object.
@var{field-name} is a string representing the field name to find.
@example
(setq R (pq-exec P "SELECT * FROM xemacs_test;"))
     @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
(pq-fnumber R "id")
     @result{} 0
(pq-fnumber R "Not a field")
     @result{} -1
@end example
@end defun

@defun pq-ftype result field-num
Return an integer code representing the data type of the specified column.
@var{result} is a PGresult object.
@var{field-num} is the field number.

The return value of this function is the Object ID (Oid) in the database
of the type.  Further queries need to be made to various system tables
in order to convert this value into something useful.
@end defun

@defun pq-fmod result field-num
Return the type modifier code associated with a field.  Field numbers
start at zero.
@var{result} is a PGresult object.
@var{field-index} selects which field to use.
@end defun

@defun pq-fsize result field-index
Return size of the given field.
@var{result} is a PGresult object.
@var{field-index} selects which field to use.

@example
(let (i l)
  (setq R (pq-exec P "SELECT * FROM xemacs_test;"))
  (setq i (pq-nfields R))
  (while (>= (decf i) 0)
    (push (list (pq-ftype R i) (pq-fsize R i)) l))
  l)
     @result{} ((23 23) (25 25) (25 25))
@end example
@end defun

@defun pq-get-value result tup-num field-num
Retrieve a return value.
@var{result} is a PGresult object.
@var{tup-num} selects which tuple to fetch from.
@var{field-num} selects which field to fetch from.

Both tuples and fields are numbered from zero.

@example
(setq R (pq-exec P "SELECT * FROM xemacs_test;"))
     @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
(pq-get-value R 0 1)
     @result{} "Musashimaru"
(pq-get-value R 1 1)
     @result{} "Dejima"
(pq-get-value R 2 1)
     @result{} "Musoyama"
@end example
@end defun

@defun pq-get-length result tup-num field-num
Return the length of a specific value.
@var{result} is a PGresult object.
@var{tup-num} selects which tuple to fetch from.
@var{field-num} selects which field to fetch from.

@example
(setq R (pq-exec P "SELECT * FROM xemacs_test;"))
     @result{} #<PGresult PGRES_TUPLES_OK[5] - SELECT>
(pq-get-length R 0 1)
     @result{} 11
(pq-get-length R 1 1)
     @result{} 6
(pq-get-length R 2 1)
     @result{} 8
@end example
@end defun

@defun pq-get-is-null result tup-num field-num
Return t if the specific value is the SQL @code{NULL}.
@var{result} is a PGresult object.
@var{tup-num} selects which tuple to fetch from.
@var{field-num} selects which field to fetch from.
@end defun

@defun pq-cmd-status result
Return a summary string from the query.
@var{result} is a PGresult object.
@example
@comment This example was written on day 3 of the 2000 Haru Basho.
(setq R (pq-exec P "INSERT INTO xemacs_test
                   VALUES (6, 'Wakanohana', 'Yokozuna');"))
     @result{} #<PGresult PGRES_COMMAND_OK[1] - INSERT 542086 1>
(pq-cmd-status R)
     @result{} "INSERT 542086 1"
(setq R (pq-exec P "UPDATE xemacs_test SET rank='retired'
                    WHERE shikona='Wakanohana';"))
     @result{} #<PGresult PGRES_COMMAND_OK[1] - UPDATE 1>
(pq-cmd-status R)
     @result{} "UPDATE 1"
@end example

Note that the first number returned from an insertion, like in the
example, is an object ID number and will almost certainly vary from
system to system since object ID numbers in Postgres must be unique
across all databases.
@end defun

@defun pq-cmd-tuples result
Return the number of tuples if the last command was an INSERT/UPDATE/DELETE.
If the last command was something else, the empty string is returned.
@var{result} is a PGresult object.

@example
(setq R (pq-exec P "INSERT INTO xemacs_test VALUES
                    (7, 'Takanohana', 'Yokuzuna');"))
     @result{} #<PGresult PGRES_COMMAND_OK[1] - INSERT 38688 1>
(pq-cmd-tuples R)
     @result{} "1"
(setq R (pq-exec P "SELECT * from xemacs_test;"))
     @result{} #<PGresult PGRES_TUPLES_OK[7] - SELECT>
(pq-cmd-tuples R)
     @result{} ""
(setq R (pq-exec P "DELETE FROM xemacs_test
                    WHERE shikona LIKE '%hana';"))
     @result{} #<PGresult PGRES_COMMAND_OK[2] - DELETE 2>
(pq-cmd-tuples R)
     @result{} "2"
@end example
@end defun

@defun pq-oid-value result
Return the object id of the insertion if the last command was an INSERT.
0 is returned if the last command was not an insertion.
@var{result} is a PGresult object.

In the first example, the numbers you will see on your local system will
almost certainly be different, however the second number from the right
in the unprintable PGresult object and the number returned by
@code{pq-oid-value} should match.
@example
(setq R (pq-exec P "INSERT INTO xemacs_test VALUES
                    (8, 'Terao', 'Maegashira');"))
     @result{} #<PGresult PGRES_COMMAND_OK[1] - INSERT 542089 1>
(pq-oid-value R)
     @result{} 542089
(setq R (pq-exec P "SELECT shikona FROM xemacs_test
                    WHERE rank='Maegashira';"))
     @result{} #<PGresult PGRES_TUPLES_OK[2] - SELECT>
(pq-oid-value R)
     @result{} 0
@end example
@end defun

@defun pq-make-empty-pgresult conn status
Create an empty pgresult with the given status.
@var{conn} a database connection object
@var{status} a value that can be returned by @code{pq-result-status}.

The caller is responsible for making sure the return value gets properly
freed.
@end defun

@node Synchronous Interface Functions, Asynchronous Interface Functions, libpq Lisp Symbols and DataTypes, XEmacs PostgreSQL libpq API
@comment  node-name,  next,  previous,  up
@subsection Synchronous Interface Functions

@defun pq-connectdb conninfo
Establish a (synchronous) database connection.
@var{conninfo} A string of blank separated options.  Options are of the
form ``@var{option} = @var{value}''.  If @var{value} contains blanks, it
must be single quoted.  Blanks around the equal sign are optional.
Multiple option assignments are blank separated.
@example
(pq-connectdb "dbname=japanese port = 25432")
     @result{} #<PGconn localhost:25432 steve/japanese>
@end example
The printed representation of a database connection object has four
fields.  The first field is the hostname where the database server is
running (in this case localhost), the second field is the port number,
the third field is the database user name, and the fourth field is the
name of the database.

Database connection objects which have been disconnected and will
generate an immediate error if they are used look like:
@example
  #<PGconn BAD>
@end example
Bad connections can be reestablished with @code{pq-reset}, or deleted
entirely with @code{pq-finish}.

A database connection object that has been deleted looks like:
@example
(let ((P1 (pq-connectdb "")))
  (pq-finish P1)
  P1)
     @result{} #<PGconn DEAD>
@end example

Note that database connection objects are the most heavy weight objects
in XEmacs Lisp at this writing, usually representing as much as several
megabytes of virtual memory on the machine the database server is
running on.  It is wisest to explicitly delete them when you are
finished with them, rather than letting garbage collection do it.  An
example idiom is:

@example
(let ((P (pq-connectiondb "")))
  (unwind-protect
      (progn
	(...)) ; access database here
    (pq-finish P)))
@end example

The following options are available in the options string:
@table @code
@item authtype
Authentication type.  Same as @code{PGAUTHTYPE}.  This is no longer used.
@item user
Database user name.  Same as @code{PGUSER}.
@item password
Database password.
@item dbname
Database name.  Same as @code{PGDATABASE}
@item host
Symbolic hostname.  Same as @code{PGHOST}.
@item hostaddr
Host address as four octets (eg. like 192.168.1.1).
@item port
TCP port to connect to.  Same as @code{PGPORT}.
@item tty
Debugging TTY.  Same as @code{PGTTY}.  This value is suppressed in the
XEmacs Lisp API.
@item options
Extra backend database options.  Same as @code{PGOPTIONS}.
@end table
A database connection object is returned regardless of whether a
connection was established or not.
@end defun

@defun pq-reset conn
Reestablish database connection.
@var{conn} A database connection object.

This function reestablishes a database connection using the original
connection parameters.  This is useful if something has happened to the
TCP link and it has become broken.
@end defun

@defun pq-exec conn query
Make a synchronous database query.
@var{conn} A database connection object.
@var{query} A string containing an SQL query.
A PGresult object is returned, which in turn may be queried by its many
accessor functions to retrieve state out of it.  If the query string
contains multiple SQL commands, only results from the final command are
returned.

@example
(setq R (pq-exec P "SELECT * FROM xemacs_test;
DELETE FROM xemacs_test WHERE id=8;"))
     @result{} #<PGresult PGRES_COMMAND_OK[1] - DELETE 1>
@end example
@end defun

@defun pq-notifies conn
Return the latest async notification that has not yet been handled.
@var{conn} A database connection object.
If there has been a notification, then a list of two elements will be returned.
The first element contains the relation name being notified, the second
element contains the backend process ID number.  nil is returned if there
aren't any notifications to process.
@end defun

@defun PQsetenv conn
Synchronous transfer of environment variables to a backend
@var{conn} A database connection object.

Environment variable transfer is done as a normal part of database
connection.

Compatibility note: This function was present but not documented in versions
of libpq prior to 7.0.
@end defun

@node Asynchronous Interface Functions, Large Object Support, Synchronous Interface Functions, XEmacs PostgreSQL libpq API
@comment  node-name,  next,  previous,  up
@subsection Asynchronous Interface Functions

Making command by command examples is too complex with the asynchronous
interface functions.  See the examples section for complete calling
sequences.

@defun pq-connect-start conninfo
Begin establishing an asynchronous database connection.
@var{conninfo} A string containing the connection options.  See the
documentation of @code{pq-connectdb} for a listing of all the available
flags.
@end defun

@defun pq-connect-poll conn
An intermediate function to be called during an asynchronous database
connection.
@var{conn} A database connection object.
The result codes are documented in a previous section.
@end defun

@defun pq-is-busy conn
Returns t if @code{pq-get-result} would block waiting for input.
@var{conn} A database connection object.
@end defun

@defun pq-consume-input conn
Consume any available input from the backend.
@var{conn} A database connection object.

Nil is returned if anything bad happens.
@end defun

@defun pq-reset-start conn
Reset connection to the backend asynchronously.
@var{conn} A database connection object.
@end defun

@defun pq-reset-poll conn
Poll an asynchronous reset for completion
@var{conn} A database connection object.
@end defun

@defun pq-reset-cancel conn
Attempt to request cancellation of the current operation.
@var{conn} A database connection object.

The return value is t if the cancel request was successfully
dispatched, nil if not (in which case conn->errorMessage is set).
Note: successful dispatch is no guarantee that there will be any effect at
the backend.  The application must read the operation result as usual.
@end defun

@defun pq-send-query conn query
Submit a query to Postgres and don't wait for the result.
@var{conn} A database connection object.
Returns: t if successfully submitted
         nil if error (conn->errorMessage is set)
@end defun

@defun pq-get-result conn
Retrieve an asynchronous result from a query.
@var{conn} A database connection object.

@code{nil} is returned when no more query work remains.
@end defun

@defun pq-set-nonblocking conn arg
Sets the PGconn's database connection non-blocking if the arg is TRUE
or makes it non-blocking if the arg is FALSE, this will not protect
you from PQexec(), you'll only be safe when using the non-blocking API.
@var{conn} A database connection object.
@end defun

@defun pq-is-nonblocking conn
Return the blocking status of the database connection
@var{conn} A database connection object.
@end defun

@defun pq-flush conn
Force the write buffer to be written (or at least try)
@var{conn} A database connection object.
@end defun

@defun PQsetenvStart conn
Start asynchronously passing environment variables to a backend.
@var{conn} A database connection object.

Compatibility note: this function is only available with libpq-7.0.
@end defun

@defun PQsetenvPoll conn
Check an asynchronous environment variables transfer for completion.
@var{conn} A database connection object.

Compatibility note: this function is only available with libpq-7.0.
@end defun

@defun PQsetenvAbort conn
Attempt to terminate an asynchronous environment variables transfer.
@var{conn} A database connection object.

Compatibility note: this function is only available with libpq-7.0.
@end defun

@node Large Object Support, Other libpq Functions, Asynchronous Interface Functions, XEmacs PostgreSQL libpq API
@comment  node-name,  next,  previous,  up
@subsection Large Object Support

@defun pq-lo-import conn filename
Import a file as a large object into the database.
@var{conn} a database connection object
@var{filename} filename to import

On success, the object id is returned.
@end defun

@defun pq-lo-export conn oid filename
Copy a large object in the database into a file.
@var{conn} a database connection object.
@var{oid} object id number of a large object.
@var{filename} filename to export to.
@end defun

@node Other libpq Functions, Unimplemented libpq Functions, Large Object Support, XEmacs PostgreSQL libpq API
@comment  node-name,  next,  previous,  up
@subsection Other libpq Functions

@defun pq-finish conn
Destroy a database connection object by calling free on it.
@var{conn} a database connection object

It is possible to not call this routine because the usual XEmacs garbage
collection mechanism will call the underlying libpq routine whenever it
is releasing stale @code{PGconn} objects.  However, this routine is
useful in @code{unwind-protect} clauses to make connections go away
quickly when unrecoverable errors have occurred.

After calling this routine, the printed representation of the XEmacs
wrapper object will contain the string ``DEAD''.
@end defun

@defun pq-client-encoding conn
Return the client encoding as an integer code.
@var{conn} a database connection object

@example
(pq-client-encoding P)
     @result{} 1
@end example

Compatibility note: This function did not exist prior to libpq-7.0 and
does not exist in a non-Mule XEmacs.
@end defun

@defun pq-set-client-encoding conn encoding
Set client coding system.
@var{conn} a database connection object
@var{encoding} a string representing the desired coding system

@example
(pq-set-client-encoding P "EUC_JP")
     @result{} 0
@end example

The current idiom for ensuring proper coding system conversion is the
following (illustrated for EUC Japanese encoding):
@example
(setq P (pq-connectdb "..."))
(let ((file-name-coding-system 'euc-jp)
      (pg-coding-system 'euc-jp))
  (pq-set-client-encoding "EUC_JP")
  ...)
(pq-finish P)
@end example
Compatibility note: This function did not exist prior to libpq-7.0 and
does not exist in a non-Mule XEmacs.
@end defun

@defun pq-env-2-encoding
Return the integer code representing the coding system in
@code{PGCLIENTENCODING}.

@example
(pq-env-2-encoding)
     @result{} 0
@end example
Compatibility note: This function did not exist prior to libpq-7.0 and
does not exist in a non-Mule XEmacs.
@end defun

@defun pq-clear res
Destroy a query result object by calling free() on it.
@var{res} a query result object

Note:  The memory allocation systems of libpq and XEmacs are different.
The XEmacs representation of a query result object will have both the
XEmacs version and the libpq version freed at the next garbage collection
when the object is no longer being referenced.  Calling this function does
not release the XEmacs object, it is still subject to the usual rules for
Lisp objects.  The printed representation of the XEmacs object will contain
the string ``DEAD'' after this routine is called indicating that it is no
longer useful for anything.
@end defun

@defun pq-conn-defaults
Return a data structure that represents the connection defaults.
The data is returned as a list of lists, where each sublist contains
info regarding a single option.
@end defun

@node Unimplemented libpq Functions, , Other libpq Functions, XEmacs PostgreSQL libpq API
@comment  node-name,  next,  previous,  up
@subsection Unimplemented libpq Functions

@deftypefn {Unimplemented Function} PGconn *PQsetdbLogin (char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName, char *login, char *pwd)
Synchronous database connection.
@var{pghost} is the hostname of the PostgreSQL backend to connect to.
@var{pgport} is the TCP port number to use.
@var{pgoptions} specifies other backend options.
@var{pgtty} specifies the debugging tty to use.
@var{dbName} specifies the database name to use.
@var{login} specifies the database user name.
@var{pwd} specifies the database user's password.

This routine is deprecated as of libpq-7.0, and its functionality can be
replaced by external Lisp code if needed.
@end deftypefn

@deftypefn {Unimplemented Function} PGconn *PQsetdb (char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName)
Synchronous database connection.
@var{pghost} is the hostname of the PostgreSQL backend to connect to.
@var{pgport} is the TCP port number to use.
@var{pgoptions} specifies other backend options.
@var{pgtty} specifies the debugging tty to use.
@var{dbName} specifies the database name to use.

This routine was deprecated in libpq-6.5.
@end deftypefn

@deftypefn {Unimplemented Function} int PQsocket (PGconn *conn)
Return socket file descriptor to a backend database process.
@var{conn} database connection object.
@end deftypefn

@deftypefn {Unimplemented Function} void PQprint (FILE *fout, PGresult *res, PGprintOpt *ps)
Print out the results of a query to a designated C stream.
@var{fout} C stream to print to
@var{res} the query result object to print
@var{ps} the print options structure.

This routine is deprecated as of libpq-7.0 and cannot be sensibly exported
to XEmacs Lisp.
@end deftypefn

@deftypefn {Unimplemented Function} void PQdisplayTuples (PGresult *res, FILE *fp, int fillAlign, char *fieldSep, int printHeader, int quiet)
@var{res} query result object to print
@var{fp} C stream to print to
@var{fillAlign} pad the fields with spaces
@var{fieldSep} field separator
@var{printHeader} display headers?
@var{quiet}

This routine was deprecated in libpq-6.5.
@end deftypefn

@deftypefn {Unimplemented Function} void PQprintTuples (PGresult *res, FILE *fout, int printAttName, int terseOutput, int width)
@var{res} query result object to print
@var{fout} C stream to print to
@var{printAttName} print attribute names
@var{terseOutput} delimiter bars
@var{width} width of column, if 0, use variable width

This routine was deprecated in libpq-6.5.
@end deftypefn

@deftypefn {Unimplemented Function} int PQmblen (char *s, int encoding)
Determine length of a multibyte encoded char at @code{*s}.
@var{s} encoded string
@var{encoding} type of encoding

Compatibility note:  This function was introduced in libpq-7.0.
@end deftypefn

@deftypefn {Unimplemented Function} void PQtrace (PGconn *conn, FILE *debug_port)
Enable tracing on @code{debug_port}.
@var{conn} database connection object.
@var{debug_port} C output stream to use.
@end deftypefn

@deftypefn {Unimplemented Function} void PQuntrace (PGconn *conn)
Disable tracing.
@var{conn} database connection object.
@end deftypefn

@deftypefn {Unimplemented Function} char *PQoidStatus (PGconn *conn)
Return the object id as a string of the last tuple inserted.
@var{conn} database connection object.

Compatibility note: This function is deprecated in libpq-7.0, however it
is used internally by the XEmacs binding code when linked against versions
prior to 7.0.
@end deftypefn

@deftypefn {Unimplemented Function} PGresult *PQfn (PGconn *conn, int fnid, int *result_buf, int *result_len, int result_is_int, PQArgBlock *args, int nargs)
``Fast path'' interface --- not really recommended for application use
@var{conn} A database connection object.
@var{fnid}
@var{result_buf}
@var{result_len}
@var{result_is_int}
@var{args}
@var{nargs}
@end deftypefn

The following set of very low level large object functions aren't
appropriate to be exported to Lisp.

@deftypefn {Unimplemented Function} int pq-lo-open (PGconn *conn, int lobjid, int mode)
@var{conn} a database connection object.
@var{lobjid} a large object ID.
@var{mode} opening modes.
@end deftypefn

@deftypefn {Unimplemented Function} int pq-lo-close (PGconn *conn, int fd)
@var{conn} a database connection object.
@var{fd} a large object file descriptor
@end deftypefn

@deftypefn {Unimplemented Function} int pq-lo-read (PGconn *conn, int fd, char *buf, int len)
@var{conn} a database connection object.
@var{fd} a large object file descriptor.
@var{buf} buffer to read into.
@var{len} size of buffer.
@end deftypefn

@deftypefn {Unimplemented Function} int pq-lo-write (PGconn *conn, int fd, char *buf, size_t len)
@var{conn} a database connection object.
@var{fd} a large object file descriptor.
@var{buf} buffer to write from.
@var{len} size of buffer.
@end deftypefn

@deftypefn {Unimplemented Function} int pq-lo-lseek (PGconn *conn, int fd, int offset, int whence)
@var{conn} a database connection object.
@var{fd} a large object file descriptor.
@var{offset}
@var{whence}
@end deftypefn

@deftypefn {Unimplemented Function} int pq-lo-creat (PGconn *conn, int mode)
@var{conn} a database connection object.
@var{mode} opening modes.
@end deftypefn

@deftypefn {Unimplemented Function} int pq-lo-tell (PGconn *conn, int fd)
@var{conn} a database connection object.
@var{fd} a large object file descriptor.
@end deftypefn

@deftypefn {Unimplemented Function} int pq-lo-unlink (PGconn *conn, int lobjid)
@var{conn} a database connection object.
@var{lbojid} a large object ID.
@end deftypefn

@node XEmacs PostgreSQL libpq Examples,  , XEmacs PostgreSQL libpq API, PostgreSQL Support
@comment  node-name,  next,  previous,  up
@section XEmacs PostgreSQL libpq Examples

This is an example of one method of establishing an asynchronous
connection.

@example
(defun database-poller (P)
  (message "%S before poll" (pq-pgconn P 'pq::status))
  (pq-connect-poll P)
  (message "%S after poll" (pq-pgconn P 'pq::status))
  (if (eq (pq-pgconn P 'pq::status) 'pg::connection-ok)
      (message "Done!")
    (add-timeout .1 'database-poller P)))
     @result{} database-poller
(progn
  (setq P (pq-connect-start ""))
  (add-timeout .1 'database-poller P))
     @result{} pg::connection-started before poll
     @result{} pg::connection-made after poll
     @result{} pg::connection-made before poll
     @result{} pg::connection-awaiting-response after poll
     @result{} pg::connection-awaiting-response before poll
     @result{} pg::connection-auth-ok after poll
     @result{} pg::connection-auth-ok before poll
     @result{} pg::connection-setenv after poll
     @result{} pg::connection-setenv before poll
     @result{} pg::connection-ok after poll
     @result{} Done!
P
     @result{} #<PGconn localhost:25432 steve/steve>
@end example

Here is an example of one method of doing an asynchronous reset.

@example
(defun database-poller (P)
  (let (PS)
    (message "%S before poll" (pq-pgconn P 'pq::status))
    (setq PS (pq-reset-poll P))
    (message "%S after poll [%S]" (pq-pgconn P 'pq::status) PS)
    (if (eq (pq-pgconn P 'pq::status) 'pg::connection-ok)
	(message "Done!")
      (add-timeout .1 'database-poller P))))
     @result{} database-poller
(progn
  (pq-reset-start P)
  (add-timeout .1 'database-poller P))
     @result{} pg::connection-started before poll
     @result{} pg::connection-made after poll [pgres::polling-writing]
     @result{} pg::connection-made before poll
     @result{} pg::connection-awaiting-response after poll [pgres::polling-reading]
     @result{} pg::connection-awaiting-response before poll
     @result{} pg::connection-setenv after poll [pgres::polling-reading]
     @result{} pg::connection-setenv before poll
     @result{} pg::connection-ok after poll [pgres::polling-ok]
     @result{} Done!
P
     @result{} #<PGconn localhost:25432 steve/steve>
@end example

And finally, an asynchronous query.

@example
(defun database-poller (P)
  (let (R)
    (pq-consume-input P)
    (if (pq-is-busy P)
	(add-timeout .1 'database-poller P)
      (setq R (pq-get-result P))
      (if R
	  (progn
	    (push R result-list)
	    (add-timeout .1 'database-poller P))))))
     @result{} database-poller
(when (pq-send-query P "SELECT * FROM xemacs_test;")
  (setq result-list nil)
  (add-timeout .1 'database-poller P))
     @result{} 885
;; wait a moment
result-list
     @result{} (#<PGresult PGRES_TUPLES_OK - SELECT>)
@end example

Here is an example showing how multiple SQL statements in a single query
can have all their results collected.
@example
;; Using the same @code{database-poller} function from the previous example
(when (pq-send-query P "SELECT * FROM xemacs_test;
SELECT * FROM pg_database;
SELECT * FROM pg_user;")
  (setq result-list nil)
  (add-timeout .1 'database-poller P))
     @result{} 1782
;; wait a moment
result-list
     @result{} (#<PGresult PGRES_TUPLES_OK - SELECT> #<PGresult PGRES_TUPLES_OK - SELECT> #<PGresult PGRES_TUPLES_OK - SELECT>)
@end example

Here is an example which illustrates collecting all data from a query,
including the field names.

@example
(defun pg-util-query-results (results)
  "Retrieve results of last SQL query into a list structure."
  (let ((i (1- (pq-ntuples R)))
	j l1 l2)
    (while (>= i 0)
      (setq j (1- (pq-nfields R)))
      (setq l2 nil)
      (while (>= j 0)
	(push (pq-get-value R i j) l2)
	(decf j))
      (push l2 l1)
      (decf i))
    (setq j (1- (pq-nfields R)))
    (setq l2 nil)
    (while (>= j 0)
      (push (pq-fname R j) l2)
      (decf j))
    (push l2 l1)
    l1))
     @result{} pg-util-query-results
(setq R (pq-exec P "SELECT * FROM xemacs_test ORDER BY field2 DESC;"))
     @result{} #<PGresult PGRES_TUPLES_OK - SELECT>
(pg-util-query-results R)
     @result{} (("f1" "field2") ("a" "97") ("b" "97") ("stuff" "42") ("a string" "12") ("foo" "10") ("string" "2") ("text" "1"))
@end example

Here is an example of a query that uses a database cursor.

@example
(let (data R)
  (setq R (pq-exec P "BEGIN;"))
  (setq R (pq-exec P "DECLARE k_cursor CURSOR FOR SELECT * FROM xemacs_test ORDER BY f1 DESC;"))

  (setq R (pq-exec P "FETCH k_cursor;"))
  (while (eq (pq-ntuples R) 1)
    (push (list (pq-get-value R 0 0) (pq-get-value R 0 1)) data)
    (setq R (pq-exec P "FETCH k_cursor;")))
  (setq R (pq-exec P "END;"))
  data)
     @result{} (("a" "97") ("a string" "12") ("b" "97") ("foo" "10") ("string" "2") ("stuff" "42") ("text" "1"))
@end example

Here's another example of cursors, this time with a Lisp macro to
implement a mapping function over a table.

@example
(defmacro map-db (P table condition callout)
  `(let (R)
     (pq-exec ,P "BEGIN;")
     (pq-exec ,P (concat "DECLARE k_cursor CURSOR FOR SELECT * FROM "
			 ,table
			 " "
			 ,condition
			 " ORDER BY f1 DESC;"))
     (setq R (pq-exec P "FETCH k_cursor;"))
     (while (eq (pq-ntuples R) 1)
       (,callout (pq-get-value R 0 0) (pq-get-value R 0 1))
       (setq R (pq-exec P "FETCH k_cursor;")))
     (pq-exec P "END;")))
     @result{} map-db
(defun callback (arg1 arg2)
  (message "arg1 = %s, arg2 = %s" arg1 arg2))
     @result{} callback
(map-db P "xemacs_test" "WHERE field2 > 10" callback)
     @result{} arg1 = stuff, arg2 = 42
     @result{} arg1 = b, arg2 = 97
     @result{} arg1 = a string, arg2 = 12
     @result{} arg1 = a, arg2 = 97
     @result{} #<PGresult PGRES_COMMAND_OK - COMMIT>
@end example