Marcio Mazzucato Marcio Mazzucato - 6 months ago 21
SQL Question

Best alternative to set a PostgreSQL schema using PHP PDO

I am using PHP PDO to access a PostgreSQL database with various schemas, so first i create a connection and then i set the correct schema, like below:

$Conn = new PDO('pgsql:host=localhost;port=5432;dbname=db', 'user', 'pass');

$result = $Conn->exec('SET search_path TO accountschema');

if ( ! $result) {
die('Failed to set schema: ' . $Conn->errorMsg());
}


Is this a good practice? Is there a better way to do this?

Answer

In order to specify the default schema you should set the search_path instead.

$Conn->exec('SET search_path TO accountschema');

You can also set the default search_path per database user and in that case the above statement becomes redundant.

ALTER USER user SET search_path TO accountschema;