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