NAME

FU::Pg - The Ultimate (synchronous) Interface to PostgreSQL

EXPERIMENTAL

This module is still in development and there will likely be a few breaking API changes, see the main FU module for details.

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->q('INSERT INTO books (title) VALUES ($1)', 'Revelation Space')->exec;
$conn->q('INSERT INTO books (title) VALUES ($1)', 'The Invincible')->exec;

for my ($id, $title) ($conn->q('SELECT * FROM books')->flat->@*) {
    print "$id:  $title\n";
}

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 libpq.

Connection setup

FU::Pg->connect($string)

Connect to the PostgreSQL server and return a new FU::Pg::conn object. $string can either be in key=value format or a URI, refer to the PostgreSQL documentation for the full list of supported formats and options. You may also pass an empty string and leave the configuration up environment variables.

$conn->server_version

Returns the version of the PostgreSQL server as an integer in the format of $major * 10000 + $minor. For example, returns 170002 for PostgreSQL 17.2.

$conn->lib_version

Returns the libpq version in the same format as the server_version method. Also available directly as FU::Pg::lib_version().

$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:

idle: Awaiting commands, not in a transaction.
txn_idle: Awaiting commands, inside a transaction.
txn_done: Idle, but a transaction object still exists. The connection is unusable until that object goes out of scope.
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.
bad: Connection is dead or otherwise unusable.

$conn->cache($enable)

$conn->text_params($enable)

$conn->text_results($enable)

$conn->text($enable)

Set the default settings for new statements created with $conn->q().

$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 $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.

$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 $st methods below for the fields that can be inspected. $sub can be set to undef to disable query tracing.

It is important to not hold on to the given $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 $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 before the query results have been returned.

The subroutine is (currently) only called for queries executed through $conn->exec, $conn->q, $conn->Q and their $txn variants; 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.

$conn->disconnect

Close the connection. Any active transactions are rolled back and further attempts to use $conn throw an error.

Querying

$conn->exec($sql)

Execute one or more SQL commands, separated by a semicolon. Returns the number of rows affected by the last statement or undef if that information is not available for the given command (like with CREATE TABLE).

$conn->q($sql, @params)

Create a new SQL statement with the given $sql string and an optional list of bind parameters. $sql can only hold a single statement.

Parameters can be referenced from $sql with numbered placeholders, where $1 refers to the first parameter, $2 to the second, etc. Be careful to not accidentally interpolate perl's $1 and $2. Using a question mark for placeholders, as is common with DBI, is not supported. An error is thrown when attempting to execute a query where the number of @params does not match the number of placeholders in $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.

$conn->Q(@args)

Same as $conn->q() but uses FU::SQL to construct the query and bind parameters.

Statement objects returned by $conn->q() support the following configuration parameters, which can be set before the statement is executed:

$st->cache($enable): Enable or disable caching of the prepared statement for this particular query.
$st->text_params($enable): Enable or disable sending bind parameters in the text format. See "Formats and Types" below for what this means.
$st->text_results($enable): Enable or disable receiving query results in the text format. See "Formats and Types" below for what this means.
$st->text($enable): Shorthand for setting text_params and text_results at the same time.

To execute the statement, call one (and exactly one) of the following methods, depending on how you'd like to obtain the results:

$st->exec

Execute the query and return the number of rows affected. Similar to $conn->exec.

my $v = $conn->q('UPDATE books SET read = true WHERE id = 1')->exec;
# $v = 1

$st->val

Return the first column of the first row. Throws an error if the query does not return exactly one column, or if multiple rows are returned. Returns undef if no rows are returned or if its value is NULL.

my $v = $conn->q('SELECT COUNT(*) FROM books')->val;
# $v = 2

$st->rowl

Return the first row as a list, or an empty list if no rows are returned. Throws an error if the query returned more than one row.

my($id, $title) = $conn->q('SELECT id, title FROM books LIMIT 1')->rowl;
# ($id, $title) = (1, 'Revelation Space');

$st->rowa

Return the first row as an arrayref, equivalent to [$st->rowl] but might be slightly more efficient. Returns undef if the query did not generate any rows.

my $row = $conn->q('SELECT id, title FROM books LIMIT 1')->rowa;
# $row = [1, 'Revelation Space'];

$st->rowh

