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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Christoph Bilz
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.



Reply | Threaded
Open this post in threaded view
|

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

Ian Barwick-3
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


Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
In reply to this post by Christoph Bilz
On Tue, Jul 9, 2019 at 1:00 PM Christoph Bilz <[hidden email]> 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.