PostgreSQL: Changing the database without reconnecting?

saloon12yrd

Hey everybody...

I'm afraid this is quite a newbiequestion but hey, with PostgreSQL I really am 🙂.

For Sybase PHP provides the sybase_select_db() function to change the current database without the need to reconnect.

For MySQL PHP provides the same with the mysql_select_db() function. Alternatively you can do a "USE newdb" SQL statement which does the same.

Is there something like this for PostgreSQL as well? Obviously there's no pg_select_db() function and the USE statement doesn't work either. I've skimmed the PostgreSQL SQL reference but I'm not any smarter now.

So what did I overlook?

Thanks in advance,

Dominique

LordShryku

Only thing you can really do is define a new connect string. There isn't a valid database_choose function for Postgres...

Sxooter

If you need to access more than 1 db at a time, look at dblink in the contrib directory. It may let you do what you want.

But generally, a connection in postgresql is BY the database you're connected to, and you can't change the database after connect. Multiple connects are ok though.

LordShryku

I actually do multi connects in most of my scripts, and have never had a problem or any performance issue...

Sxooter

Yeah, I actually prefer this way too. So, I don't accidentally throw the wrong query in the wrong place, I can name my connectors something like:

$phonebook
$sales_info
$karma

etc...

Plus, my guess is that, based on the way postgresql is put together, a database switch could never be any faster than disconnection and reconnecting, because it would probably be limited to being the same thing in a wrapper.

That would mean open transactions need to get handled, and is the default behavior to roll back or commit on such a switch?

Now, as of postgresql 7.3 you have schemas, which means you can create a schema then access tables across multiple schemas in the same database. I.e. you get the data partitioning of seperate databases, but you don't have to jump through hoops with dblink anymore.

So, you have a schema sxooter and pie. You can join across like so, assuming you have permission, of course:

select * from sxooter.cupboard s natural join pie.sky;

So, maybe 7.3 could be providing the answer. It's up to 7.3.2 I think and with all the changes that went into 7.3, it would have certainly been 8.0 in the commercial world, but then again, so would 7.1. By the x.y.2 releases postgresql has always had all the show stopper bugs worked out in the past.