Re: Remote tables infrastructure.

On 11/10/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Dawid Kuroczko" <qnex42(at)gmail(dot)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.