Prepared statements

Prepared statements are SQL queries that you define once and then invoke as many times as you like, typically with varying parameters. More...

Prepared statements are SQL queries that you define once and then invoke as many times as you like, typically with varying parameters.

It's basically a function that you can define ad hoc.

If you have an SQL statement that you're going to execute many times in quick succession, it may be more efficient to prepare it once and reuse it. This saves the database backend the effort of parsing complex SQL and figuring out an efficient execution plan. Another nice side effect is that you don't need to worry about escaping parameters.

You create a prepared statement by preparing it on the connection, passing an identifier and its SQL text. The identifier is the name by which the prepared statement will be known; it should consist of letters, digits, and underscores only and start with a letter. The name is case-sensitive.

void prepare_my_statement(pqxx::connection_base &c)
{
  c.prepare("my_statement", "SELECT * FROM Employee WHERE name = 'Xavier'");
}

Once you've done this, you'll be able to call my_statement from any transaction you execute on the same connection. Note that this uses a member function called "prepared"; the definition used a member function called "prepare".

pqxx::result execute_my_statement(pqxx::transaction_base &t)
{
  return t.prepared("my_statement").exec();
}

Did I mention that you can pass parameters to prepared statements? You define those along with the statement. The query text uses $1, $2 etc. as placeholders for the parameters in the SQL text. Since your C++ compiler doesn't know how many parameters you're going to define, the syntax that lets you do this is a bit strange:

void prepare_find(pqxx::connection_base &c)
{
  // Prepare a statement called "find" that looks for employees with a given
  // name (parameter 1) whose salary exceeds a given number (parameter 2).
  const std::string sql =
    "SELECT * FROM Employee WHERE name = $1 AND salary > $2";

  c.prepare("find", sql)()();
}

It's the ()() that declares the two parameters. If any of the parameters will be in binary form, you will pass treat_binary in the corresponding pair of parentheses.

When invoking the prepared statement, you pass parameter values using a similar syntax.

pqxx::result execute_find(
  pqxx::transaction_base &t, std::string name, int min_salary)
{
  return t.prepared("find")(name)(min_salary).exec();
}

Warning

There are cases where prepared statements are actually slower than plain SQL. Sometimes the backend can produce a better execution plan when it knows the parameter values. For example, say you've got a web application and you're querying for users with status "inactive" who have email addresses in a given domain name X. If X is a very popular provider, the best way to plan the query may be to list the inactive users first and then filter for the email addresses you're looking for. But in other cases, it may be much faster to find matching email addresses first and then see which of their owners are "inactive." A prepared statement must be planned to fit either case, but a direct query can be optimized based on table statistics, partial indexes, etc.