1Pg(3)                 User Contributed Perl Documentation                Pg(3)
2
3
4

NAME

6       Pg - Perl5 extension for PostgreSQL
7

SYNOPSIS

9           use Pg;
10           $conn = Pg::connectdb("dbname=template1");
11           $res  = $conn->exec("SELECT * from pg_user");
12           while (@row = $res->fetchrow) {
13               print = join(" ", @row);
14           }
15

DESCRIPTION

17       The Pg module permits you to access all functions of the Libpq
18       interface of PostgreSQL. Libpq is the programmer's interface to
19       PostgreSQL. For examples of how to use this module, look at the file
20       test.pl.
21

GUIDELINES

23       This perl interface uses blessed references as objects.  After creating
24       a new connection or result object, the relevant Libpq functions serve
25       as virtual methods.  You do not have to care about freeing the
26       connection- and result-structures. Perl calls the destructor whenever
27       the last reference to an object goes away.
28
29       The method fetchrow can be used to fetch the next row from the server:
30       while (@row = $result->fetchrow).  Columns which have NULL as value
31       will be set to "undef".
32
33       Pg.pm contains one convenience function: doQuery. It fills a two-
34       dimensional array with the result of your query. Usage:
35
36           Pg::doQuery($conn, "select attr1, attr2 from tbl", \@ary);
37
38           for $i ( 0 .. $#ary ) {
39               for $j ( 0 .. $#{$ary[$i]} ) {
40                   print "$ary[$i][$j]\t";
41               }
42               print "\n";
43           }
44
45       Notice the inner loop !
46

FUNCTIONS

