libpqxx
7.5.1
|
Efficiently write data directly to a database table. More...
#include <stream_to.hxx>
Public Member Functions | |
Use | table () or raw_table() factory.") stream_to(transaction_base &tx |
Create a stream, without specifying columns. More... | |
template<typename Iter > | |
Use | table () or raw_table() factory.") stream_to( transaction_base & |
Create a stream, specifying column names as a sequence of strings. More... | |
~stream_to () noexcept | |
operator bool () const noexcept | |
Does this stream still need to complete() ? More... | |
bool | operator! () const noexcept |
Has this stream been through its concluding complete() ? More... | |
void | complete () |
Complete the operation, and check for errors. More... | |
template<typename Row > | |
stream_to & | operator<< (Row const &row) |
Insert a row of data. More... | |
stream_to & | operator<< (stream_from &) |
Stream a stream_from straight into a stream_to . More... | |
template<typename Row > | |
void | write_row (Row const &row) |
Insert a row of data, given in the form of a std::tuple or container. More... | |
template<typename... Ts> | |
void | write_values (Ts const &...fields) |
Insert values as a row. More... | |
Static Public Member Functions | |
static stream_to | raw_table (transaction_base &tx, std::string_view path, std::string_view columns="") |
Stream data to a pre-quoted table and columns. More... | |
static stream_to | table (transaction_base &tx, table_path path, std::initializer_list< std::string_view > columns={}) |
Create a stream_to writing to a named table and columns. More... | |
Public Attributes | |
Use std::string_view | table_name: stream_to{tx |
Use std::string_view | sv |
Use std::string_view Iter | columns_begin |
Use std::string_view Iter Iter | columns_end |
Efficiently write data directly to a database table.
If you wish to insert rows of data into a table, you can compose INSERT statements and execute them. But it's slow and tedious, and you need to worry about quoting and escaping the data.
If you're just inserting a single row, it probably won't matter much. You can use prepared or parameterised statements to take care of the escaping for you. But if you're inserting large numbers of rows you will want something better.
Inserting rows one by one using INSERT statements involves a lot of pointless overhead, especially when you are working with a remote database server over the network. You may end up sending each row over the network as a separate query, and waiting for a reply. Do it "in bulk" using stream_to
, and you may find that it goes many times faster. Sometimes you gain orders of magnitude in speed.
Here's how it works: you create a stream_to
stream to start writing to your table. You will probably want to specify the columns. Then, you feed your data into the stream one row at a time. And finally, you call the stream's complete()
to tell it to finalise the operation, wait for completion, and check for errors.
(You must complete the stream before committing or aborting the transaction. The connection is in a special state while the stream is active, where it can't process commands, and can't commit or abort a transaction.)
So how do you feed a row of data into the stream? There's several ways, but the preferred one is to call its write_values
. Pass the field values as arguments. Doesn't matter what type they are, as long as libpqxx knows how to convert them to PostgreSQL's text format: int
, std::string
or std:string_view
, float
and double
, bool
... lots of basic types are supported. If some of the values are null, feel free to use std::optional
, std::shared_ptr
, or std::unique_ptr
.
The arguments' types don't even have to match the fields' SQL types. If you want to insert an int
into a DECIMAL
column, that's your choice – it will produce a DECIMAL
value which happens to be integral. Insert a float
into a VARCHAR
column? That's fine, you'll get a string whose contents happen to read like a number. And so on. You can even insert different types of value in the same column on different rows. If you have a code path where a particular field is always null, just insert nullptr
.
There is another way to insert rows: the <<
("shift-left") operator. It's not as fast and it doesn't support variable arguments: each row must be either a std::tuple
or something iterable, such as a std::vector
, or anything else with a begin
and end
.
pqxx::transaction_focus
active on it at a time.
|
noexcept |
References pqxx::text.
void pqxx::stream_to::complete | ( | ) |
Complete the operation, and check for errors.
Always call this to close the stream in an orderly fashion, even after an error. (In the case of an error, abort the transaction afterwards.)
The only circumstance where it's safe to skip this is after an error, if you're discarding the entire connection.
|
noexcept |
Does this stream still need to complete()
?
|
noexcept |
Has this stream been through its concluding complete()
?
stream_to& pqxx::stream_to::operator<< | ( | Row const & | row | ) |
Insert a row of data.
Returns a reference to the stream, so you can chain the calls.
The row
can be a tuple, or any type that can be iterated. Each item becomes a field in the row, in the same order as the columns you specified when creating the stream.
If you don't already happen to have your fields in the form of a tuple or container, prefer write_values
. It's faster and more convenient.
References pqxx::operator<<().
pqxx::stream_to & pqxx::stream_to::operator<< | ( | stream_from & | tr | ) |
Stream a stream_from
straight into a stream_to
.
This can be useful when copying between different databases. If the source and the destination are on the same database, you'll get better performance doing it all in a regular query.
References pqxx::stream_from::get_raw_line().
|
static |
Stream data to a pre-quoted table and columns.
This factory can be useful when it's not convenient to provide the columns list in the form of a std::initializer_list
, or when the list of columns is simply not known at compile time.
Also use this if you need to create multiple streams using the same table path and/or columns list, and you want to save a bit of work on composing the internal SQL statement for starting the stream. It lets you compose the string representations for the table path and the columns list, so you can compute these once and then re-use them later.
tx | The transaction within which the stream will operate. |
path | Name or path for the table upon which the stream will operate. If any part of the table path may contain special characters or be case-sensitive, quote the path using pqxx::connection::quote_table(). |
columns | Columns to which the stream will write. They should be comma-separated and, if needed, quoted. You can produce the string using pqxx::connection::quote_columns(). If you omit this argument, the stream will write all columns in the table, in schema order. |
|
static |
Create a stream_to
writing to a named table and columns.
Use this to stream data to a table, where the list of columns is known at compile time.
tx | The transaction within which the stream will operate. |
path | A table_path designating the target table. |
columns | Optionally, the columns to which the stream should write. If you do not pass this, the stream will write to all columns in the table, in schema order. |
References pqxx::transaction_base::conn().
Use pqxx::stream_to::table | ( | ) | & |
Create a stream, without specifying columns.
table()
or raw_table()
as a factory.Fields will be inserted in whatever order the columns have in the database.
You'll probably want to specify the columns, so that the mapping between your data fields and the table is explicit in your code, and not hidden in an "implicit contract" between your code and your schema.
Use pqxx::stream_to::table | ( | ) | & |
Create a stream, specifying column names as a sequence of strings.
table()
or raw_table()
as a factory. void pqxx::stream_to::write_row | ( | Row const & | row | ) |
Insert a row of data, given in the form of a std::tuple
or container.
The row
can be a tuple, or any type that can be iterated. Each item becomes a field in the row, in the same order as the columns you specified when creating the stream.
The preferred way to insert a row is write_values
.
void pqxx::stream_to::write_values | ( | Ts const &... | fields | ) |
Insert values as a row.
This is the recommended way of inserting data. Pass your field values, of any convertible type.
References pqxx::is_null(), pqxx::transaction_base::quote_name(), pqxx::separated_list(), pqxx::size_buffer(), and pqxx::to_buf().
Use std::string_view Iter pqxx::stream_to::columns_begin |
Use std::string_view Iter Iter pqxx::stream_to::columns_end |
Use std::string_view pqxx::stream_to::sv |
Use std::string_view pqxx::stream_to::table_name |