query

Executing a query

There are three ways to execute a query:

• Call \sad_spirit\pg_wrapper\Connection::execute() with SQL string (this uses pg_query() internally)
• Call \sad_spirit\pg_wrapper\Connection::executeParams() with SQL string containing placeholders ($1, $2, ...) and separate parameter values (this uses pg_query_params() internally)
• Create an instance of \sad_spirit\pg_wrapper\PreparedStatement by calling Connection::prepare() with SQL string containing placeholders and then call PreparedStatement::execute() providing separate parameter values (this uses pg_prepare() and pg_execute() internally)

All of these return an instance of \sad_spirit\pg_wrapper\ResultSet for queries that return results or a number of affected rows otherwise.

Query parameters

While it is possible to insert query parameters directly into SQL string

$sql = 'select * from articles where article_title ~ ' . $connection->quote($titleFilter)
       . ' or article_author = ' . $connection->quote($authorId);
$res = $connection->execute($sql);

this approach may lead to security issues if quote() is not applied thoroughly. It is only recommended to use Connection::execute() for queries that do not have parameters.

On the other hand using prepare() / execute() workflow may lead to performance issues with single queries:

• prepare() and execute() require two round-trips to the database instead of one.
• On pre-9.2 PostgreSQL the query plan is created on prepare() stage and it does not use information about parameter values, this can obviously lead to sub-optimal plans being generated. On PostgreSQL 9.2+ this is less of an issue as prepared statements may use parameter values.

So prepare() / execute() are best used for queries that are executed multiple times with different parameters, especially for complex queries where time spent on parsing / planning stage is substantial.

If a query contains parameters and will be executed only once the best approach is executeParams():

$res = $connection->executeParams(
    'select * from articles where article_title ~ $1 or article_author = $2',
    [$titleFilter, $authorId]
);

This gives the benefits of previous approaches without their shortcomings:

• Parameters are passed separately from query, this makes SQL injection far less likely.
• Only one DB round-trip is required.
• Parameters' values are used by planner when building the query plan.

Specifying parameter types

Query execution methods that accept parameters (\sad_spirit\pg_wrapper\Connection::executeParams() and \sad_spirit\pg_wrapper\PreparedStatement::execute()) also accept type specifications for these parameters:

$result = $connection->executeParams(
    'select * from articles where article_id = any($1::integer[])',
    [[1, 2, 3]],
    ['integer[]']
);

PreparedStatement also accept type specifications for its bindValue() and bindParam() methods:

$prepared = $connection->prepare(
    'select * from articles where article_id = any($1::integer[])'
);
$prepared->bindValue(1, [4, 5, 6], new ArrayConverter(new IntegerConverter()));

These type specifications are processed by an implementation of TypeConverterFactory set for Connection via setTypeConverterFactory() method. The default implementation (aptly named \sad_spirit\pg_wrapper\converters\DefaultTypeConverterFactory) will accept either of the following

• TypeConverter instance. Its properties will be updated from current Connection object if needed (e.g. date and time converters will use DateStyle setting of connected database).
• Type name as string. A minimal parser is implemented, so schema-qualified names like 'pg_catalog.int4', double-quoted identifiers like '"CamelCaseType"', SQL standard names like 'CHARACTER VARYING' will be understood.
• Composite type specification as an array 'column' => 'column type specification'

It is not necessary to provide type information for every parameter: some may be skipped or type info omitted altogether. In this case an attempt will be made to guess which converter to use based on PHP variable type.

As a rule of thumb: you must specify the type if the parameter is an array, as guessing will definitely fail. If the parameter is a scalar or an instance of known class then guessing will probably work.

Specifying result column types

Generally you don't need to specify types for columns in query result: these are deduced from DB metadata.

One notable exception is a column defined by a row type constructor:

$composite = $conn->execute("select ROW('fuzzy dice', 42, 1.99) as needstype");
var_dump($composite[0]['needstype']);

the above will output

string(22) "("fuzzy dice",42,1.99)"

To provide necessary type information for a ResultSet you can either pass it to execute() / executeParams():

$composite = $conn->execute(
    "select ROW('fuzzy dice', 42, 1.99) as needstype",
    [['text', 'int4', 'float8']]
);
var_dump($composite[0]['needstype']);

or call setType() on the ResultSet instance:

$composite = $conn->execute("select ROW('fuzzy dice', 42, 1.99) as needstype");
$composite->setType('needstype', ['text', 'int4', 'float8']);
var_dump($composite[0]['needstype']);

both of the above will output

array(3) {
  [0] =>
  string(10) "fuzzy dice" 
  [1] =>
  int(42)
  [2] =>
  double(1.99)
}