1Pg(3) User Contributed Perl Documentation Pg(3)
2
6 Pg - Perl5 extension for PostgreSQL
7
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
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
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 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 Pg.pm contains one convenience function: doQuery. It fills a two-
34 dimensional array with the result of your query. Usage:
35 Pg::doQuery($conn, "select attr1, attr2 from tbl", \@ary);
37 for $i ( 0 .. $#ary ) {
39 for $j ( 0 .. $#{$ary[$i]} ) {
40 print "$ary[$i][$j]\t";
41 }
42 print "\n";
43 }
44 Notice the inner loop !
46
48 The functions have been divided into three sections: Connection,
49 Result, Large Objects. For details please read libpq.
50 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 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 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 Using appropriate methods you can access almost all fields of the
72 returned PGconn structure.
73 $conn = Pg::setdbLogin($pghost, $pgport, $pgoptions, $pgtty, $dbname, $login, $pwd)
75 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 $conn = Pg::setdb($pghost, $pgport, $pgoptions, $pgtty, $dbname)
84 The method setdb should be used when username/password authentication
86 is not needed.
87 $conn = Pg::connectdb("option1=value option2=value ...")
89 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 $Option_ref = Pg::conndefaults()
98 while(($key, $val) = each %$Option_ref) {
100 print "$key, $val\n";
101 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 $conn->reset
108 Resets the communication port with the backend and tries to establish a
110 new connection.
111 $ret = $conn->requestCancel
113 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 $dbname = $conn->db
120 Returns the database name of the connection.
122 $pguser = $conn->user
124 Returns the Postgres user name of the connection.
126 $pguser = $conn->pass
128 Returns the Postgres password of the connection.
130 $pghost = $conn->host
132 Returns the host name of the connection.
134 $pgport = $conn->port
136 Returns the port of the connection.
138 $pgtty = $conn->tty
140 Returns the tty of the connection.
142 $pgoptions = $conn->options
144 Returns the options used in the connection.
146 $status = $conn->status
148 Returns the status of the connection. For comparing the status you may
150 use the following constants:
151 - PGRES_CONNECTION_OK
153 - PGRES_CONNECTION_BAD
154 $errorMessage = $conn->errorMessage
156 Returns the last error message associated with this connection.
158 $fd = $conn->socket
160 Obtain the file descriptor number for the backend connection socket. A
162 result of -1 indicates that no backend connection is currently open.
163 $pid = $conn->backendPID
165 Returns the process-id of the corresponding backend proceess.
167 $conn->trace(debug_port)
169 Messages passed between frontend and backend are echoed to the
171 debug_port file stream.
172 $conn->untrace
174 Disables tracing.
176 $result = $conn->exec($query)
178 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 ($table, $pid) = $conn->notifies
186 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 $ret = $conn->sendQuery($string, $query)
193 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 $result = $conn->getResult
200 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 $ret = $conn->isBusy
207 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 $result = $conn->consumeInput
213 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 $ret = $conn->getline($string, $length)
219 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 $ret = $conn->putline($string)
226 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 $ret = $conn->getlineAsync($buffer, $bufsize)
232 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 $ret = $conn->putnbytes($buffer, $nbytes)
239 Sends n bytes to the backend. Returns 0 if OK, EOF if not.
241 $ret = $conn->endcopy
243 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 $result = $conn->makeEmptyPGresult($status);
250 Returns a newly allocated, initialized result with given status.
252 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 $result_status = $result->resultStatus
260 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 - 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 Use the functions below to access the contents of the PGresult
274 structure.
275 $ntuples = $result->ntuples
277 Returns the number of tuples in the query result.
279 $nfields = $result->nfields
281 Returns the number of fields in the query result.
283 $ret = $result->binaryTuples
285 Returns 1 if the tuples in the query result are bianry.
287 $fname = $result->fname($field_num)
289 Returns the field name associated with the given field number.
291 $fnumber = $result->fnumber($field_name)
293 Returns the field number associated with the given field name.
295 $ftype = $result->ftype($field_num)
297 Returns the oid of the type of the given field number.
299 $fsize = $result->fsize($field_num)
301 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 $fmod = $result->fmod($field_num)
306 Returns the type-specific modification data of the field associated
308 with the given field index. Field indices start at 0.
309 $cmdStatus = $result->cmdStatus
311 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 $oid = $result->oidStatus
318 In case the last query was an INSERT command it returns the oid of the
320 inserted tuple.
321 $oid = $result->cmdTuples
323 In case the last query was an INSERT or DELETE command it returns the
325 number of affected tuples.
326 $value = $result->getvalue($tup_num, $field_num)
328 Returns the value of the given tuple and field. This is a null-
330 terminated ASCII string. Binary cursors will not work.
331 $length = $result->getlength($tup_num, $field_num)
333 Returns the length of the value for a given tuple and field.
335 $null_status = $result->getisnull($tup_num, $field_num)
337 Returns the NULL status for a given tuple and field.
339 $res->fetchrow
341 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 $result->print($fout, $header, $align, $standard, $html3, $expanded, $pager, $fieldSep, $tableOpt, $caption, ...)
347 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 $result->displayTuples($fp, $fillAlign, $fieldSep, $printHeader, qiet)
358 Kept for backward compatibility. Use print.
360 $result->printTuples($fout, $printAttName, $terseOutput, $width)
362 Kept for backward compatibility. Use print.
364 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 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 $lobj_fd = $conn->lo_open($lobjId, $mode)
375 Opens an existing large object and returns an object id. For the mode
377 bits see lo_create. Returns -1 upon failure.
378 $ret = $conn->lo_close($lobj_fd)
380 Closes an existing large object. Returns 0 upon success and -1 upon
382 failure.
383 $nbytes = $conn->lo_read($lobj_fd, $buf, $len)
385 Reads $len bytes into $buf from large object $lobj_fd. Returns the
387 number of bytes read and -1 upon failure.
388 $nbytes = $conn->lo_write($lobj_fd, $buf, $len)
390 Writes $len bytes of $buf into the large object $lobj_fd. Returns the
392 number of bytes written and -1 upon failure.
393 $ret = $conn->lo_lseek($lobj_fd, $offset, $whence)
395 Change the current read or write location on the large object $obj_id.
397 Currently $whence can only be 0 (L_SET).
398 $lobjId = $conn->lo_creat($mode)
400 Creates a new large object. $mode is a bit-mask describing different
402 attributes of the new object. Use the following constants:
403 - PGRES_INV_SMGRMASK
405 - PGRES_INV_ARCHIVE
406 - PGRES_INV_WRITE
407 - PGRES_INV_READ
408 Upon failure it returns PGRES_InvalidOid.
410 $location = $conn->lo_tell($lobj_fd)
412 Returns the current read or write location on the large object
414 $lobj_fd.
415 $ret = $conn->lo_unlink($lobjId)
417 Deletes a large object. Returns -1 upon failure.
419 $lobjId = $conn->lo_import($filename)
421 Imports a Unix file as large object and returns the object id of the
423 new object.
424 $ret = $conn->lo_export($lobjId, $filename)
426 Exports a large object into a Unix file. Returns -1 upon failure, 1
428 otherwise.
429
431 Edmund Mergl <E.Mergl@bawue.de>
432
434 PostgreSQL Programmer's Guide, Large Objects and libpq
435perl v5.34.0 2021-05-21 Pg(3)