

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.


Wed, 16 Apr 2003 15:28:32 -0400
mlw <pgsql@mohawksoft.com>
Re: Foreign Database Connectivity
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


Wed, 16 Apr 2003 16:02:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Foreign Database Connectivity 
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


Wed, 16 Apr 2003 14:03:27 -0700
Joe Conway <mail@joeconway.com>
Re: Foreign Database Connectivity
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


Wed, 16 Apr 2003 14:08:08 -0700
Joe Conway <mail@joeconway.com>
Re: Foreign Database Connectivity
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


Sat, 03 May 2003 08:17:30 -0700
Joe Conway <mail@joeconway.com>
Re: Foreign Database Connectivity