Re: Foreign Database Connectivity

Tom Lane wrote:
> mlw <pgsql(at)mohawksoft(dot)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