view release on metacpan or  search on metacpan

Pg.pm  view on Meta::CPAN

  ## Simple example:
  $dbh->do("COPY mytable FROM STDIN");
  $dbh->pg_putcopydata("123\tPepperoni\t3\n");
  $dbh->pg_putcopydata("314\tMushroom\t8\n");
  $dbh->pg_putcopydata("6\tAnchovies\t100\n");
  $dbh->pg_putcopyend();

  ## This example uses explicit columns and a custom delimiter
  $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
  $dbh->pg_putcopydata("Pepperoni~123\n");
  $dbh->pg_putcopydata("Mushroom~314\n");
  $dbh->pg_putcopydata("Anchovies~6\n");
  $dbh->pg_putcopyend();

=head3 B<pg_putcopyend>

When you are finished with pg_putcopydata, call pg_putcopyend to let the server know 
that you are done, and it will return to a normal, non-COPY state. Returns a 1 on 
success. This method will fail if called when not in COPY IN mode.

=head2 Postgres limits

For convenience, DBD::Pg can export certain constants representing the limits of 
Postgres data types. To use them, just add C<:pg_limits> when DBD::Pg is used:

  use DBD::Pg qw/:pg_limits/;

The constants and their values are:

=pod

  PG_MIN_SMALLINT    -32768
  PG_MAX_SMALLINT     32767
  PG_MIN_INTEGER     -2147483648
  PG_MAX_INTEGER      2147483647
  PG_MIN_BIGINT      -9223372036854775808
  PG_MAX_BIGINT       9223372036854775807
  PG_MIN_SMALLSERIAL  1
  PG_MAX_SMALLSERIAL  32767
  PG_MIN_SERIAL       1
  PG_MAX_SERIAL       2147483647
  PG_MIN_BIGSERIAL    1
  PG_MAX_BIGSERIAL    9223372036854775807

=cut


=head2 Large Objects

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) {
      # Do something with the data.
    }
  }
  $dbh->do("CLOSE csr");

=head2 Datatype bool

The current implementation of PostgreSQL returns 't' for true and 'f' for
false. From the Perl point of view, this is a rather unfortunate
choice. DBD::Pg therefore translates the result for the C<BOOL> data type in a
Perlish manner: 'f' becomes the number C<0> and 't' becomes the number C<1>. This way 
the application does not have to check the database-specific returned values for 
the data-type C<BOOL> because Perl treats C<0> as false and C<1> as true. You may 
set the L<pg_bool_tf|/pg_bool_tf (boolean)> attribute to a true value to change the values back to 't' and
'f' if you wish.

Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y', 'yes' or
'1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for false.

On Perl 5.36 and newer, distinguished boolean values (see
L<builtin/is_bool>) can be used as placeholder values.  They will be
sent as C<1> and C<0>, or C<t> and C<f> if C<pg_bool_tf> is set to a
true value.

On older versions of Perl, false values returned by built-in operators
(such as C<!!0>) must be converted to one of the above false values,
or bound with C<< pg_type => PG_BOOL >>, since they stringify to the
empty string.

=head2 Schema support

The PostgreSQL schema concept may differ from those of other databases. In a nutshell,
a schema is a named collection of objects within a single database. Please refer to the
PostgreSQL documentation for more details:

L<http://www.postgresql.org/docs/current/static/ddl-schemas.html>

DBD::Pg does not provide explicit support for PostgreSQL schemas.
However, schema functionality may be used without any restrictions by
explicitly addressing schema objects, e.g.

  my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");

or by manipulating the schema search path with C<SET search_path>, e.g.

  $dbh->do("SET search_path TO my_schema, public");

=head1 SEE ALSO

L<The B<DBI> module|DBI>