DBD-Pg
view release on metacpan or search on metacpan
DBD::Pg supports all largeobject functions provided by libpq via the
C<< $dbh->pg_lo* >> methods. Please note that access to a large object, even read-only
large objects, must be put into a transaction.
If DBD::Pg is compiled against and connected to PostgreSQL 9.3 or newer, and
your Perl has 64-bit integers, it will use the 64-bit variants of the seek,
tell and truncate methods.
=head2 Cursors
Although PostgreSQL supports cursors, they have not been used in the current
implementation. When DBD::Pg was created, cursors in PostgreSQL could only be
used inside a transaction block. Because only one transaction block at a time
is allowed, this would have implied the restriction not to use any nested
C<SELECT> statements. Therefore the L</execute> method fetches all data at
once into data structures located in the front-end application. This fact
must to be considered when selecting large amounts of data!
You can use cursors in your application, but you'll need to do a little
work. First you must declare your cursor. Now you can issue queries against
the cursor, then select against your queries. This typically results in a
double loop, like this:
# WITH HOLD is not needed if AutoCommit is off
$dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");
while (1) {
my $sth = $dbh->prepare("fetch 1000 from csr");
$sth->execute;
last if 0 == $sth->rows;
while (my $row = $sth->fetchrow_hashref) {
- Have docs describe various ways to set client_encoding
- Change license to Artistic 2
- Remove the "goto" calls in the tests
- Force a test database rebuild when a git branch switch is detected
- Make all tests work when server and/or client encoding is SQL_ASCII
- Enable native JSON decoding, similar to arrays, perhaps with JSON::PP
- Allow partial result sets, either via PQsetSingleRowMode or something better
- Hack libpq to make user-defined number of rows returned
- Map hstore to hashes similar to the array/array mapping
- Fix ping problem: http://www.cpantesters.org/cpan/report/53c5cc72-6d39-11e1-8b9d-82c3d2d9ea9f
- Use WITH HOLD for cursor work
- Devise a way to automatically create ppm for Windows builds
- I8N docs and error messages
- Change quote and dequote functions to take Sv instead of string so that
things like arrays can be serialized by the quote function. This will
take care of broken chopblanks and pg_bool_tf (pass the quote/dequote
options struct to function quote/dequote functions)
- Allow user callbacks to quote user-defined types
- Revisit the use of version.pm
- Test heavily with a thread-enabled Perl
- Remove libpq dependency
/* Once here, we are either rewinding, or are done parsing the string */
/* If end of string, rewind one character */
if (0==ch) {
dollaroffset--;
}
if (dollarstring)
Safefree(dollarstring);
/* Advance our cursor to the current position */
currpos += dollaroffset+1;
statement--; /* Rewind statement by one */
/* If not found, might be end of string, so set ch */
if (!found) {
ch = 1;
}
/* Regardless if found or not, we send it back */
for (x=1,currph=imp_sth->ph; NULL != currph; currph = currph->nextph,x++) {
if (x==phnum)
break;
}
}
/* Check the value */
if (SvTYPE(newvalue) > SVt_PVLV) { /* hook for later array logic */
croak("Cannot bind a non-scalar value (%s)", neatsvpv(newvalue,0));
}
/* dbi handle allowed for cursor variables */
if (SvROK(newvalue) &&!IS_DBI_HANDLE(newvalue)) {
if (sv_isa(newvalue, "DBD::Pg::DefaultValue")
|| sv_isa(newvalue, "DBI::DefaultValue")) {
/* This is a special type */
Safefree(currph->value);
currph->value = NULL;
currph->valuelen = 0;
currph->isdefault = DBDPG_TRUE;
imp_sth->has_default = DBDPG_TRUE;
}
if (0==strcmp(word, "csv")) return DBDPG_TRUE;
if (0==strcmp(word, "cube")) return DBDPG_TRUE;
if (0==strcmp(word, "current")) return DBDPG_TRUE;
if (0==strcmp(word, "current_catalog")) return DBDPG_TRUE;
if (0==strcmp(word, "current_date")) return DBDPG_TRUE;
if (0==strcmp(word, "current_role")) return DBDPG_TRUE;
if (0==strcmp(word, "current_schema")) return DBDPG_TRUE;
if (0==strcmp(word, "current_time")) return DBDPG_TRUE;
if (0==strcmp(word, "current_timestamp")) return DBDPG_TRUE;
if (0==strcmp(word, "current_user")) return DBDPG_TRUE;
if (0==strcmp(word, "cursor")) return DBDPG_TRUE;
if (0==strcmp(word, "cycle")) return DBDPG_TRUE;
if (0==strcmp(word, "data")) return DBDPG_TRUE;
if (0==strcmp(word, "database")) return DBDPG_TRUE;
if (0==strcmp(word, "day")) return DBDPG_TRUE;
if (0==strcmp(word, "deallocate")) return DBDPG_TRUE;
if (0==strcmp(word, "dec")) return DBDPG_TRUE;
if (0==strcmp(word, "decimal")) return DBDPG_TRUE;
if (0==strcmp(word, "declare")) return DBDPG_TRUE;
if (0==strcmp(word, "default")) return DBDPG_TRUE;
if (0==strcmp(word, "defaults")) return DBDPG_TRUE;
{PG_PATHARRAY ,"_path" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_PG_ATTRIBUTEARRAY ,"_pg_attribute" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_PG_CLASSARRAY ,"_pg_class" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_PG_LSNARRAY ,"_pg_lsn" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_PG_PROCARRAY ,"_pg_proc" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_PG_SNAPSHOTARRAY ,"_pg_snapshot" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_PG_TYPEARRAY ,"_pg_type" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_POINTARRAY ,"_point" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_POLYGONARRAY ,"_polygon" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_RECORDARRAY ,"_record" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_REFCURSORARRAY ,"_refcursor" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_REGCLASSARRAY ,"_regclass" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_REGCOLLATIONARRAY ,"_regcollation" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_REGCONFIGARRAY ,"_regconfig" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_REGDICTIONARYARRAY ,"_regdictionary" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_REGNAMESPACEARRAY ,"_regnamespace" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_REGOPERARRAY ,"_regoper" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_REGOPERATORARRAY ,"_regoperator" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_REGPROCARRAY ,"_regproc" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_REGPROCEDUREARRAY ,"_regprocedure" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_REGROLEARRAY ,"_regrole" ,1,',',"array_out" ,quote_string,dequote_string,{0},0},
{PG_PG_LSN ,"pg_lsn" ,1,',',"pg_lsn_out" ,quote_string,dequote_string,{0},0},
{PG_PG_MCV_LIST ,"pg_mcv_list" ,1,',',"pg_mcv_list_out" ,quote_string,dequote_string,{0},0},
{PG_PG_NDISTINCT ,"pg_ndistinct" ,1,',',"pg_ndistinct_out" ,quote_string,dequote_string,{0},0},
{PG_PG_NODE_TREE ,"pg_node_tree" ,1,',',"pg_node_tree_out" ,quote_string,dequote_string,{0},0},
{PG_PG_PROC ,"pg_proc" ,1,',',"record_out" ,quote_string,dequote_string,{0},0},
{PG_PG_SNAPSHOT ,"pg_snapshot" ,1,',',"pg_snapshot_out" ,quote_string,dequote_string,{0},0},
{PG_PG_TYPE ,"pg_type" ,1,',',"record_out" ,quote_string,dequote_string,{0},0},
{PG_POINT ,"point" ,1,',',"point_out" ,quote_geom ,dequote_string,{0},0},
{PG_POLYGON ,"polygon" ,1,',',"poly_out" ,quote_geom ,dequote_string,{0},0},
{PG_RECORD ,"record" ,1,',',"record_out" ,quote_string,dequote_string,{0},0},
{PG_REFCURSOR ,"refcursor" ,1,',',"textout" ,quote_string,dequote_string,{0},0},
{PG_REGCLASS ,"regclass" ,1,',',"regclassout" ,quote_string,dequote_string,{0},0},
{PG_REGCOLLATION ,"regcollation" ,1,',',"regcollationout" ,quote_string,dequote_string,{0},0},
{PG_REGCONFIG ,"regconfig" ,1,',',"regconfigout" ,quote_string,dequote_string,{0},0},
{PG_REGDICTIONARY ,"regdictionary" ,1,',',"regdictionaryout" ,quote_string,dequote_string,{0},0},
{PG_REGNAMESPACE ,"regnamespace" ,1,',',"regnamespaceout" ,quote_string,dequote_string,{0},0},
{PG_REGOPER ,"regoper" ,1,',',"regoperout" ,quote_string,dequote_string,{0},0},
{PG_REGOPERATOR ,"regoperator" ,1,',',"regoperatorout" ,quote_string,dequote_string,{0},0},
{PG_REGPROC ,"regproc" ,1,',',"regprocout" ,quote_string,dequote_string,{0},0},
{PG_REGPROCEDURE ,"regprocedure" ,1,',',"regprocedureout" ,quote_string,dequote_string,{0},0},
{PG_REGROLE ,"regrole" ,1,',',"regroleout" ,quote_string,dequote_string,{0},0},
( run in 0.316 second using v1.01-cache-2.11-cpan-4d50c553e7e )