48       The functions have been divided into three sections: Connection,
49       Result, Large Objects. For details please read libpq.
50
51   1. Connection
52       With these functions you can establish and close a connection to a
53       database. In Libpq a connection is represented by a structure called
54       PGconn.
55
56       When opening a connection a given database name is always converted to
57       lower-case, unless it is surrounded by double quotes. All unspecified
58       parameters are replaced by environment variables or by hard coded
59       defaults:
60
61           parameter  environment variable  hard coded default
62           --------------------------------------------------
63           host       PGHOST                localhost
64           port       PGPORT                5432
65           options    PGOPTIONS             ""
66           tty        PGTTY                 ""
67           dbname     PGDATABASE            current userid
68           user       PGUSER                current userid
69           password   PGPASSWORD            ""
70
71       Using appropriate methods you can access almost all fields of the
72       returned PGconn structure.
73
74           $conn = Pg::setdbLogin($pghost, $pgport, $pgoptions, $pgtty, $dbname, $login, $pwd)
75
76       Opens a new connection to the backend. The connection identifier $conn
77       ( a pointer to the PGconn structure ) must be used in subsequent
78       commands for unique identification. Before using $conn you should call
79       $conn->status to ensure, that the connection was properly made.
80       Closing a connection is done by deleting the connection handle, eg
81       'undef $conn;'.
82
83           $conn = Pg::setdb($pghost, $pgport, $pgoptions, $pgtty, $dbname)
84
85       The method setdb should be used when username/password authentication
86       is not needed.
87
88           $conn = Pg::connectdb("option1=value option2=value ...")
89
90       Opens a new connection to the backend using connection information in a
91       string. Possible options are: host, port, options, tty, dbname, user,
92       password.  The connection identifier $conn (a pointer to the PGconn
93       structure) must be used in subsequent commands for unique
94       identification. Before using $conn you should call $conn->status to
95       ensure, that the connection was properly made.
96
97           $Option_ref = Pg::conndefaults()
98
99           while(($key, $val) = each %$Option_ref) {
100               print "$key, $val\n";
101
102       Returns a reference to a hash containing as keys all possible options
103       for connectdb(). The values are the current defaults. This function
104       differs from his C-counterpart, which returns the complete
105       conninfoOption structure.
106
107           $conn->reset
108
109       Resets the communication port with the backend and tries to establish a
110       new connection.
111
112           $ret = $conn->requestCancel
113
114       Abandon processing of the current query. Regardless  of the return
115       value of requestCancel, the application must continue with the normal
116       result-reading sequence using getResult. If the current query is part
117       of a transaction, cancellation will abort the whole transaction.
118
119           $dbname = $conn->db
120
121       Returns the database name of the connection.
122
123           $pguser = $conn->user
124
125       Returns the Postgres user name of the connection.
126
127           $pguser = $conn->pass
128
129       Returns the Postgres password of the connection.
130
131           $pghost = $conn->host
132
133       Returns the host name of the connection.
134
135           $pgport = $conn->port
136
137       Returns the port of the connection.
138
139           $pgtty = $conn->tty
140
141       Returns the tty of the connection.
142
143           $pgoptions = $conn->options
144
145       Returns the options used in the connection.
146
147           $status = $conn->status
148
149       Returns the status of the connection. For comparing the status you may
150       use the following constants:
151
152         - PGRES_CONNECTION_OK
153         - PGRES_CONNECTION_BAD
154
155           $errorMessage = $conn->errorMessage
156
157       Returns the last error message associated with this connection.
158
159           $fd = $conn->socket
160
161       Obtain the file descriptor number for the backend connection socket.  A
162       result of -1 indicates that no backend connection is currently open.
163
164           $pid = $conn->backendPID
165
166       Returns the process-id of the corresponding backend proceess.
167
168           $conn->trace(debug_port)
169
170       Messages passed between frontend and backend are echoed to the
171       debug_port file stream.
172
173           $conn->untrace
174
175       Disables tracing.
176
177           $result = $conn->exec($query)
178
179       Submits a query to the backend. The return value is a pointer to the
180       PGresult structure, which contains the complete query-result returned
181       by the backend. In case of failure, the pointer points to an empty
182       structure. Before using $result you should call resultStatus to ensure,
183       that the query was properly executed.
184
185           ($table, $pid) = $conn->notifies
186
187       Checks for asynchronous notifications. This functions differs from the
188       C-counterpart which returns a pointer to a new allocated structure,
189       whereas the perl implementation returns a list. $table is the table
190       which has been listened to and $pid is the process id of the backend.
191
192           $ret = $conn->sendQuery($string, $query)
193
194       Submit a query to Postgres without waiting for the result(s). After
195       successfully calling PQsendQuery, call PQgetResult one or more times to
196       obtain the query results.  PQsendQuery may not be called again until
197       getResult has returned NULL, indicating that the query is done.
198
199           $result = $conn->getResult
200
201       Wait for the next result from a prior PQsendQuery, and return it.  NULL
202       is returned when the query is complete and there will be no more
203       results.  getResult  will block only if a query is active and the
204       necessary response data has not yet been read by PQconsumeInput.
205
206           $ret = $conn->isBusy
207
208       Returns TRUE if a query is busy, that is, PQgetResult would block
209       waiting for input.  A FALSE  return indicates that PQgetResult can be
210       called with assurance of not blocking.
211
212           $result = $conn->consumeInput
213
214       If input is available from the backend, consume it. After calling
215       consumeInput, the application may check isBusy and/or notifies to see
216       if their state has changed.
217
218           $ret = $conn->getline($string, $length)
219
220       Reads a string up to $length - 1 characters from the backend.  getline
221       returns EOF at EOF, 0 if the entire line has been read, and 1 if the
222       buffer is full. If a line consists of the two characters "\." the
223       backend has finished sending the results of the copy command.
224
225           $ret = $conn->putline($string)
226
227       Sends a string to the backend. The application must explicitly send the
228       two characters "\." to indicate to the backend that it has finished
229       sending its data.
230
231           $ret = $conn->getlineAsync($buffer, $bufsize)
232
233       Non-blocking version of getline. It reads up to $bufsize characters
234       from the backend. getlineAsync returns -1 if the end-of-copy-marker has
235       been recognized, 0 if no data is avilable, and >0 the number of bytes
236       returned.
237
238           $ret = $conn->putnbytes($buffer, $nbytes)
239
240       Sends n bytes to the backend. Returns 0 if OK, EOF if not.
241
242           $ret = $conn->endcopy
243
244       This function waits  until the backend has finished the copy.  It
245       should either be issued when the last string has been sent to  the
246       backend  using  putline or when the last string has been received from
247       the backend using getline. endcopy returns 0 on success, 1 on failure.
248
249           $result = $conn->makeEmptyPGresult($status);
250
251       Returns a newly allocated, initialized result with given status.
252
253   2. Result
254       With these functions you can send commands to a database and
255       investigate the results. In Libpq the result of a command is
256       represented by a structure called PGresult. Using the appropriate
257       methods you can access almost all fields of this structure.
258
259           $result_status = $result->resultStatus
260
261       Returns the status of the result. For comparing the status you may use
262       one of the following constants depending upon the command executed:
263
264         - PGRES_EMPTY_QUERY
265         - PGRES_COMMAND_OK
266         - PGRES_TUPLES_OK
267         - PGRES_COPY_OUT
268         - PGRES_COPY_IN
269         - PGRES_BAD_RESPONSE
270         - PGRES_NONFATAL_ERROR
271         - PGRES_FATAL_ERROR
272
273       Use the functions below to access the contents of the PGresult
274       structure.
275
276           $ntuples = $result->ntuples
277
278       Returns the number of tuples in the query result.
279
280           $nfields = $result->nfields
281
282       Returns the number of fields in the query result.
283
284           $ret = $result->binaryTuples
285
286       Returns 1 if the tuples in the query result are bianry.
287
288           $fname = $result->fname($field_num)
289
290       Returns the field name associated with the given field number.
291
292           $fnumber = $result->fnumber($field_name)
293
294       Returns the field number associated with the given field name.
295
296           $ftype = $result->ftype($field_num)
297
298       Returns the oid of the type of the given field number.
299
300           $fsize = $result->fsize($field_num)
301
302       Returns the size in bytes of the type of the given field number.  It
303       returns -1 if the field has a variable length.
304
305           $fmod = $result->fmod($field_num)
306
307       Returns the type-specific modification data of the field associated
308       with the given field index. Field indices start at 0.
309
310           $cmdStatus = $result->cmdStatus
311
312       Returns the command status of the last query command.  In case of
313       DELETE it returns also the number of deleted tuples.  In case of INSERT
314       it returns also the OID of the inserted tuple followed by 1 (the number
315       of affected tuples).
316
317           $oid = $result->oidStatus
318
319       In case the last query was an INSERT command it returns the oid of the
320       inserted tuple.
321
322           $oid = $result->cmdTuples
323
324       In case the last query was an INSERT or DELETE command it returns the
325       number of affected tuples.
326
327           $value = $result->getvalue($tup_num, $field_num)
328
329       Returns the value of the given tuple and field. This is a null-
330       terminated ASCII string. Binary cursors will not work.
331
332           $length = $result->getlength($tup_num, $field_num)
333
334       Returns the length of the value for a given tuple and field.
335
336           $null_status = $result->getisnull($tup_num, $field_num)
337
338       Returns the NULL status for a given tuple and field.
339
340           $res->fetchrow
341
342       Fetches the next row from the server and returns NULL if all rows have
343       been processed. Columns which have NULL as value will be set to
344       "undef".
345
346           $result->print($fout, $header, $align, $standard, $html3, $expanded, $pager, $fieldSep, $tableOpt, $caption, ...)
347
348       Prints out all the tuples in an intelligent  manner. This function
349       differs from the C-counterpart. The struct PQprintOpt has been
350       implemented with a list. This list is of variable length, in order to
351       care for the character array fieldName in PQprintOpt.  The arguments
352       $header, $align, $standard, $html3, $expanded, $pager are boolean
353       flags. The arguments $fieldSep, $tableOpt, $caption are strings. You
354       may append additional strings, which will be taken as replacement for
355       the field names.
356
357           $result->displayTuples($fp, $fillAlign, $fieldSep, $printHeader, qiet)
358
359       Kept for backward compatibility. Use print.
360
361           $result->printTuples($fout, $printAttName, $terseOutput, $width)
362
363       Kept for backward compatibility. Use print.
364
365   3. Large Objects
366       These functions provide file-oriented access to user data.  The large
367       object interface is modeled after the Unix file system interface with
368       analogies of open, close, read, write, lseek, tell.
369
370       Starting with postgresql-6.5 it is required to use large objects only
371       inside a transaction ! See eg/lo_demo.pl for an example, how to handle
372       large objects.
373
374           $lobj_fd = $conn->lo_open($lobjId, $mode)
375
376       Opens an existing large object and returns an object id.  For the mode
377       bits see lo_create. Returns -1 upon failure.
378
379           $ret = $conn->lo_close($lobj_fd)
380
381       Closes an existing large object. Returns 0 upon success and -1 upon
382       failure.
383
384           $nbytes = $conn->lo_read($lobj_fd, $buf, $len)
385
386       Reads $len bytes into $buf from large object $lobj_fd.  Returns the
387       number of bytes read and -1 upon failure.
388
389           $nbytes = $conn->lo_write($lobj_fd, $buf, $len)
390
391       Writes $len bytes of $buf into the large object $lobj_fd.  Returns the
392       number of bytes written and -1 upon failure.
393
394           $ret = $conn->lo_lseek($lobj_fd, $offset, $whence)
395
396       Change the current read or write location on the large object $obj_id.
397       Currently $whence can only be 0 (L_SET).
398
399           $lobjId = $conn->lo_creat($mode)
400
401       Creates a new large object. $mode is a bit-mask describing different
402       attributes of the new object. Use the following constants:
403
404         - PGRES_INV_SMGRMASK
405         - PGRES_INV_ARCHIVE
406         - PGRES_INV_WRITE
407         - PGRES_INV_READ
408
409       Upon failure it returns PGRES_InvalidOid.
410
411           $location = $conn->lo_tell($lobj_fd)
412
413       Returns the current read or write location on the large object
414       $lobj_fd.
415
416           $ret = $conn->lo_unlink($lobjId)
417
418       Deletes a large object. Returns -1 upon failure.
419
420           $lobjId = $conn->lo_import($filename)
421
422       Imports a Unix file as large object and returns the object id of the
423       new object.
424
425           $ret = $conn->lo_export($lobjId, $filename)
426
427       Exports a large object into a Unix file.  Returns -1 upon failure, 1
428       otherwise.
429

AUTHOR

431           Edmund Mergl <E.Mergl@bawue.de>
432

SEE ALSO

434       PostgreSQL Programmer's Guide, Large Objects and libpq
435
436
437
438perl v5.30.1                      2020-01-30                             Pg(3)
Impressum