pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: J Kinsley <jkinsley@horus.bticc.net>
To: pgsql-sql@postgresql.org
Subject: Is SELECT FROM multiple databases possible??? (USE db?)
Date: Thu, 24 Jun 1999 19:40:46 -0400 (EDT)
Message-ID: <Pine.LNX.4.02.9906241940260.2013-100000@horus.bticc.net> (raw)

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 





view thread (2+ messages)

Message-ID: <Pine.LNX.4.02.9906241940260.2013-100000@horus.bticc.net>
Permalink:  ../Pine.LNX.4.02.9906241940260.2013-100000@horus.bticc.net/
Also on:    postgresql.org/message-id/Pine.LNX.4.02.9906241940260.2013-100000@horus.bticc.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: jkinsley@horus.bticc.net
  Subject: Re: Is SELECT FROM multiple databases possible??? (USE db?)
  In-Reply-To: <Pine.LNX.4.02.9906241940260.2013-100000@horus.bticc.net>
  References: <Pine.LNX.4.02.9906241940260.2013-100000@horus.bticc.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox