On 7/10/19 5:00 AM, Christoph Bilz wrote:
Hello,
i want write functions like this:
|CREATEFUNCTIONfoo(text)returns real as'<path>/foo.dll','foo'LANGUAGE C STRICT;"
CREATE FUNCTION foo2(text) returns table(c1 text, c2 int) as '<path>/foo2.dll', 'foo'
LANGUAGE C STRICT;|
So far, so clear. I don't want to return one scalar value or SETOF smth, I want
to start the function like this:
select * from foo; … and the rows will be returned.
I read the chapter
https://www.postgresql.org/docs/11/spi.html
and especially
https://www.postgresql.org/docs/11/xfunc-c.html#id-1.8.3.13.11
again and again but I found no solution. So, the functions should return rows
of data not just „one“ result or composite types.
The documentation regarding usable examples are very sparse, so I found and
tried with this pice of code:
|ArrayType*pg_array =DatumGetArrayTypeP(_row_val);c_array =(float
*)ARR_DATA_PTR(pg_array);pg_array_size =ARR_DIMS(pg_array)[0];|
—> so it’s clear how do I get my data via SPI* functionalty, the result set is
within the pg_array or the casted c_array.
I found this within funcapi.h:
|/* Type categories for get_call_result_type and siblings */typedef enum
TypeFuncClass {TYPEFUNC_SCALAR,/* scalar result type */TYPEFUNC_COMPOSITE,/*
determinable rowtype result */TYPEFUNC_COMPOSITE_DOMAIN,/* domain over
determinable rowtype result */TYPEFUNC_RECORD,/* indeterminate rowtype result
*/TYPEFUNC_OTHER /* bogus type, eg pseudotype */}TypeFuncClass;|
and tried:
|TupleDesc tupdesc;HeapTuple tuple;Datum rs[100];int tuplen;bool
nulls;if(get_call_result_type(fcinfo,NULL,&tupdesc)!=TYPEFUNC_RECORD)ereport(ERROR,(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),errmsg("function
returning record called in context ""that cannot accept type
record")));BlessTupleDesc(tupdesc);for(int j =0;j <100;j++){rs[j]=Float8GetDatum(c_array[j]);}tuplen
=tupdesc->natts;nulls =palloc(tuplen *sizeof(bool));tuple
=heap_form_tuple(tupdesc,rs,nulls);pfree(nulls);SPI_finish();PG_RETURN_DATUM(HeapTupleGetDatum(tuple));|
But it doesn’t work. Either the get_call_result_type fails because the function
definition doesn’t match or the the client process crashes because smth.
happens and I don’t know how this stuff should work.
So, due to the lack of examples in general and the sparse documentation about
it, any help will be appreciate.
A good place to find working examples is the source code of the various contrib
modules.
Regards
Ian Barwick
--
Ian Barwick https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
