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
