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(c1text, 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
compositetypes.
> 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
resulttype */TYPEFUNC_COMPOSITE,/* determinable rowtype result */TYPEFUNC_COMPOSITE_DOMAIN,/* domain over determinable
rowtyperesult */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
returningrecord 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
clientprocess 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