.say for $pg.query('select * from foo').arrays;
.say for $pg.query('select * from foo').hashes;

If you have no placeholders/arguments and aren't retrieving
information, you can use execute. It does not PREPARE the query.

$pg.execute('insert into foo (x,y) values (1,2)');

Connection Caching

Database connection handles are created on demand, and cached for
reuse in a connection pool. Similarly, statement handles are
prepared, cached and reused.

When the first query is called, a new database connection will be
created. After the results are read from the connection, the
connection will be returned and cached in the pool. When a later
query is performed, that cached connection will be reused.

If multiple simultaneous queries occur, perhaps in different threads,
each will get a new connection so they won't interfere with one
another.

For example, you can perform database actions while iterating through
results from a query:

Connection caching is a nice convenience, but it does require some
care from the consumer. If you call query with an imperative
statement (insert, update, delete) the connection will
automatically be returned to the pool for re-use. For a query that
returns results, such as select, in order to reliably return the
connection to the pool for the next user, you must do one of two
things:

Read all the results. Once the last returned row is read, the
database connection handle will automatically get returned for reuse.

Database

Though you can just call .query() on the main DB::Pg object,
sometimes you want to explicitly manage the database connection. Use
the .db method to get a DB::Pg::Database object, and call
.finish explicitly on it to return it to the pool when you are
finished with it.

The database object also has .query() and .execute() methods, they
just don't automatically .finish to return the handle to the pool.
You must explicitly do that after use.

The begin/commit ensure that the statements between them happen
atomically, either all or none.

Transactions can also dramatically improve performance for some
actions, such as performing thousands of inserts/deletes/updates since
the indices for the affected table can be updated in bulk once for the
entire transaction.

If you .finish the database prior to a .commit, an uncommitted
transaction will automatically be rolled back.

As a convenience, .commit also returns the database object, so you
can just $db.commit.finish.

Cursors

When a query is performed, all the results from that query are
immediately returned from the server to the client. For exceptionally
large queries, this can be problematic, both waiting the time for the
whole query to execute, and the memory for all the
results. Cursors
provide a better way.

The cursor method will fetch N rows at a time from the server (can
be controlled with the :fetch parameter, defaults to 1,000). The
:hash parameter can be used to retrieve hashes for the rows instead
of arrays.

Bulk Copy Out

Listen/Notify

PostgreSQL also supports an asynchronous
LISTEN
command that you can use to receive notifications from the database.
The .listen() method returns a supply that can be used within a
react block. You can listen to multiple channels, and all listens
will share the same database connection.

There are three parts to this conversion. First the BUILD adds the
type mappings, then there are two methods, the first converts from a
string (Str:D) to a JSON:U type. The second will be used when a
parameter requires a JSON object. If the object already has a Str
method that results in a suitable string for PostgreSQL (often the
case), the second method can be omitted. (Or if you are only reading
a type from the database, and never passing it to the server.)

Several Converters are bundled with this module, and by default
they are added to the Converter automatically:

DateTime (date, timestamp, timestamptz -> Date, DateTime)

JSON (json, jsonb)

UUID (uuid -> UUID via LibUUID)

Geometric (point, line, lseg, box, path, polygon, circle)

The Geometric types are available in DB::Pg::GeometricTypes.

If you don't want any of those converters, just pass in an empty
converters array, or with just the ones you want:

my $pg = DB::Pg.new(converters => <DateTime JSON>)

If you want a different type of conversion than those canned types,
just exclude the default one and install your own as above.