Is it possible to do something like the following in PostgreSQL???
CREATE DATABASE pub;
CREATE TABLE states (st_abv CHAR(2),st_name VARCHAR(24)
);
INSERT INTO states VALUES('AL', 'Alabama');
...
INSERT INTO states VALUES('OH', 'Ohio');
...
INSERT INTO states VALUES('WY', 'Wyoming');
CREATE DATABASE accounts;
CREATE TABLE address (adr_id INT4,...adr_city VARCHAR(24),adr_state CHAR(2),...
);
INSERT INTO address VALUES ('1',...,'Akron','OH',...);
INSERT INTO address VALUES ('2',...,'Canton','OH',...);
-- Connect to accounts database
USE accounts; -- From MySQL
SELECT a.adr_id AS id, a.adr_city AS city, s.st_name as state FROM address a, pub.states s WHERE a.adr_state =
s.st_abv;
This query returns a parse error on the '.' in pub.states. If I move
the states table into the accounts and replace pub.states with
states, the query works properly. However, I would like to keep one
copy of states in the pub database instead of having to create a copy
in every databae that will use it.
While I chose the above as an example, I have other tables which I
would also like to store in a single database while being accessable
from an arbitrary number of other databases.
In MySQL, this can be accomplished with the USE statement which from
the documentation appears to be for Sybase compatibility. Since
there is no USE statement in PostgreSQL, is there another way to
achieve the desired results?
TIA
Regards,
Jarrod Kinsley
