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
