Executing queries

There are three ways to execute a query:

  • Connection::execute() passing SQL string (this uses pg_query() internally);

  • Connection::executeParams() passing SQL string containing placeholders ($1, $2, …) and separate parameter values (this uses pg_query_params() internally);

  • Connection::prepare() passing SQL string (usually) containing placeholders to create an instance of PreparedStatement, then call PreparedStatement::execute() / PreparedStatement::executeParams() providing separate parameter values (this uses pg_prepare() and pg_execute() internally). This way is described in the next chapter.

All of the above methods return an instance of Result.

Passing query parameters

While it is possible to embed 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 has an obvious performance issue with queries that are executed once: it requires two round-trips to the database instead of one.

So prepare() / execute() are best used for queries that are executed multiple times with different parameters, especially for complex ones 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 database round-trip is required.

Specifying parameter types

Connection::executeParams() also accepts type specifications for parameters:

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

PreparedStatement will try to get proper parameter types from Postgres by default, but these can be overridden / specified manually via Connection::prepare(), PreparedStatement::setParameterType(), PreparedStatement::bindValue(), and PreparedStatement::bindParam().

$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 will accept either of the following:

  • Type name as string. As shown above, array types can be specified using square brackets: typename[].

  • Composite type specification as an array 'column' => 'column type specification'.

  • TypeConverter instance, it will receive current Connection to update its configuration, if needed.

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.

Tip

You must specify the type if the parameter is a PHP array as in above examples, guessing will definitely fail. If the parameter is a scalar or an instance of a 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 result 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)"

as Postgres specifies its type as a generic record pseudo-type. To provide necessary type information for a Result 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 Result 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)
}