Prepared statements

The two main benefits of using prepared statements are

Query parameters are passed separately from query text;

Query execution plan can be cached, this saves time for parsing / planning on subsequent executions.

Since Postgres offers a method to pass query text separately from parameters without the need to prepare, see executeParams() method described in the previous chapter, it mostly makes sense to use the below API for queries that will be executed multiple times.

`PreparedStatement` class

Note

Instances of this class are created by Connection::prepare() method, PreparedStatement::__construct() is marked internal and should not be used outside of Connection methods.

The statement is automatically prepared when an instance of PreparedStatement is created and automatically deallocated when the object is destroyed. Manual methods are also available just in case:

public function prepare(): $this

Actually prepares the statement with pg_prepare().

public function deallocate(): $this

Manually deallocates the prepared statement using DEALLOCATE ... SQL statement.

Trying to call execute() / executeParams() after deallocate() will result in an Exception.

A very useful method allows specifying the number of parameters in the query:

public function setNumberOfParameters(int $numberOfParameters): $this

Sets number of parameters used in the query.

Parameter symbols should start with $1 and have no gaps in numbers, otherwise Postgres will throw an error, so setting their number is sufficient.

// If we know the number of parameters...
$prepared->setNumberOfParameters(2);
// ...then all the below methods will throw exceptions
$prepared->executeParams([1, 2, 3]);
$prepared->bindValue(4, 'foo');
$prepared->setParameterType(5, 'integer');

Tip

Number of parameters will always be set to a correct value by fetchParameterTypes(), so there is no need to call setNumberOfParameters() unless automatic fetching of parameter types is disabled.

Supplying parameter values

There are two ways to supply parameters for a prepared statement, the first one is binding the parameters and calling execute()

public function bindValue(int $parameterNumber, mixed $value, mixed $type = null): $this

Sets the value for a parameter of a prepared query.

$parameterNumber is 1-based, $type contains specification of parameter type. An exception will be raised if $type is omitted / null and the parameter type is not already known.

public function bindParam(int $parameterNumber, mixed &$param, mixed $type = null): $this

Binds a variable to a parameter of a prepared query.

public function execute(): Result

Executes a prepared query using previously bound values. Note that the method does not accept arguments, all values should be bound.

$prepared = $connection->prepare('select * from foo where bar_id = $1 and foo_deleted = $2');
$result   = $prepared
    ->bindValue(1, 10)
    ->bindValue(2, false)
    ->execute();

The second way is just

public function executeParams(array $params): Result

Executes the prepared query using (only) the given parameters.

$params should have integer keys with (0-based) key N corresponding to (1-based) statement placeholder $(N + 1). Unlike native pg_execute(), array keys will be respected and values mapped by keys rather than in “array order”: passing ['foo', 'bar'] will use ‘foo’ for $1 and ‘bar’ for $2, while [1 => 'foo', 0 => 'bar'] will use ‘bar’ for $1 and ‘foo’ for $2.

$prepared = $connection->prepare('select * from foo where bar_id = $1 and foo_deleted = $2');
$result   = $prepared->executeParams([10, false]);

Note

These approaches are mutually exclusive, executeParams() will throw an exception if any parameter has a bound value.

Fetching parameter types automatically

By default, PreparedStatement gets the types of the query parameters from Postgres (specifically, from pg_prepared_statements system view), so there is no need to pass type specifications at all:

$prepared = $connection->prepare(
    'select * from pg_catalog.pg_type where oid = any($1) order by typname'
);
$result   = $prepared->executeParams([[16, 20, 603]]);

This behaviour is controlled by static methods

public static function setAutoFetchParameterTypes(bool $autoFetch): void: Sets whether parameter types should be automatically fetched after first preparing a statement.
public static function getAutoFetchParameterTypes(): bool: Returns whether parameter types will be automatically fetched after first preparing a statement. This defaults to true since version 3.0

Changing that setting will affect all PreparedStatement objects created afterwards.

The method that fetches types can also be called manually

public function fetchParameterTypes(bool $overrideExistingTypes = false): $this

Fetches info about the types assigned to query parameters from the database.

This method will always set parameter count to a correct value, but will not change existing type converters for parameters unless $overrideExistingTypes is true.

Specifying types manually

It is assumed that the statement will be executed multiple times and that types of parameters and result columns are quite unlikely to change between executions. Therefore, both query execution methods do not accept type specifications and executeParams() will throw an exception if a type for a parameter is not known.

Both parameter types and result types can be specified either when preparing a statement

$prepared = $connection->prepare(
    'select row(foo_id, foo_added) from foo where bar = any($1::integer[])',
    ['integer[]'],
    [['id' => 'integer', 'added' => 'timestamptz']]
);

or using the methods of PreparedStatement instance

public function setParameterType(int $parameterNumber, mixed $type): $this: Sets the type for a parameter of a prepared query.
public function setResultTypes(array $resultTypes): $this: Sets result types that will be passed to created Result instances.

Additionally, bindValue() and bindParam() accept type specifications as well.