Return the first row as a hashref. Returns undef if the query did not generate any rows. Throws an error if the query returns multiple columns with the same name.

my $row = $conn->q('SELECT id, title FROM books LIMIT 1')->rowh;
# $row = { id => 1, title => 'Revelation Space' };

$st->alla

Return all rows as an arrayref of arrayrefs.

my $data = $conn->q('SELECT id, title FROM books')->alla;
# $data = [
#   [ 1, 'Revelation Space' ],
#   [ 2, 'The Invincible' ],
# ];

$st->allh

Return all rows as an arrayref of hashrefs. Throws an error if the query returns multiple columns with the same name.

my $data = $conn->q('SELECT id, title FROM books')->allh;
# $data = [
#   { id => 1, title => 'Revelation Space' },
#   { id => 2, title => 'The Invincible' },
# ];

$st->flat

Return an arrayref with all rows flattened.

my $data = $conn->q('SELECT id, title FROM books')->flat;
# $data = [
#   1, 'Revelation Space',
#   2, 'The Invincible',
# ];

$st->kvv

Return a hashref where the first result column is used as key and the second column as value. If the query only returns a single column, true is used as value instead. An error is thrown if the query returns 3 or more columns.

my $data = $conn->q('SELECT id, title FROM books')->kvv;
# $data = {
#   1 => 'Revelation Space',
#   2 => 'The Invincible',
# };

$st->kva

Return a hashref where the first result column is used as key and the remaining columns are stored as arrayref.

my $data = $conn->q('SELECT id, title, read FROM books')->kva;
# $data = {
#   1 => [ 'Revelation Space', true ],
#   2 => [ 'The Invincible', false ],
# };

$st->kvh

Return a hashref where the first result column is used as key and the remaining columns are stored as hashref.

my $data = $conn->q('SELECT id, title, read FROM books')->kvh;
# $data = {
#   1 => { title => 'Revelation Space', read => true },
#   2 => { title => 'The Invincible', read => false },
# };

The only time you actually need to assign a statement object to a variable is when you want to inspect the statement using one of the methods below, in all other cases you can chain the methods for more concise code. For example:

my $data = $conn->q('SELECT a, b FROM table')->cache(0)->text->alla;

Statement objects can be inspected with the following methods (many of which only make sense after the query has been executed):

$st->query

Returns the SQL query that the statement was created with.

$st->param_values

Returns the provided bind parameters as an arrayref.

$st->param_types

Returns an arrayref of integers indicating the type (as oid) of each parameter in the given $sql string. Example:

my $oids = $conn->q('SELECT id FROM books WHERE id = $1 AND title = $2')->param_types;
# $oids = [23,25]

