« back to all changes in this revision
Viewing changes to pgintcl-3.0.1/REFERENCE.txt
- browse files at revision 1.1.1
- compare with another revision
- download diff
- download tarball
- view history from revision 1.1.1
- Committer: Package Import Robot
- Author(s): Mark Hindley
- Date: 2013-02-13 10:54:36 UTC
- mto: This revision was merged to the branch mainline in revision 3.
- Revision ID: package-import@ubuntu.com-20130213105436-w8flw5ecbt8s7w2d
Tags: upstream-2.0.7
Import upstream version 2.0.7
- files added:
- arrow-end.xbm
- doc/en
- license
- msgs
- files removed:
- arrow_down.xbm
- help
- pgintcl-1.5.0
- pgintcl-3.0.1
- tclkit
- files modified:
- CHANGE_LOG.txt
added
removed
pgintcl-3.0.1/REFERENCE.txt
1
This is pgin.tcl/REFERENCE, programmer's reference to pgin.tcl.
2
Last updated for pgin.tcl-3.0.1 on 2006-08-30
3
The project home page is: http://pgfoundry.org/projects/pgintcl/
4
-----------------------------------------------------------------------------
5
6
This is a concise reference to pgin.tcl commands. For more information on
7
the details of the Core and Large Object commands, refer to the libpgtcl or
8
pgtcl-ng documentation. Pgin.tcl attempts to emulate the command usage and
9
behavior of other versions of this interface wherever possible.
10
11
(Note: The syntax ?...? refers to optional values, per the Tcl documentation.)
12
13
14
CORE COMMANDS:
15
16
pg_conndefaults
17
18
Get the connection parameter defaults.
19
20
Returns:
21
A list of elements. Each element describes one connection parameter
22
in the following form:
23
{ OptionName Display-label display-flag display-length default-value }
24
For example, for the "user" parameter: { user Database-User {} 20 yourname }
25
26
27
pg_connect -conninfo conninfo
28
29
Connects to a database. Only the newer "-conninfo" format is supported,
30
not the older form: pg_connect dbname ?-host name ...?.
31
The conninfo argument has a series of 'option=value' entries.
32
Commonly used connection option names are:
33
dbname host port user password
34
Values need to be in single quotes if they contain spaces.
35
Within single quoted values, use \\ for \ and \' for '.
36
37
Values default to environment variables or built-in defaults.
38
Host defaults to the environment variable PGHOST, or to "localhost"
39
if no PGHOST. (libpgtcl defaults to using a Unix Domain Socket
40
in that case, but pgin.tcl cannot support UDS.)
41
42
Parameters:
43
-conninfo Fixed argument.
44
conninfo Connection info string, with the form: "option=value..."
45
46
Returns:
47
A connection handle which is used with all other commands to access the
48
database.
49
50
Notes:
51
No attempt is made to convert connection parameters such as username
52
or database name between character sets. Non-ASCII characters in these
53
parameters may not work properly.
54
55
pg_disconnect db
56
57
Disconnect from database. Also destroys any left-over result
58
structures associated with this connection.
59
60
Parameters:
61
db Connection handle to close
62
63
64
pg_select db query arrayName script
65
66
Execute a query and iterate a command script over each result row.
67
68
Parameters:
69
db Connection handle
70
query Query to execute, almost always a SELECT
71
arrayName Name of an array variable. For each row, each column value
72
is assigned to an element of this array with the column
73
name as the index.
74
script A command script to execute for each row. The script
75
can use break, continue, error, and return.
76
77
78
pg_exec db query ?args...?
79
80
Execute SQL and return a result handle.
81
If optional args are supplied, they replace parameters in the query
82
(written as $1, $2, etc. - remember to escape the $ for Tcl). This can
83
be used to insert parameters in SQL without concern for quoting or escaping.
84
It only works with text arguments - it is not binary safe. See also
85
pg_exec_params. Note: Optional args feature was added in pgin.tcl-2.1.0
86
87
Parameters:
88
db Connection handle
89
query Query to execute
90
args... Optional argument values to replace $1, $2, etc. in the query.
91
92
Returns:
93
A result handle for use with pg_result. Must be freed when no longer
94
needed.
95
96
97
pg_execute ?-oid oidName? ?-array arrayName? db query ?script?
98
99
Execute SQL and optionally iterate a script over the rows.
100
This was added to libpgtcl in PostgreSQL 7.1, and can replace both
101
pg_exec and pg_select in many cases.
102
If -array is not given on a Select, a variable is created for
103
each field in the query result.
104
If no proc is supplied, only the first query result row is saved.
105
106
Options:
107
-oid oidName A variable to receive the OID of an inserted row
108
-array arrayName An array variable name to store each row into
109
110
Parameters:
111
db Connection handle
112
query Query to execute
113
script Optional command script to execute for each row.
114
The script can use break, continue, error, and return.
115
116
Returns:
117
The number of tuples queried or affected by the command.
118
119
120
pg_result result option ?args?
121
122
Get information about a result. The option indicates the desired
123
information, which is returned by the command.
124
125
Parameters:
126
result A result handle returned by pg_exec, pg_exec_prepared,
127
or pg_exec_params.
128
option One of the command options listed below.
129
args option-dependent command arguments.
130
131
Returns:
132
Depends on the command option
133
134
Options:
135
-status Returns the result status (see notes)
136
-error ?c? Returns the error message if no code 'c' is provided,
137
or a error field if a code is provided (see below).
138
[Before 2.2.0, did not support optional 'c' parameter]
139
-errorField ?c? Same as -error
140
[Before 2.2.0, the 'c' parameter was required]
141
-conn Returns the connection handle for this result
142
-oid Returns the OID of an inserted tuple
143
-numTuples Returns the number of tuples in the result
144
-numAttrs Returns the number of attributes
145
-assign A Assign the query result data to A(tuple,attribName)
146
-assignbyidx A s Assign results to an array (see the libpgtcl docs)
147
-getTuple N Return a list of values for tuple N
148
-getNull N Return a list of NULL flags for tuple N
149
-tupleArray N A Store the Nth tuple in array A as A(attribName)
150
-attributes Return a list of attributes
151
-lAttributes Return a list of attributes as {{name type size}...}
152
-lxAttributes Return a list of extended information about
153
attributes as: {{name type size size_modifier
154
format table_OID table_column}...}
155
-cmdTuples Return number of tuples INSERTed, DELETEd, UPDATEd
156
-cmdStatus Return command status tag.
157
-list Return result set as a list of values.
158
-llist Return result set as a list of tuple data, each
159
of which is a list of values.
160
-clear Deallocate the result structure. Returns nothing.
161
162
Notes:
163
Result status from pg_result -status is one of these string values:
164
PGRES_TUPLES_OK PGRES_COMMAND_OK PGRES_FATAL_ERROR
165
PGRES_COPY_OUT PGRES_COPY_IN PGRES_EMPTY_QUERY
166
167
-oid returns 0 if the query was not an INSERT.
168
169
-cmdTuples is an extension, recently added to the bundled interface.
170
It returns an empty string if the query was not INSERT, UPDATE,
171
or DELETE. This is the expected future behavior of libpgtcl.
172
173
-cmdStatus is an extension which returns the command status tag.
174
This is the SQL command (for example: "INSERT", "CREATE") possibly
175
followed by additional data (such as the number of rows affected).
176
177
-list and -llist are extensions added to Gborg pgtcl.
178
179
-errorField is an extension to access error message subfields.
180
As of 2.2.0, the error field name or code is optional in -errorField,
181
and can also be supplied to pg_result -error. So -error and
182
-errorField are now equivalent. The optional field name
183
or code can be one of the following:
184
Field name: Code: Notes:
185
SEVERITY S ERROR or FATAL for example
186
SQLSTATE C 5-character SQL State
187
MESSAGE_PRIMARY M Main error message
188
MESSAGE_DETAIL D Optional detailed message
189
MESSAGE_HINT H Optional suggestion
190
STATEMENT_POSITION P Decimal integer cursor position
191
CONTEXT W Call stack-trace
192
SOURCE_FILE F PostgreSQL source code filename
193
SOURCE_LINE L PostgreSQL source code line number
194
SOURCE_FUNCTION R PostgreSQL function name
195
PRIMARY *Main error message
196
DETAIL *Optional detailed message
197
HINT *Optional suggestion
198
POSITION *Decimal integer cursor position
199
FILE *PostgreSQL source code filename
200
LINE *PostgreSQL source code line number
201
FUNCTION *PostgreSQL function name.
202
203
(*) These field names were added for compatibility with Gborg pgtcl.
204
Field names and codes are not case sensitive (also for compatibility
205
with Gborg pgtcl).
206
207
If the field name or code is defined, pg_result returns
208
the value of that field (if defined), else an empty string.
209
Note: pg_result -error without a code returns the SEVERITY
210
followed by the MESSAGE_PRIMARY as a single string.
211
212
-lxAttributes is an extension. It returns the same information as
213
-lAttributes plus additional information provided by the PostgreSQL
214
server about the result attributes.
215
216
-getNull is an extension. It returns a list with a flag for each column
217
in the tuple. The flag is 1 is the column value is NULL, else 0.
218
This gives you a way to tell if a database result column is NULL,
219
which otherwise looks the same as an empty string.
220
221
222
pg_listen db name ?script?
223
224
Listen for PostgreSQL notifications and call a procedure proc, or unlisten.
225
See NOTIFICATIONS below.
226
227
Parameters:
228
db Connection handle
229
name Notification condition name to start or stop listening for
230
script If provided, command script to call when notification
231
arrives. If not provided, clear current notification
232
listen for condition 'name'.
233
234
235
pg_escape_string str
236
237
Escape a string for including in SQL strings. That is, returns str with
238
single quotes and backslashes doubled up. This command was first seen
239
in beta Gborg pgtcl in CVS, but was later removed (see pg_quote).
240
241
Parameters:
242
str String to escape
243
244
Returns:
245
The escaped string.
246
247
248
pg_quote str
249
250
Escape a string for including in SQL strings, and return it with leading
251
and trailing quotes. These commands are equivalent:
252
set s '[pg_escape_string $str]'
253
set s [pg_quote $str]
254
This command was first seen in beta Gborg pgtcl in CVS, replacing the
255
pg_escape_string command. pgin.tcl supports both versions.
256
257
Parameters:
258
str String to escape
259
260
Returns:
261
The escaped string inside single quotes.
262
263
264
pg_escape_bytea binstr
265
266
Escape a binary string for including in SQL strings, intended for use
267
with bytea (byte array) columns.
268
269
Parameters:
270
binstr String to escape. This can contain arbitrary binary data.
271
272
Returns:
273
The escaped string. Put it in single quotes when using in SQL.
274
275
Notes:
276
This is slow on large strings. Consider using binary prepared queries
277
instead.
278
279
280
pg_unescape_bytea str
281
282
Unescape a string coming back from a PostgreSQL query on a bytea (byte
283
array) column, and return the original binary string.
284
285
Parameters:
286
str String to unescape. This should be the result of a
287
query on a bytea column; other uses are undefined (see notes)
288
289
Returns:
290
The unescaped binary string.
291
292
Notes:
293
Consider using binary prepared queries instead, for better performance.
294
295
This command does not fully emulate the corresponding libpq function
296
PQunescapeBytea, and will return different results for some strings.
297
(Specifically, it uses the Tcl subst command which unescapes more
298
sequences than the PQunescapeBytea.)
299
But it will return the correct data for all strings which can possibly
300
be returned by a PostgreSQL server in response to a query on a bytea
301
column. So it acts correctly when used in the intended way.
302
303
304
-----------------------------------------------------------------------------
305
EXTENSIONS:
306
307
pgin.tcl has some extended commands and variables.
308
NOTE: These commands are subject to change, and those changes may break
309
code which uses these commands. These commands do not exist in libpgtcl. If
310
equivalent but different commands are added to libpgtcl, it is likely that
311
pgin.tcl will be changed to match.
312
313
pg_notice_handler db ?command?
314
315
Query or set a command to handle Notice or Warning messages.
316
If the command is supplied, sets the handler to that command, and
317
returns the previous command. If the command is not supplied,
318
returns the current handler command. See NOTICES below.
319
320
Parameters:
321
db Connection handle
322
command Command to execute on receipt of notice or warning
323
324
Returns:
325
The previous handler command
326
327
328
pg_endcopy result
329
330
This must be called after SQL COPY FROM or COPY TO completes.
331
See COPY FROM/TO below.
332
333
Parameters:
334
result Result handle on which a COPY is done.
335
336
337
pg_copy_read result
338
339
Read the next line (record) for SQL COPY TO STDOUT. Returns the line
340
read, or an empty string when COPY is done. The returned line does
341
not end in a newline, so you can just split it on tab to get the
342
column values. With PostgreSQL-7.4 support, you must use this
343
routine for COPY TO STDOUT; reading from the socket no longer works.
344
345
Parameters:
346
result Result handle on which a COPY is active.
347
348
Returns:
349
The line read from the server, or empty when done.
350
351
352
pg_copy_write result line
353
354
Write one line (record) $line for SQL COPY FROM STDIN.
355
The passed argument must not end in a newline.
356
With PostgreSQL-7.4 support, you must use this routine for
357
COPY FROM STDIN; writing to the socket no longer works.
358
359
Parameters:
360
result Result handle on which a COPY is active.
361
line One record to write to the server.
362
363
$pgtcl::version
364
This variable has the pgin.tcl version number. The existence of
365
this variable can also be used to determine if pgin.tcl has been loaded.
366
Note: This is deprecated in favor of using Tcl's package management.
367
Use [package present pgintcl] to test for pgin.tcl and get its version.
368
369
370
pg_callfn db fname result arginfo arg...
371
pg_callfn_int db fname arginfo arg...
372
373
These two commands allow access to the PostgreSQL back-end "fast-path"
374
function call interface. This is not intended for routine use.
375
See the INTERNALS document for more information.
376
377
378
pg_parameter_status db param
379
380
Fetch the value of a parameter supplied by a PostgreSQL-7.4 or higher
381
backend. Returns the value of the named parameter (or an empty string
382
if no such parameter has been sent by the backend). The following
383
parameters are commonly sent by the backend:
384
client_encoding DateStyle is_superuser server_encoding
385
server_version session_authorization
386
387
Parameters:
388
db Connection handle
389
param Name of the parameter to get the value of
390
391
392
pg_exec_params db query res_formats arg_formats arg_types arg...
393
394
Parse SQL statement, bind parameters, and execute statement.
395
This is similar to pg_exec_prepared (see below), but doesn't use a
396
pre-prepared statement, and if you want to binary parameters you
397
must also provide the type OIDs.
398
399
res_formats is a list (but see note below) describing the query result
400
columns, and arg_formats is a list describing the query parameter formats,
401
as follows. An empty list means all parameters or result columns are text
402
(or, that there are no parameters/result columns). A single word "TEXT"
403
(or "T"), or "BINARY" (or "B"), indicates the format of all parameters or
404
of all result columns. Finally, a list of those words indicates the format
405
of each individual parameter or result column. For example:
406
{} All text format
407
T All text format
408
T B One text, one binary format
409
B B B Three binary format
410
411
Parameters:
412
db Connection handle
413
query Query to execute, may contain parameters $1, $2, ...
414
res_formats A list describing results: B* => binary, else text
415
arg_formats A list describing args: B* => Binary, else Text.
416
arg_types A list of type OIDs for each argument (if Binary).
417
args Variable number of arguments to bind to the query params.
418
Returns:
419
A result handle, for use with pg_result.
420
421
Notes:
422
There is no support for passing NULL arguments
423
424
If there are any binary format arguments, an arg_type must be specified
425
for each argument, although the value will be ignored for each text
426
format argument.
427
428
libpq does not support mixed Text/Binary result columns in prepared
429
queries. Although pgin.tcl does, this is not recommended because it
430
will not be compatible with libpq-based versions of the pgtcl interface.
431
So the res_formats argument should contain at most a single word.
432
433
434
pg_exec_prepared db stmt_name res_formats arg_formats arg...
435
436
Executes a pre-prepared SQL statement with text and/or binary parameters
437
and text and/or binary result columns. Parameter place-holders in the
438
prepared statement are designated $1, $2, etc.
439
This allows binding arguments to SQL statement parameters without
440
quoting problems, and sending and receiving raw binary data.
441
442
The statement must be prepared with the SQL command
443
PREPARE statement_name (args) AS ...
444
445
res_formats is a list (but see note below) describing the query result
446
columns, and arg_formats is a list describing the query parameter formats,
447
as follows. An empty list means all parameters or result columns are text
448
(or, that there are no parameters/result columns). A single word "TEXT"
449
(or "T"), or "BINARY" (or "B"), indicates the format of all parameters or
450
of all result columns. Finally, a list of those words indicates the format
451
of each individual parameter or result column. (See pg_exec_params)
452
453
Parameters:
454
db Connection handle
455
stmt_name Name of a pre-prepared SQL statement
456
res_formats A list describing results: B* => binary, else text
457
arg_formats A list describing args: B* => Binary, else Text.
458
args Variable number of arguments to bind to the query params.
459
460
Returns:
461
A result handle, for use with pg_result.
462
463
Notes:
464
There is no support for passing NULL arguments
465
466
libpq does not support mixed Text/Binary result columns in prepared
467
queries. Although pgin.tcl does, this is not recommended because it
468
will not be compatible with libpq-based versions of the pgtcl interface.
469
So the res_formats argument should contain at most a single word.
470
471
472
pg_transaction_status db
473
474
Returns the current in-transaction status.
475
476
Parameters:
477
db Connection handle
478
479
Returns:
480
The status - one of the following strings:
481
IDLE (Connection is idle, not in a transaction)
482
INTRANS (Connection is idle, in a valid transaction block)
483
INERROR (Connection is in a failed transaction block)
484
UNKNOWN (Connection is bad or in an unknown state)
485
486
487
488
-----------------------------------------------------------------------------
489
LARGE OBJECTS:
490
491
pgin.tcl implements the Large Object commands of libpgtcl.
492
493
Remember that these routines must be used inside transactions. Also note
494
that you, not PostgreSQL, are responsible for tracking the large objects by
495
their OIDs in your database schema tables. So for example you will
496
generally have to pair a database INSERT with a pg_lo_creat, and a database
497
DELETE with a pg_lo_unlink.
498
499
All of the Large Object commands throw a Tcl error if an error occurs, with
500
the exception of pg_lo_read and pg_lo_write, which return -1 on error.
501
This is inconsistent, but the read and write commands were already
502
documented in the PostgreSQL manual to return -1 on error, so that is how
503
pgin.tcl implements them. Error behavior of the other routines was not
504
documented; as coded some returned a negative number and some threw an
505
error. The decision to have the pgin.tcl implementation of these commands
506
always throw a Tcl error was made because otherwise there is no way to get
507
at the error message text. It is possible that future versions of the
508
interface will also have pg_lo_read and pg_lo_write throw a Tcl error if an
509
error occurs.
510
511
512
pg_lo_creat db mode
513
514
Create a large object. Mode should be one of the strings INV_READ,
515
INV_WRITE, or INV_READ|INV_WRITE, although to be honest I do not know
516
what the difference is. As an extension, to be compatible with pg_lo_open,
517
this command also accepts mode of "r", "w", or "rw".
518
519
Parameters:
520
db Connection handle
521
mode Mode to create large object: INV_READ|INV_WRITE
522
523
Returns:
524
A large object OID, which you should promptly insert into a table.
525
526
527
pg_lo_open db loid mode
528
529
Open a large object and returns a large object file descriptor.
530
Mode can be "r", "w", or "rw" specifying read and/or write. As an
531
extension, to be compatible with pg_lo_creat and libPQ, this command
532
also accepts mode of INV_READ, INV_WRITE, or "INV_READ|INV_WRITE".
533
The $loid usually comes from the return value of pg_lo_creat directly,
534
or indirectly as an oid-type field in a table.
535
536
Parameters:
537
db Connection handle
538
loid Large Object ID identifying the large object to open
539
mode Mode to open large object in: "r", "w", "rw"
540
541
Returns:
542
A large object file descriptor (a lofd) for use with the commands below.
543
544
545
pg_lo_close db lofd
546
547
Close a large object opened with pg_lo_open.
548
549
Parameters:
550
db Connection handle
551
lofd Large Object file descriptor to close
552
553
554
pg_lo_unlink db loid
555
556
Delete a large object.
557
558
Parameters:
559
db Connection handle
560
loid Large Object ID identifying the large object to delete
561
562
563
pg_lo_read db lofd buf_name maxlen
564
565
Read from a large object.
566
567
Parameters:
568
db Connection handle
569
lofd Large Object file descriptor to read from
570
buf_name Name of the buffer variable to read into
571
maxlen Maximum number of bytes to read from the large object
572
573
Returns:
574
The number of bytes actually read, 0 on end of large object, -1 on error.
575
576
577
pg_lo_write db lofd buf len
578
579
Write to a large object.
580
581
Parameters:
582
db Connection handle
583
lofd Large Object file descriptor to write to
584
buf Buffer containing data to write to the large object
585
len Maximum number of bytes to write from buf to the large object.
586
(If buf has fewer than len bytes, just write all of buf.)
587
588
Returns:
589
The number of bytes actually written, -1 on error.
590
591
pg_lo_lseek db lofd offset whence
592
593
Reposition the (virtual) file position pointer in a large object.
594
595
Parameters:
596
db Connection handle
597
lofd Large Object file descriptor to position
598
offset New position, interpreted per "whence"
599
whence Position mode: SEEK_SET, SEEK_CUR, or SEEK_END specifying
600
that offset is a byte count relative to start of large object,
601
current position, or end of large object respectively.
602
603
604
pg_lo_tell db lofd
605
606
Get the current large object position pointer.
607
608
Parameters:
609
db Connection handle
610
lofd Large Object file descriptor to get position of.
611
612
Returns:
613
The integer (virtual) file offset of the current file position
614
pointer in the large object.
615
616
617
pg_lo_import db filename
618
619
Create a new large object, and import the contents of a file into it.
620
621
Parameters:
622
db Connection handle
623
filename Pathname of a file to import as a large object
624
625
Returns:
626
A large object OID, which you should promptly insert into a table.
627
628
629
pg_lo_export db loid filename
630
631
Export a large object and write its contents into a file.
632
633
Parameters:
634
db Connection handle
635
loid Large Object ID identifying the large object to export
636
filename Pathname of a file to export the large object into
637
638
639
-----------------------------------------------------------------------------
640
NOTICES:
641
642
If the backend sends a notice or warning message, the notice handler will
643
be executed with the text of the notice as the final parameter. The default
644
procedure just prints the message to stderr (like libpq does). You may
645
replace this by defining your own procedure and using the command:
646
pg_notice_handler $conn_handle "notice_command ..."
647
The actual message will be appended as an additional argument to your
648
command.
649
650
If you want to suppress notice and warning messages completely, you can set
651
the notice handler to an empty string. For example, if you need to
652
temporarily suppress notices and warnings, use something like this:
653
set save_handler [pg_notice_handler $conn_handle {}]
654
... commands with no notice or warning messages reported ...
655
pg_notice_handler $conn_handle $save_handler
656
But note that a better way to ignore NOTICE messages is to increase the
657
message threshold with: SET CLIENT_MIN_MESSAGES TO WARNING
658
659
Don't confuse Notices with Notification. Notice and warning messages are
660
generated by the server in response to a command from the client, but do
661
not imply failure of the command so they don't affect the result status.
662
An example of a notice is index creation as a result of creating a table
663
with a primary key. An example of a warning is if ROLLBACK is issued
664
outside a transaction. By contrast, notifications are messages sent on
665
behalf of another database client.
666
667
Previous versions of this interface used the following syntax instead:
668
pg_configure $conn_handle notice "notice_command ..."
669
This is still supported but deprecated.
670
671
-----------------------------------------------------------------------------
672
NOTIFICATIONS:
673
674
Support for backend notifications differs from libpgtcl. With libpgtcl, the
675
notification will be received as soon as Tcl enters the idle loop, e.g. if
676
you use "update". libpgtcl does not need to be reading from the backend to
677
get a notification. With pgin.tcl, the notification from the backend will
678
only be seen while something is being read from the backend; that is,
679
during pg_exec, pg_select, or pg_execute processing. After a notification
680
is read, it will be delivered the next time Tcl enters the idle loop.
681
682
-----------------------------------------------------------------------------
683
COPY FROM/TO:
684
685
Front-end copy is a bulk import or export operation where multiple rows
686
are sent between the PostgreSQL back-end and client front-end with minimal
687
formatting. This is implemented in PostgreSQL with the following SQL:
688
COPY tablename TO STDOUT; -- Export table
689
COPY tablename FROM STDIN; -- Import table
690
Each row is transmitted as one line, with columns separated by a delimiter
691
which defaults to tab, backslash (\) escaping of control characters, and
692
\N used for NULL.
693
694
(Note: You never have to use COPY FROM/TO. You can always use the standard
695
SQL SELECT and INSERT instead. COPY FROM/TO is said to be more efficient
696
for large amounts of data.)
697
698
The COPY protocol changed with PostgreSQL-7.4, and it is no longer possible
699
to directly read and write to the connection handle as with previous
700
versions of pgin.tcl. You must use the routines below to read and write
701
records during COPY. This is currently incompatible with libpgtcl.
702
703
To copy out a table, first issue "COPY tablename TO STDOUT" using pg_exec.
704
The result status will change to PGRES_COPY_OUT. Then use pg_copy_read to
705
read each record. Returned records will not end in a newline. Repeat
706
pg_copy_read until it returns an empty string, then execute pg_endcopy.
707
For example:
708
709
while {[set line [pg_copy_read $result_handle]] != ""} {
710
... Process record in $line ...
711
}
712
pg_endcopy $result_handle
713
714
After pg_endcopy returns, the result status should be PGRES_COMMAND_OK if
715
the copy was successful.
716
717
To copy in a table, first issue "COPY tablename FROM STDIN" using pg_exec.
718
The result status will change to PGRES_COPY_IN. Then use pg_copy_write to
719
write each record. Do not append a newline to the record. Repeat
720
pg_copy_write until you are done, then execute pg_endcopy. For example:
721
while {... more data to send ...} {
722
pg_copy_write $result_handle $tab_separated_data_line
723
}
724
pg_endcopy $result_handle
725
726
After pg_endcopy returns, the result status should be PGRES_COMMAND_OK if
727
the copy was successful.
728
729
Do not write or expect to read the old COPY delimiter "\.".
730
731
-----------------------------------------------------------------------------
732
ENCODINGS: (New at pgin.tcl-3.0.0)
733
734
Pgin.tcl converts all text sent to PostgreSQL (query strings, COPY FROM
735
data, text-mode parameters of prepared queries, and prepared statement
736
names) into UTF-8 (Unicode). It converts all text received from PostgreSQL
737
(query results which are text-mode, error/notice/notify strings, COPY TO
738
data, field names) back from UTF-8 (Unicode). (This happens implicitly in
739
the compiled versions of the Tcl interface, but Pgin.tcl has to do it
740
explicitly.)
741
742
Pgin.tcl informs the PostgreSQL server that it will be using Unicode when
743
communicating with the server. This is the same behavior as the libpq-based
744
versions of the Tcl PostgreSQL interface. We do this because Tcl uses
745
Unicode internally, and using Unicode allows for different client and
746
server character sets without loss of information.
747
748
PostgreSQL converts between this Unicode data and the database encoding, if
749
necessary. For example, if the database encoding is Latin1, then Latin1
750
characters will be stored in the database, because PostgreSQL converts the
751
Tcl-supplied UTF-8 (Unicode) into Latin1. If the client application also
752
uses Latin1, then data is converted twice in each direction: for sending
753
over the communications link in Unicode, and then in the server or client
754
back to Latin1.
755
756
Provided the database encoding is correct, translation will happen
757
transparently to the client application. Other non-Tcl applications,
758
such as psql, will also be able to access the data correctly provided
759
they set their client_encoding parameter.
760
761
* * * CAUTION * * *
762
763
Do not store non-ASCII characters in character or text fields in a
764
PostgreSQL database which was created with encoding SQL_ASCII.
765
The SQL_ASCII encoding provides no information to PostgreSQL on
766
how to translate characters, so the server will be unable to
767
translate. Applications using a Tcl interface, including
768
pgin.tcl, will encode these characters using UTF-8 for storage
769
in the database, but PostgreSQL will not know it due to the
770
SQL_ASCII encoding setting. The result is that it may be
771
impossible to access the data correctly from other applications.
772
Always use the correct encoding when creating a database: for
773
example, LATIN1 or Unicode.
774
775
Pgin.tcl-2.x and older do not convert to/from Unicode and do not set
776
client_encoding at all. These older versions may not work with non-ASCII
777
characters in any database encoding.
778
779
At this time, Pgin.tcl does not recode connection string parameters
780
Username, Database Name, or Password. Non-ASCII characters in these
781
fields will probably not work.
782
783
-----------------------------------------------------------------------------
Loggerhead is a web-based interface for Breezy
Version: 2.0.1