FU
view release on metacpan or search on metacpan
package FU::Pg::error {
use overload '""' => sub($e, @) { $e->{full_message} };
}
1;
__END__
=head1 NAME
FU::Pg - The Ultimate (synchronous) Interface to PostgreSQL
=head1 SYNOPSYS
use FU::Pg;
my $conn = FU::Pg->connect("dbname=test user=test password=nottest");
$conn->exec('CREATE TABLE books (id SERIAL, title text, read bool)');
$conn->sql('INSERT INTO books (title) VALUES ($1)', 'Revelation Space')->exec;
$conn->sql('INSERT INTO books (title) VALUES ($1)', 'The Invincible')->exec;
for my ($id, $title) ($conn->sql('SELECT * FROM books')->flat->@*) {
print "$id: $title\n";
}
=head1 DESCRIPTION
FU::Pg is a client module for PostgreSQL with a convenient high-level API and
support for flexible and complex type conversions. This module interfaces
directly with C<libpq>.
=head1 Connection setup
=over
=item FU::Pg->connect($string)
Connect to the PostgreSQL server and return a new C<FU::Pg::conn> object.
C<$string> can either be in key=value format or a URI, refer to L<the
PostgreSQL
documentation|https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING>
for the full list of supported formats and options. You may also pass an empty
string and leave the configuration up to L<environment
variables|https://www.postgresql.org/docs/current/libpq-envars.html>.
=item $conn->server_version
Returns the version of the PostgreSQL server as an integer in the format of
C<$major * 10000 + $minor>. For example, returns 170002 for PostgreSQL 17.2.
=item $conn->lib_version
Returns the libpq version in the same format as the C<server_version> method.
Also available directly as C<FU::Pg::lib_version()>.
=item $conn->status
Returns a string indicating the status of the connection. Note that this method
does not verify that the connection is still alive, the status is updated after
each command. Possible return values:
=over
=item idle
Awaiting commands, not in a transaction.
=item txn_idle
Awaiting commands, inside a transaction.
=item txn_done
Idle, but a transaction object still exists. The connection is unusable until
that object goes out of scope.
=item txn_error
Inside a transaction that is in an error state. The transaction must be rolled
back in order to recover to a usable state. This happens automatically when the
transaction object goes out of scope.
=item active
Currently executing a query. This state can only be observed during a L<COPY
operation|/"COPY support">.
=item bad
Connection is dead or otherwise unusable.
=back
=item $conn->escape_literal($str)
Return an escaped version of C<$str> suitable for use as a string literal in an
SQL statement. You'll rarely need this, it's often better to pass data as bind
parameters instead.
=item $conn->escape_identifier($str)
Return an escaped version of C<$str> suitable for use as an identifier (name of
a table, column, function, etc) in an SQL statement.
=item $conn->cache($enable)
=item $conn->text_params($enable)
=item $conn->text_results($enable)
=item $conn->text($enable)
Set the default settings for new statements created with B<< $conn->sql() >>.
=item $conn->cache_size($num)
Set the number of prepared statements to keep in the cache. Defaults to 256.
Setting this (temporarily) to 0 will immediately reclaim all cached statements.
Prepared statements that still have an active C<$st> object are not counted
towards this number. The cache works as an LRU: when it's full, the statement
that hasn't been used for the longest time is reclaimed.
=item $conn->query_trace($sub)
Set a subroutine to be called on every query executed on this connection. The
subroutine is given a statement object, refer to the C<$st> methods below for
the fields that can be inspected. C<$sub> can be set to C<undef> to disable
query tracing.
It is important to not hold on to the given C<$st> any longer than strictly
necessary, because the prepared statement is not closed or reclaimed while the
object remains alive. If you need information to remain around for longer than
the duration of the subroutine call, it's best to grab the relevant information
from the C<$st> methods and save that for later.
Also worth noting that the subroutine is called from the context of the code
executing the query, but I<before> the query results have been returned.
The subroutine is (currently) only called for queries executed through C<<
$conn->exec >>, C<< $conn->sql >>, C<< $conn->SQL >> and their C<$txn> variants;
C<< $conn->copy >> statements and internal queries performed by this module
(such as for transaction management, querying type information, etc) do not
trigger the callback. Statements that result in an error being thrown during or
before execution are also not traceable this way. This behavior might change in
the future.
=item $conn->disconnect
Close the connection. Any active transactions are rolled back and further
attempts to use C<$conn> throw an error.
=back
=head1 Querying
=over
=item $conn->exec($sql)
Execute one or more SQL commands, separated by a semicolon. Returns the number
of rows affected by the last statement or I<undef> if that information is not
available for the given command (like with C<CREATE TABLE>).
=item $conn->sql($sql, @params)
Create a new SQL statement with the given C<$sql> string and an optional list
of bind parameters. C<$sql> can only hold a single statement.
Parameters can be referenced from C<$sql> with numbered placeholders, where
C<$1> refers to the first parameter, C<$2> to the second, etc. Be careful to
not accidentally interpolate perl's C<$1> and C<$2>. Using a question mark for
placeholders, as is common with L<DBI>, is not supported. An error is thrown
when attempting to execute a query where the number of C<@params> does not
match the number of placeholders in C<$sql>.
Note that this method just creates a statement object, the query is not
prepared or executed until the appropriate statement methods (see below) are
used.
=item $conn->SQL(@args)
Same as C<< $conn->sql() >> but uses L<FU::SQL> to construct the query and bind
parameters. Uses the 'pg' C<in_style> and C<< $conn->escape_identifier() >> for
identifier quoting.
=back
Statement objects returned by C<< $conn->sql() >> support the following
configuration parameters, which can be set before the statement is executed:
=over
parameter in the given C<$sql> string. Example:
my $oids = $conn->sql('SELECT id FROM books WHERE id = $1 AND title = $2')->param_types;
# $oids = [23,25]
my $oids = $conn->sql('SELECT id FROM books')->params;
# $oids = []
This method can be called before the query has been executed, but will then
trigger a prepare operation. An empty array is also returned if the query has
already been executed without a separate preparation step; this happens if
prepared statement caching is disabled and C<text_params> is enabled.
=item $st->columns
Returns an arrayref of hashrefs describing each column that the statement
returns.
my $cols = $conn->sql('SELECT id, title FROM books')->columns;
# $cols = [
# { name => 'id', oid => 23 },
# { name => 'title', oid => 25 },
# ]
=item $st->nrows
Number of rows returned by the query.
=item $st->exec_time
Observed query execution time, in seconds. Includes network round-trip and
fetching the full query results. Does not include conversion of the query
results into Perl values.
=item $st->prepare_time
Observed query preparation time, in seconds, including network round-trip.
Returns 0 if a cached prepared statement was used or C<undef> if the query was
executed without a separate preparation phase.
=item $st->get_cache
=item $st->get_text_params
=item $st->get_text_results
Returns the respective configuration parameters.
=back
=head1 Transactions
This module provides a convenient and safe API for I<scoped transactions> and
I<subtransactions>. A new transaction can be started with C<< $conn->txn >>,
which returns an object that can be used to run commands inside the transaction
and control its fate. When the object goes out of scope, the transaction is
automatically rolled back if no explicit C<< $txn->commit >> has been
performed. Any attempts to run queries on the parent C<< $conn >> object will
fail while a transaction object is alive.
{
# start a new transaction
my $txn = $conn->txn;
# run queries
$txn->sql('DELETE FROM books WHERE id = $1', 1)->exec;
# run commands in a subtransaction
{
my $subtxn = $txn->txn;
# ...
}
# commit
$txn->commit;
# If $txn->commit has not been called, the transaction will be rolled back
# automatically when it goes out of scope.
}
Transaction methods:
=over
=item $txn->exec(..)
=item $txn->sql(..)
=item $txn->SQL(..)
Run a query inside the transaction. These work the same as the respective
methods on the parent C<$conn> object.
=item $txn->commit
=item $txn->rollback
Commit or abort the transaction. Any attempts to run queries on this
transaction object after this call will throw an error.
Calling C<rollback> is optional, the transaction is automatically rolled back
when the object goes out of scope.
=item $txn->cache($enable)
=item $txn->text_params($enable)
=item $txn->text_results($enable)
=item $txn->text($enable)
Set the default settings for new statements created with B<< $txn->sql() >>.
These settings are inherited from the main connection when the transaction is
created. Subtransactions inherit these settings from their parent transaction.
Changing these settings within a transaction does not affect the main
connection or any already existing subtransactions.
=item $txn->txn
=over
=item $conn->perl2bin($oid, $val)
=item $conn->bin2perl($oid, $bin)
Convert the value for a specific type between the Perl representation and the
PostgreSQL binary format, using the current type configuration of the
connection. This is the same conversion used internally by this module to send
bind parameters and receive query results, and map to the C<send> and C<recv>
functions of C<< $conn->set_type() >>.
These methods throw an error if C<$oid> is not a known type or if the given
data is not valid for the type. However, these methods should not be used for
strict validation: the conversion routines are usually written under the
assumption that the data has been received directly from Postgres or is about
to be sent to (and further validated by) Postgres. For some types,
C<perl2bin()> may return invalid data on invalid input and C<bin2perl()> may
accept invalid binary data.
=item $conn->bin2text($oid, $bin, ...)
=item $conn->text2bin($oid, $text, ...)
Convert between the binary format and the PostgreSQL text format. This
conversion requires a round-trip to the server and throws an error if the
connection state is not I<idle> or I<txn_idle>. Since it is Postgres doing the
conversion, the input is properly validated and, in the case of C<bin2text()>,
the result is guaranteed to be suitable for use as a textual bind parameter or
for inclusion in an SQL query (but don't forget to use C<escape_literal()> in
that case).
Calling these methods many times can be pretty slow. If you have several values
to convert, you can do that in a single call to speed things up:
my($text1, $text2, ..) = $conn->bin2text($oid1, $bin1, $oid2, $bin2, ..);
=back
I<TODO:> Methods to query type info.
=head1 COPY support
You can use L<COPY
statements|https://www.postgresql.org/docs/current/sql-copy.html> for efficient
bulk data transfers between your application and the PostgreSQL server:
=over
=item $copy = $conn->copy($statement)
=item $copy = $txn->copy($statement)
Execute C<$statement> and return a C<FU::Pg::copy> object that lets you
transfer data to or from Postgres.
It is not possible to execute any other queries on the same connection while a
copy operation is in progress. When used on a transaction object, C<$txn> must
be kept alive long enough to finish the copy operation.
=back
A C<$copy> object supports the following methods:
=over
=item $copy->is_binary
Returns true if the transfer is performed in the binary format, false for text.
=item $copy->write($data)
Send C<$data> to the server. An error is thrown if this is not a C<COPY FROM
STDIN> operation. An error may be thrown if C<$data> is not a valid format
understood by Postgres, but such errors can also be deferred to C<close()>.
C<$data> is interpreted as a Perl Unicode string for textual transfers and as a
binary string for binary transfers.
=item $copy->read
Return the next row read from the Postgres server, or C<undef> if no more data
is coming. In the text format, a single line - including trailing newline - is
returned as a Perl Unicode string. In the binary format, a single row is
returned as a byte string. An error is thrown if this is not a C<COPY TO
STDOUT> operation.
=item $copy->close
Marks the end of the copy operation. Does not return anything but throws an
error if something went wrong.
It is possible to close a read-copy operation before all data has been
consumed, but that causes all data to still be read and discarded during
C<close()>. If you really want to interrupt a large read operation, a more
efficient approach is to call C<< $conn->disconnect >> and discard the entire
connection.
It is not I<necessary> to call this method, simply letting the C<$copy> object
run out of scope will do the trick as well, but in that case errors are
silently discarded. An explicit C<close()> is recommended to catch errors.
=back
=head1 Errors
All methods can throw an exception on error. When possible, the error message
is constructed using L<Carp>'s C<confess()>, including a full stack trace.
SQL errors and other errors from I<libpq> are reported with a C<FU::Pg::error>
object, which has the following fields:
=over
=item action
The action that was attempted, "connect", "prepare" or "exec".
( run in 0.675 second using v1.01-cache-2.11-cpan-d7a12ab2c7f )