Re: Setof RangeType returns
Thanks for the feedback Tom!
> TypeCacheEntry *typcache;
> PG_RETURN_RANGE_P(range_serialize(typcache, &lower, &upper, false));
The use of typcache really confuses me. range_get_typcache() is used in
order to initialize typcache
> typcache =range_get_typcache(fcinfo, RangeTypeGetOid(r1));
In my case, I do not have a range as an argument, I am receiving 2 int,
which I am using to create a range. How can I initialize typcache in this
case?
That's the part where I am really stuck.
Datum
make_range_griis(PG_FUNCTION_ARGS){
RangeBound lower;
RangeBound upper;
int32 start = PG_GETARG_INT32(0);
int32 finish = PG_GETARG_INT32(1);
lower.val = (Datum) (start);
lower.infinite = false;
lower.lower = true;
upper.val = (Datum) (finish);
upper.infinite = false;
upper.lower = false;
if (!lower.infinite && !lower.inclusive){
lower.val = DirectFunctionCall2(int4pl, lower.val, Int32GetDatum(1));
lower.inclusive = true;
}
if (!upper.infinite && upper.inclusive){
upper.val = DirectFunctionCall2(int4pl, upper.val, Int32GetDatum(1));
upper.inclusive = false;
}
TypeCacheEntry *typcache;
//> typcache = ??;
PG_RETURN_RANGE_P(range_serialize(typcache, &lower, &upper, false));
}
regards,
*Andjasubu Bungama, Patrick *
Le ven. 27 nov. 2020 à 14:24, Tom Lane a écrit :
> Patrick Handja writes:
> > This is what I am doing:
>
> > static int
> > get_range_lower(FunctionCallInfo fcinfo, RangeType *r1)
> > {
> > /* Return NULL if there's no finite lower bound */
> > if (empty || lower.infinite)
> > PG_RETURN_NULL();
>
> You can't really use PG_RETURN_NULL here, mainly because there is
> no good value for it to return from get_range_lower().
>
> > return (lower.val);
>
> Here and elsewhere, you're cavalierly casting between Datum and int.
> While you can get away with that as long as the SQL type you're
> working with is int4, it's bad style; mainly because it's confusing,
> but also because you'll have a hard time adapting the code if you
> ever want to work with some other type. Use DatumGetInt32 or
> Int32GetDatum as appropriate.
>
> > TypeCacheEntry *typcache;
> > PG_RETURN_RANGE_P(range_serialize(typcache, &lower, &upper, false));
>
> This sure appears to be passing an uninitialized typcache pointer
> to range_serialize(). If your compiler isn't whining about that,
> you don't have adequately paranoid warning options enabled.
> That's an excellent way to waste a lot of time, as you just have.
> C is an unforgiving language, so you need all the help you can get.
>
> BTW, use of PG_RETURN_RANGE_P here isn't very appropriate either,
> since the function is not declared as returning Datum.
>
> regards, tom lane
>
Re: Setof RangeType returns
Just figured out. I think I can use RANGE_EMPTY and it will be like:
> typcache =range_get_typcache(fcinfo, RangeTypeGetOid(RANGE_EMPTY));
Regards,
*Andjasubu Bungama, Patrick *
Le mar. 1 déc. 2020 à 16:42, Patrick Handja a
écrit :
> Thanks for the feedback Tom!
>
> > TypeCacheEntry *typcache;
> > PG_RETURN_RANGE_P(range_serialize(typcache, &lower, &upper, false));
>
> The use of typcache really confuses me. range_get_typcache() is used in
> order to initialize typcache
> > typcache =range_get_typcache(fcinfo, RangeTypeGetOid(r1));
>
> In my case, I do not have a range as an argument, I am receiving 2 int,
> which I am using to create a range. How can I initialize typcache in this
> case?
> That's the part where I am really stuck.
>
> Datum
> make_range_griis(PG_FUNCTION_ARGS){
> RangeBound lower;
> RangeBound upper;
>
> int32 start = PG_GETARG_INT32(0);
> int32 finish = PG_GETARG_INT32(1);
>
> lower.val = (Datum) (start);
> lower.infinite = false;
> lower.lower = true;
>
> upper.val = (Datum) (finish);
> upper.infinite = false;
> upper.lower = false;
>
> if (!lower.infinite && !lower.inclusive){
> lower.val = DirectFunctionCall2(int4pl, lower.val, Int32GetDatum(1));
> lower.inclusive = true;
> }
>
> if (!upper.infinite && upper.inclusive){
> upper.val = DirectFunctionCall2(int4pl, upper.val, Int32GetDatum(1));
> upper.inclusive = false;
> }
>
> TypeCacheEntry *typcache;
> //> typcache = ??;
> PG_RETURN_RANGE_P(range_serialize(typcache, &lower, &upper, false));
> }
>
> regards,
>
> *Andjasubu Bungama, Patrick *
>
>
>
>
> Le ven. 27 nov. 2020 à 14:24, Tom Lane a écrit :
>
>> Patrick Handja writes:
>> > This is what I am doing:
>>
>> > static int
>> > get_range_lower(FunctionCallInfo fcinfo, RangeType *r1)
>> > {
>> > /* Return NULL if there's no finite lower bound */
>> > if (empty || lower.infinite)
>> > PG_RETURN_NULL();
>>
>> You can't really use PG_RETURN_NULL here, mainly because there is
>> no good value for it to return from get_range_lower().
>>
>> > return (lower.val);
>>
>> Here and elsewhere, you're cavalierly casting between Datum and int.
>> While you can get away with that as long as the SQL type you're
>> working with is int4, it's bad style; mainly because it's confusing,
>> but also because you'll have a hard time adapting the code if you
>> ever want to work with some other type. Use DatumGetInt32 or
>> Int32GetDatum as appropriate.
>>
>> > TypeCacheEntry *typcache;
>> > PG_RETURN_RANGE_P(range_serialize(typcache, &lower, &upper, false));
>>
>> This sure appears to be passing an uninitialized typcache pointer
>> to range_serialize(). If your compiler isn't whining about that,
>> you don't have adequately paranoid warning options enabled.
>> That's an excellent way to waste a lot of time, as you just have.
>> C is an unforgiving language, so you need all the help you can get.
>>
>> BTW, use of PG_RETURN_RANGE_P here isn't very appropriate either,
>> since the function is not declared as returning Datum.
>>
>> regards, tom lane
>>
>
Re: Setof RangeType returns
Hello, After some days, I finally found what I was looking for. Actually this worked: > Oid rngtypid = get_fn_expr_rettype(fcinfo->flinfo); > typcache = range_get_typcache(fcinfo, rngtypid); Thanks for the help. *Andjasubu Bungama, Patrick * Le mar. 1 déc. 2020 à 17:39, Tom Lane a écrit : > Patrick Handja writes: > > In my case, I do not have a range as an argument, I am receiving 2 int, > > which I am using to create a range. How can I initialize typcache in this > > case? > > You should be passing down the pointer from the outer function, which > does have it at hand, no? > > regards, tom lane >
How to get Relation tuples in C function
Greetings! I would like to know if there is a better way to pass a relation or if the relation name (CString) as a parameter in a C function and thus be able to manipulate its tuples. The documentation is available here: https://www.postgresql.org/docs/13/xfunc-c.html#id-1.8.3.13.11. But it is not quite clear enough on how to retrieve tuples. The handling of these is quite clear. The only function I'm currently using (but not working) is the TupleDesc TypeGetTupleDesc (Oid typeoid, List * colaliases) function. Do we have a function like TupleDesc RelationNameGetTupleDesc (const char * relname) (Old and deprecated)? regards, *Andjasubu Bungama, Patrick *
SPI return
Greetings,
I have spent a couple of days working on separated lib in PostgreSql, and I
am facing some issues with the return of data using SPI (Server Programming
Interface).
I have this simple get_tuples function used in the main function foobar. My
example is very simple too (Using a previously created table a with a
single column and with some data):
SELECT foobar('select * from a');
I am not able to get the array returned by get_tuples function, and I am
thinking it's SPI_finish(). When I tried to print my array tuples itens
after SPI_finish(), It is not working.
## My Code ##
static char**
get_tuples(char *command) {
int ret;
int8 rows;
char **tuples;
SPI_connect();
ret = SPI_exec(command, 0);
rows = SPI_processed;
tuples = palloc(sizeof(char*)*rows);
if (ret > 0 && SPI_tuptable != NULL) {
TupleDesc tupdesc = SPI_tuptable->tupdesc;
SPITupleTable *tuptable = SPI_tuptable;
uint64 j;
for (j = 0; j < rows; j++){
HeapTuple tuple = tuptable->vals[j];
int i;
for (i = 1; i <= tupdesc->natts; i++){
char *rowid;
rowid = SPI_getvalue(tuple, tupdesc, i);
tuples[j] = palloc(strlen(rowid)*sizeof(char));
tuples[j]= rowid;
}
}
}
// Printing my array to verify if I have all tuples, in fact I have all
of the
for (int i = 0; i < rows; ++i) {
elog(INFO, "Item: %s", *(tuples + i));
}
pfree(command);
SPI_finish();
return tuples;
}
Datum
foobar (PG_FUNCTION_ARGS) {
char *command;
command = text_to_cstring(PG_GETARG_TEXT_PP(0));
get_tuples(command);
// *When I tried to do something like this, I am losing the connection
with the server (error)*
//elog(INFO, "*: %s", *(get_tuples(command) + 1));
PG_RETURN_INT64(1);
}
CREATE FUNCTION foobar(text) RETURNS int8
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;
regards,
*Andjasubu Bungama, Patrick *
Setof RangeType returns
Hello, I am currently working on Library with some specific operators to manipulate RangeType in PostGreSQL. I would like to know if it is possible to return a setof rangetype elements in Postresql in C-Language function using the suggestion like specified here: https://www.postgresql.org/docs/current/xfunc-c.html#XFUNC-C-RETURN-SET. I have been trying this for days. If what I am trying to do is impossible, is there any way I can use to have a RangeType set return? Regards, *Andjasubu Bungama, Patrick *
Re: Setof RangeType returns
Hello Heikki,
Thank you for responding to my email.
This is what I am doing:
//= C file
static int
get_range_lower(FunctionCallInfo fcinfo, RangeType *r1)
{
TypeCacheEntry *typcache;
RangeBound lower;
RangeBound upper;
bool empty;
typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1));
range_deserialize(typcache, r1, &lower, &upper, &empty);
/* Return NULL if there's no finite lower bound */
if (empty || lower.infinite)
PG_RETURN_NULL();
return (lower.val);
}
static int
get_range_upper_griis(FunctionCallInfo fcinfo, RangeType *r1)
{
TypeCacheEntry *typcache;
RangeBound lower;
RangeBound upper;
bool empty;
typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1));
range_deserialize(typcache, r1, &lower, &upper, &empty);
/* Return NULL if there's no finite upper bound */
if (empty || upper.infinite)
PG_RETURN_NULL();
return (upper.val);
}
static RangeType *
make_range(int start, int finish)
{
RangeBound lower;
RangeBound upper;
lower.val = (Datum) (start);
lower.infinite = false;
lower.inclusive = true;
lower.lower = true;
upper.val = (Datum) (finish);
upper.infinite = false;
upper.inclusive = false;
upper.lower = false;
if (!lower.infinite && !lower.inclusive)
{
lower.val = DirectFunctionCall2(int4pl, lower.val, Int32GetDatum(1));
lower.inclusive = true;
}
if (!upper.infinite && upper.inclusive)
{
upper.val = DirectFunctionCall2(int4pl, upper.val, Int32GetDatum(1));
upper.inclusive = false;
}
TypeCacheEntry *typcache;
PG_RETURN_RANGE_P(range_serialize(typcache, &lower, &upper, false));
}
typedef struct
{
int32 current;
int32 finish;
int32 step;
} generate_series_range_fctx;
static inline bool
control_increment(int32 a, int32 b, int32 *result)
{
int64 res = (int64) a + (int64) b;
if (res > PG_INT32_MAX || res < PG_INT32_MIN)
{
*result = 0x5EED;
return true;
}
*result = (int32) res;
return false;
}
PG_FUNCTION_INFO_V1(generate_ranges);
Datum
generate_ranges(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
generate_series_range_fctx *fctx;
MemoryContext oldcontext;
RangeType *r1 = PG_GETARG_RANGE_P(0);
RangeType *result;
TypeCacheEntry *typcache;
typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1));
int32 lower = get_range_lower(fcinfo, r1);
int32 upper = get_range_upper(fcinfo, r1);
if (SRF_IS_FIRSTCALL())
{
int32 start = lower;
int32 finish = upper;
int32 step = 1;
funcctx = SRF_FIRSTCALL_INIT();
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
fctx = (generate_series_range_fctx *)
palloc(sizeof(generate_series_range_fctx));
fctx->current = start;
fctx->finish = finish;
fctx->step = step;
funcctx->user_fctx = fctx;
MemoryContextSwitchTo(oldcontext);
}
funcctx = SRF_PERCALL_SETUP();
fctx = funcctx->user_fctx;
result = make_range(fctx->current, fctx->current+1);
if ((fctx->step > 0 && fctx->current <= fctx->finish) ||
(fctx->step < 0 && fctx->current >= fctx->finish))
{
if (control_increment(fctx->current, fctx->step, &fctx->current))
fctx->step = 0;
SRF_RETURN_NEXT(funcctx, PointerGetDatum(result));
}
else
SRF_RETURN_DONE(funcctx);
}
//= SQL file
CREATE FUNCTION generate_ranges(anyrange) RETURNS setof anyrange
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;
//= Test File Expected
SELECT generate_ranges(int4range(4,10));
generate_ranges
---
[4,5)
[5,6)
[6,7)
[7,8)
[8,9)
[9,10)
[10,11)
(7 row)
//=========
Regards,
*Andjasubu Bungama, Patrick *
Le ven. 27 nov. 2020 à 04:01, Heikki Linnakangas a écrit :
> On 26/11/2020 23:28, Patrick Handja wrote:
> > Hello,
> >
> > I am currently working on Library with some specific operators to
> > manipulate RangeType in PostGreSQL. I would like to know if it is
> > possible to return a setof rangetype elements in Postresql in C-Language
> > function using the suggestion like specified here:
> > https://www.postgresql.org/docs/current/xfunc-c.html#XFUNC-C-RETURN-SET
> > <https://www.postgresql.org/docs/current/xfunc-c.html#XFUNC-C-RETURN-SET>.
>
> > I have been trying this for days. If what I am trying to do is
> > impossible, is there any way I can use to have a RangeType set return?
>
> Yes, it is possible.
>
> I bet there's just a silly little bug or misunderstanding in your code.
> This stuff can be fiddly. Feel free to post what you have here, and I'm
> sure someone will point out where the problem is very quickly.
>
> - Heikki
>