my $oids = $conn->q('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 text_params is enabled.

$st->columns

Returns an arrayref of hashrefs describing each column that the statement returns.

my $cols = $conn->q('SELECT id, title FROM books')->columns;
# $cols = [
#   { name => 'id', oid => 23 },
#   { name => 'title', oid => 25 },
# ]

$st->nrows

Number of rows returned by the query.

$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.

$st->prepare_time

Observed query preparation time, in seconds, including network round-trip. Returns 0 if a cached prepared statement was used or undef if the query was executed without a separate preparation phase (currently only happens with $conn->exec(), but support for direct query execution may be added for other queries in the future as well).

$st->get_cache

$st->get_text_params

$st->get_text_results

Returns the respective configuration parameters.

Transactions

This module provides a convenient and safe API for scoped transactions and subtransactions. A new transaction can be started with $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 $txn->commit has been performed. Any attempts to run queries on the parent $conn object will fail while a transaction object is alive.

{
  # start a new transaction
  my $txn = $conn->txn;

  # run queries
  $txn->q('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:

$txn->exec(..)

$txn->q(..)

$txn->Q(..)

Run a query inside the transaction. These work the same as the respective methods on the parent $conn object.

$txn->commit

$txn->rollback

Commit or abort the transaction. Any attempts to run queries on this transaction object after this call will throw an error.

Calling rollback is optional, the transaction is automatically rolled back when the object goes out of scope.

$txn->cache($enable)

$txn->text_params($enable)

$txn->text_results($enable)

$txn->text($enable)

Set the default settings for new statements created with $txn->q().

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.

$txn->txn

Create a subtransaction within the current transaction. A subtransaction works exactly the same as a top-level transaction, except any changes remain invisible to other sessions until the top-level transaction has been committed.

$txn->status

Like $conn->status, but with the following status codes:

idle

Current transaction is active and awaiting commands.

done

Current transaction has either been committed or rolled back, further commands will throw an error.

error

Current transaction is in error state and must be rolled back.

txn_idle

A subtransaction is active and awaiting commands. The current transaction is not usable until the subtransaction goes out of scope.

(This status code is also returned when the subtransaction is 'done', the current implementation does not track subtransactions that closely)

txn_error

A subtransaction is in error state and awaiting to be rolled back.

bad

Connection is dead or otherwise unusable.

Of course, if you prefer the old-fashioned manual approach to transaction handling, that is still available:

$conn->exec('BEGIN');
# We're now inside a transaction
$conn->exec('COMMIT') or $conn->exec('ROLLBACK');

Just don't try to use transaction objects and manual transaction commands at the same time, that won't end well.

Formats and Types

The PostgreSQL wire protocol supports sending bind parameters and receiving query results in two different formats: text and binary. While the exact wire protocol is an implementation detail that you don't have to worry about, this module does have a different approach to processing the two formats.

When you enable text mode, your bind parameters are sent verbatim, as text, to the PostgreSQL server, where they are then parsed, validated and interpreted. Likewise, when receiving query results in text mode, it is the PostreSQL server that is formatting the data into textual strings. Text mode is essentially a way to tell this module: "don't try to interpret my data, just send and receive everything as text!"

Instead, in the (default) binary mode, the responsibility of converting Postgres data to and from Perl values lies with this module. This allows for a lot of type-specific conveniences, but has the downside of requiring special code for every PostgreSQL type. Most of the core types are supported by this module and convert in an intuitive way, but you can also configure each type manually:

$conn->set_type($target_type, $type)

$conn->set_type($target_type, send => $type, recv => $type)

Change how $target_type is being converted when used as a bind parameter (send) or when received from query results (recv). The two-argument version is equivalent to setting send and recv to the same $type.

Types can be specified either by their numeric Oid or by name. In the latter case, the name must exactly match the internal type name used by PostgreSQL. Note that this "internal type name" does not always match the names used in documentation. For example, smallint, integer and bigint should be specified as int2, int4 and int8, respectively, and the char type is internally called bpchar. The full list of recognized types in your database can be queried with:

SELECT oid, typname FROM pg_type;

The $target_type does not have to exist in the database when this method is called. This method only stores the type in its internal configuration, which is consulted when executing a query that takes the type as bind parameter or returns a column of that type.

The following arguments are supported for $type:

undef, to reset the conversion functions to their default.
The numeric Oid or name of a built-in type supported by this module, to use those conversion functions.
A subroutine reference that is called to perform the conversion. For send, the subroutine is given a Perl value as argument and expected to return a binary string to be sent to Postgres. For recv, the subroutine is given a binary string received from Postgres and expected to return a Perl value.

Some built-in types deserve a few additional notes:

bool

Boolean values are converted to builtin::true and builtin::false. As bind parameters, Perl's idea of truthiness is used: 0, false and "" are false, everything else is true. Objects that overload bool are also supported. undef always converts to SQL NULL.

bytea

The bytea type represents arbitrary binary data and this module will pass that along as raw binary strings. If you prefer to work with hex strings instead, use:

$conn->set_type(bytea => '$hex');

The bytea and the $hex (pseudo-)types can be applied to any other type to convert between the PostgreSQL binary wire format and Perl strings. For example, if you prefer to receive integers as big-endian hex strings, you can do that:

$conn->set_type(int4 => recv => '$hex');

Or to treat UUIDs as 16-byte strings:

$conn->set_type(uuid => 'bytea');

timestamp / timestamptz

These are converted to and from seconds since the Unix epoch as a floating point value, for easy comparison against time() and related functions.

The timestamp types in Postgres have microsecond accuracy. Floating point can represent that without loss for dates that are near enough to the epoch (still seems to be fine in 2025, at least), but this conversion may be lossy for dates far beyond or before the epoch.

Postgres internally represents timestamps as microseconds since 2000-01-01 stored in a 64-bit integer. If you prefer that, use:

$conn->set_type(timestamptz => 'int8');

date

Converted between seconds since Unix epoch as an integer, with the time fixed at 00:00:00 UTC. When used as bind parameter, the time part is truncated. This format makes for easy comparison with other timestamps, but if you prefer to work with strings in the YYYY-MM-DD format instead, use:

$conn->set_type(date => '$date_str');

Postgres accepts a bunch of alternative date formats for bind paramaters, this module does not.

time

Converted between floating point seconds since 00:00:00, supporting microsecond precision. This format allows for easy comparison against Unix timestamps (time of day = $timestamp % 86400) and can be added to an integer date value to form a complete timestamp.

(There's no support for the string format yet)

json / jsonb

These types are converted through json_parse() and json_format() from FU::Util.

While null is a valid JSON value, there's currently no way to distinguish that from SQL NULL. When sending undef as bind parameter, it is sent as SQL NULL.

If you prefer to work with JSON are raw text values instead, use:

$conn->set_type(json => 'text');

That doesn't quite work for the jsonb type. I mean, it works, but then there's a single "\1" byte prefixed to the string.

arrays

PostgreSQL arrays automatically convert to and from Perl arrays as you'd expect. Arrays in PostgreSQL have the rather unusual feature that the starting index can be changed for each individual array, but this module doesn't support that. All arrays received from Postgres will use Perl's usual 0-based indexing and all arrays sent to Postgres will use their default 1-based indexing.

records / row types

Typed records are converted to and from hashrefs. Untyped records (i.e. values of the record pseudo-type) are not supported.

domain types

These are recognized and automatically converted to and from their underlying type. It may be tempting to use set_type() to configure special type conversions for domain types, but beware that PostgreSQL reports columns in the SELECT clause of a query as being of the underlying type rather than the domain type, so the conversions will not apply in that case. They do seem to apply when the domain type is used as bind parameter, array element or record field. This is an (intentional) limitation of PostgreSQL, sadly not something I can work around.

geometric types

numeric

macaddr

money

timetz

bit / varbit

tsvector / tsquery

range / multirange

Extension types

These are not supported at the moment. Not that they're hard to implement (I think), I simply haven't looked into them yet. Open a bug report if you need any of these.

As a workaround, you can always switch back to the text format or use set_type() to configure appropriate conversions for these types.

TODO: Methods to convert between the various formats.

TODO: Methods to query type info.

Errors

All methods can throw an exception on error. When possible, the error message is constructed using Carp's confess(), including a full stack trace.

SQL errors and other errors from libpq are reported with a FU::Pg::error object, which has the following fields:

action: The action that was attempted, "connect", "prepare" or "exec".
query: The query that was being prepared or executed, if any.
message: Human-readable error message.
verbose_message: More verbose message, usually consisting of multiple lines.
severity
detail
hint
statement_position
internal_position
internal_query
context
schema_name
table_name
column_name
datatype_name
constraint_name
source_file
source_line
source_function: These correspond to error fields from PQresultErrorField().

LIMITATIONS

Does not support older versions of libpq or PostgreSQL. Currently only tested with version 17, but versions a bit older than that ought to work fine as well. Much older versions will certainly not work fine.
(Probably) not thread-safe.
Only supports the UTF-8 encoding for all text strings sent to and received from the PostgreSQL server. The encoding is assumed to be UTF-8 by default, but if this may not be the case in your situation, setting client_encoding=utf8 as part of the connection string or manually switching to it after connect() is always safe:
```
my $conn = FU::Pg->connect('');
$conn->exec('SET client_encoding=utf8');
```
Only works with blocking (synchronous) calls, not very suitable for use in asynchronous frameworks unless you know your queries are fast and you have a low-latency connection with the Postgres server.

Missing features:

COPY support: I hope to implement this someday.
LISTEN support: Would be nice to have, most likely doable without going full async.
Asynchronous calls: Probably won't happen. Perl's async story is slightly awkward in general, and fully supporting async operation might require a fundamental redesign of how this module works. It certainly won't simplify the implementation.
Pipelining: I have some ideas for an API, but doubt I'll ever implement it. Suffers from the same awkwardness and complexity as asynchronous calls.

COPYRIGHT

MIT.

AUTHOR

Yorhel <projects@yorhel.nl>

To install FU, copy and paste the appropriate command in to your terminal.

cpanm

cpanm FU

CPAN shell

perl -MCPAN -e shell
install FU

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)