Thread: how to return rows of data via function written by language C strict
Hello,
i want write functions like this:
CREATE FUNCTION foo(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
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.
Thanks.
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
Re: how to return rows of data via function written by language C strict
From
"David G. Johnston"
Date:
On Tue, Jul 9, 2019 at 1:00 PM Christoph Bilz <christoph.bilz@icloud.com> wrote:
Hello,i want write functions like this:CREATE FUNCTION foo(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.
Maybe SQL or pl/pgSQL would be a better choice then?
Also, if you plan to return more than one row you are, by definition, creating a SETOF (TABLE is just shorthand) function.
Also, for what its worth I have no idea with "smth" means here - but I also don't program C.
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")));
The inequality check above seems wrong - you want to error if you are presented with a record, not when you aren't.
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.
As Ian noted, contrib is usually the recommended source for up-to-date coding examples.
David J.