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.