On 11/10/06,
Tom Lane <
tgl@sss.pgh.pa.us> wrote:
"Dawid Kuroczko" <qnex42@gmail.com> writes:
> Methinks it would be good idea to discuss a more general approach,
> i.e. don't "create" links using existing infrastructure but rather
> provide something conceptually similar to CREATE AGGREGATE statements.
> In other words, make a remote link a first class object inside PostgreSQL.
Please see the archives, particularly past discussions of the SQL-MED
standard. We do not need to go inventing our own syntax for this.
Great! I was not aware of that part of SQL:2003. Anyway I managed
to find September 2003 draft of the standard (Wikipedia has a link)
and skimmed through it (I didn't find much discussion in the archives,
just one thread).
While skimming through the draft I made quick notes on the
commands they propose to provide the functionality. Here is
the short list (I've ommited ALTER and DROP statements,
and also DATALINK which I don't quite grok at this late hour).
First there is a command:
CREATE FOREIGN DATA WRAPPER wrapper LIBRARY ...;
which establishes machinery for accessing remote data.
I would think of it as CREATE LANGUAGE of remote links.
Then there is a command to create a server definition,
the "instance" of connection.
CREATE SEVER servername [ TYPE type ] [ VERSION ver ]
FOREIGN DATA WRAPPER wrapper;
And finally, when we have a SERVER, we can get some
foregin data:
CREATE FOREIGN TABLE tabname ( ...) SERVER servername;
Or do it in bulk:
IMPORT FOREIGN SCHEMA foreignschema
[ LIMIT TO (tab1,tab2,...) | EXCEPT (tab3,tab4,...) ]
FROM SERVER server INTO localschema;
And these, I am not sure how they should be used. User mapping
seems straightforward, but routine mapping seems cryptic to me.
CREATE ROUTINE MAPPING rmapname FOR routine
SERVER server;
CREATE USER MAPPING umapname FOR userident SERVER server;
Regards,
Dawid
PS: So, to create connection to "foreign" postgresql, one would need to:
CREATE FOREIGN DATA WRAPPER which probably should be backed
by a bunch of our plain old FUNCTIONs or a specialized dynamically
linked library.
Then CREATE SERVER giving it an URI to our database, say
psql://host:port/dbname (is there an "official" uri syntax for PostgreSQL?)
And then either CREATE FOREIGN TABLEs or simply IMPORT FOREIGN
SCHEMA somewhere.
This would be a "minimum" I guess.
