CODEWITHSUNDEEP
phpsqlpostgresqlpdo
Marcio Mazzucato
Marcio Mazzucato

Reputation: 9285

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?

Upvotes: 14

Views: 10881

Answers (1)

Kouber Saparev
Kouber Saparev

Reputation: 8105

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;

Upvotes: 19

Related Questions