Thread: Foreign Database Connectivity
I have been thinking about this for a while. Now that we have a practical methodology for returning full rows from functions, we could make this sort of thing pretty "easily." How should it look? How much work is worth putting into it? My thinking is that there is a module in contrib or something that enables this. The foreign DB link module should be able to load external DB drivers, this way anyone can add a new DB to the system. We should start with DBF files, ODBC, and of course, PostgreSQL. Does it need to look like a table or can we get away with it being a function? If we make it a function that's easier. Any thoughts?
mlw <pgsql@mohawksoft.com> writes:
> Any thoughts?
See previous discussion of SQL-MED standard. I'd prefer to see us try
to implement a standard than invent something off-the-cuff ...
regards, tom lane
> I have been thinking about this for a while. Now that we have a > practical methodology for returning full rows from functions, we could > make this sort of thing pretty "easily." Does postgres now support returning multiple rows / tuples from a stored procedure / function?
Tom Lane wrote: >mlw <pgsql@mohawksoft.com> writes: > > >>Any thoughts? >> >> > >See previous discussion of SQL-MED standard. I'd prefer to see us try >to implement a standard than invent something off-the-cuff ... > > > Does anyone implement this? I have never seen it before. What about Oracle's Create database link syntax?
On Tue, 2003-04-15 at 20:04, Rob Butler wrote: > Does postgres now support returning multiple rows / tuples from a stored > procedure / function? Yes (since PostgreSQL 7.3). Cheers, Neil
mlw <pgsql@mohawksoft.com> writes:
> Tom Lane wrote:
>> See previous discussion of SQL-MED standard. I'd prefer to see us try
>> to implement a standard than invent something off-the-cuff ...
> Does anyone implement this?
Couldn't say. It's a relatively new standard. That hasn't stopped us
from implementing SQL99 stuff though, nor in some cases SQL200x stuff
that the ink is still wet on...
> 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.
regards, tom lane
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.
mlw <pgsql@mohawksoft.com> writes:
> 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.
Just create a rowtype and declare the function as returning that.
With Joe's recent additions for polymorphic functions, it's even
possible for the function to discover what it's supposed to return
at runtime. (Hey Joe, did we make that work for functions called
from the FROM clause? If not, seems like something to fix up.)
> The "WITH" attributes could provide one more attribute, a parameter
> which could be passed to the function.
Actually, the way that you probably ought to build it is as a new PL
language type. All the stuff you are thinking of as WITH parameters
would be inside the "function body" in some trivial syntax. I think
this could likely even be built in 7.3, without the polymorphic
functions (PL handlers are already polymorphic ...)
regards, tom lane
Tom Lane wrote: > mlw <pgsql@mohawksoft.com> writes: >>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. > > Just create a rowtype and declare the function as returning that. Or for flexibility use "RETURNS setof record" > With Joe's recent additions for polymorphic functions, it's even > possible for the function to discover what it's supposed to return > at runtime. (Hey Joe, did we make that work for functions called > from the FROM clause? If not, seems like something to fix up.) Yeah -- the best example is the new hash based crosstab function in contrib/tablefunc: CREATE OR REPLACE FUNCTION crosstab(text,text) RETURNS setof record AS 'MODULE_PATHNAME','crosstab_hash' LANGUAGE 'C' STABLE STRICT; create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text); <insert data> SELECT * FROM crosstab ( 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 'SELECT DISTINCT attribute FROM cth ORDER BY 1' ) AS (rowid text, rowdt timestamp, temp int4, res text, startdate timestamp, volts float8); In this case crosstab_hash() gets its tupdesc from rsinfo->expectedDesc. It then uses the output of SPI_getvalue and the tupdesc to build the new tuple via BuildTupleFromCStrings() >>The "WITH" attributes could provide one more attribute, a parameter >>which could be passed to the function. > > Actually, the way that you probably ought to build it is as a new PL > language type. All the stuff you are thinking of as WITH parameters > would be inside the "function body" in some trivial syntax. I think > this could likely even be built in 7.3, without the polymorphic > functions (PL handlers are already polymorphic ...) Sounds like an interesting approach -- actually PL/R uses the polymorphic abilities pretty extensively too. For example when the R function returns a "data frame", PL/R uses the runtime return type to decide what to do with it. As far as foreign database connectivity goes specifically, someone has already done a proof of concept "jdbclink" based on dblink (which he sent to me). If I can find the time before the 7.4 feature freeze, I'm going to try to merge his code into dblink so that dblink can access any jdbc data source. Beyond that, I think heading down the SQL-MED road is the way to go. Joe
Joe Conway wrote: > Tom Lane wrote: >> With Joe's recent additions for polymorphic functions, it's even >> possible for the function to discover what it's supposed to return >> at runtime. (Hey Joe, did we make that work for functions called >> from the FROM clause? If not, seems like something to fix up.) > > Yeah -- the best example is the new hash based crosstab function in > contrib/tablefunc: > Dooh! I just reread what you wrote. Your referring to the FuncExpr addition to the FmgrInfo struct. I'll have to check that out and let you know. I'm getting ready to dive back into that stuff in the next day or so. Joe
Tom Lane wrote: > With Joe's recent additions for polymorphic functions, it's even > possible for the function to discover what it's supposed to return > at runtime. (Hey Joe, did we make that work for functions called > from the FROM clause? If not, seems like something to fix up.) I'm just getting back into the polymorphic-functions/array-expression changes this weekend. To follow up on the question above, I checked and found that get_fn_expr_rettype() works fine in FROM clause functions. Of course, as I mentioned earlier, fcinfo->resultinfo->expectedDesc is also available in FROM clause functions, and is probably more convenient to use for return type discovery. However, get_fn_expr_argtype() is your only real option as far as discovering run time argument types. I'm sure it also works in FROM clause functions (because I'm actively using it in plr) Joe