Re: Foreign Database Connectivity

Tom Lane wrote:

>>What about Oracle's Create database link syntax?
>>
>>
>
>What about it? Does anyone beside Oracle support it? Can we implement
>it without running afoul of Oracle patents? Quite honestly, I'm afraid
>to base any feature on "this is how Oracle does it", first because it's
>foolish to shoot at a target that the competition can move at will, and
>second because I know that sooner or later they are going to be looking
>for ways to nail us for patent infringement.
>
>
>
A real concern for sure. OK, but...

Supporting the SQL/MED syntax will take *a lot* of work, where as a
simpler PG-Only feature can be developed as a contrib. I think there is
a *need* for the ability, but not nessisarily a requirement for a
specific implementation.

So, lets assume that the core PG crowd hates what ever it is that would
get built to do this. No worries, its not the first time :) Lets forget
that it is a feature that you do not like, and I am asking for a more
"generic" feature for functions returning sets.

How about this: (a varient)

CREATE [OR REPLACE] FUNCTION name (args)
RETURNS setof (mycol1 integer, mycol2 varchar)
LANGUAGE langname
.....
WITH (attribute, param1='param1', param2='param2',...)

Now, what would be cool, is if there was a way for the RETURNS specifier
to be passed to the function in some easy to use preparsed form. So that
the function could "know" what it was supposed to return and the name of
the field, it is important for the function to know the data type and
its name.

The "WITH" attributes could provide one more attribute, a parameter
which could be passed to the function. his will allow functions to do
virtually anything, for instance:

create function "ODBC_Music" (varchar) returns setof (id integer, title
varchar, artist varchar)
as 'mydll.so', 'ODBC_Music'
LANGUAGE C
WITH(STABLE, param1='NAME:DBUSER;DSN:FREEDB;AUTH:FUBAR', param2='select
* from sometable where title = ''%s''');

Would this implementation take much *any* real work? If PostgreSQL had
this, then external DB access would be trivial to implement. More
importantly, it makes it easier for a DBA to use an ISV's data link
function. Also, the "RETURNS" parameter could/should be he standard
CREATE TABLE syntax.