Is SELECT FROM multiple databases possible??? (USE db?)

From: J Kinsley <jkinsley(at)horus(dot)bticc(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Is SELECT FROM multiple databases possible??? (USE db?)
Date: 1999-06-24 23:40:46
Message-ID: Pine.LNX.4.02.9906241940260.2013-100000@horus.bticc.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Wayne Piekarski 1999-06-25 06:52:36 Re: trouble creating log table with rules
Previous Message J Kinsley 1999-06-24 23:32:48 Is SELECT FROM multiple databases possible??? (USE db?)