Thread: Setof RangeType returns

Setof RangeType returns

From

Patrick Handja

Date:

27 November 2020, 00:28:20

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

From

Heikki Linnakangas

Date:

27 November 2020, 12:01:40

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

Re: Setof RangeType returns

From

Patrick Handja

Date:

27 November 2020, 21:46:49

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 <hlinnaka@iki.fi> 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

Re: Setof RangeType returns

From

Tom Lane

Date:

27 November 2020, 22:24:09

Patrick Handja <patrick.bungama@gmail.com> 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

From

Patrick Handja

Date:

02 December 2020, 00:42:56

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 <tgl@sss.pgh.pa.us> a écrit :

Patrick Handja <patrick.bungama@gmail.com> 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

From

Patrick Handja

Date:

02 December 2020, 01:19:02

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 <patrick.bungama@gmail.com> 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 <tgl@sss.pgh.pa.us> a écrit :
Patrick Handja <patrick.bungama@gmail.com> 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

From

Chapman Flack

Date:

02 December 2020, 01:31:29

On 12/01/20 17:19, Patrick Handja wrote:
> Just figured out. I think I can use RANGE_EMPTY and it will be like:
> 
>> typcache =range_get_typcache(fcinfo, RangeTypeGetOid(RANGE_EMPTY));

Are you sure you are not simply looking for
range_get_typcache(fcinfo, INT4RANGEOID) ?

Top-posting is not used for replies on these lists.

Regards,
-Chap

Re: Setof RangeType returns

From

Tom Lane

Date:

02 December 2020, 01:39:24

Patrick Handja <patrick.bungama@gmail.com> 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

Re: Setof RangeType returns

From

Patrick Handja

Date:

10 December 2020, 04:54:13

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 <tgl@sss.pgh.pa.us> a écrit :

Patrick Handja <patrick.bungama@gmail.com> 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