Re: how to return rows of data via function written by language C strict

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