Switching Between Databases

I was not entirely sure how to frame my search of ODBC mailing database on
this topic so my apologies if it has been previously discussed at length.

When using the MySQL ODBC driver it is possible to frame an SQL query to
select a chosen database on the fly.within a single DSN Connection Instance

For example

Select * from weather.cities

Would select everything from the Cities Table within the Weather DB.

Alternatively

Use Weather
Select * from cities

Would do the same thing but require two SQL statements similar to psql which
would be

\c weather
Select * from cities

Question

Is it possible when using the PostgreSQL ODBC driver to switch back and
forth between different Databases within a single connection instance?

Mike

____________________________________________________________ _____
Advertisement: Make shopping exciting. Find what you want at www.eBay.com.au
http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Frover%2Eebay %2Ecom%2Frover%2F1%2F705%2D10129%2D5668%2D323%2F4%2F%3Fid%3D 6&_t=763807330&_r=email_taglines_EBAY&_m=EXT


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: Switching Between Databases

"Michael Peters" writes:
> Is it possible when using the PostgreSQL ODBC driver to switch back and
> forth between different Databases within a single connection instance?

No, but if you're looking for a MySQL substitute, our schemas act a
lot more like their databases than our databases do.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Switching Between Databases

On 21 Jul 2007 at 16:25, Michael Peters wrote:

> I was not entirely sure how to frame my search of ODBC mailing database on
> this topic so my apologies if it has been previously discussed at length.
>
> When using the MySQL ODBC driver it is possible to frame an SQL query to
> select a chosen database on the fly.within a single DSN Connection Instance
>
> For example
>
> Select * from weather.cities
>
> Would select everything from the Cities Table within the Weather DB.
>
> Alternatively
>
> Use Weather
> Select * from cities
>
> Would do the same thing but require two SQL statements similar to psql which
> would be
>
> \c weather
> Select * from cities
>
> Question
>
> Is it possible when using the PostgreSQL ODBC driver to switch back and
> forth between different Databases within a single connection instance?
>
> Mike

You can switch between schemas, but not between DB's. Which means that you
can accomplish the same thing, just approaching it differently.

Instead of multiple DB's just set up multiple schema's. Then the way to
chenage becomes:
Select item from schema1.table;
or
SET search_path TO schema1;
Select item from table;

That will select from schema1.table

You can also say something like:
SET search_path TO schema1,schema2,public;

and in that case any object will be chosen from the first schema (in the
defined sequence) in which it's found (with public being the default
schema).

Read up on schemas in the documentation for more info. But, at least at
the current time, a construct like dbname.schema.table isn't supported
(which is what would be needed for multiple DB support).

HTH

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly