Thread: How to compare different datums within from a tuple?

Hello,
I try to write my first patch. It is too early to tell more about it, 
but I am just fiddling around with some prototypes.

Can someone tell me, how I can compare two datum fields, when I do not 
know the data type in advance inside an executor function?

For example, "x less than y" where x and y are of various types that 
form intervals. I have found the method ExecTuplesMatch, but it is only 
for equality comparison, I think. Another one is ApplySortComparator... 
maybe that's the correct way to go?

Some code to make things clearer...

Datum x = heap_getattr(out->tts_tuple,    node->xpos,    out->tts_tupleDescriptor,    &isNull1);
Datum y = slot_getattr(curr, node->ypos, &isNull2);

if (compareDatumWithCorrectMethod(x,y) < 0)
{ /* do something */
}

Thx,
Peter

On 8/10/15 12:36 PM, Peter Moser wrote:
> Can someone tell me, how I can compare two datum fields, when I do not
> know the data type in advance inside an executor function?
> 
> For example, "x less than y" where x and y are of various types that
> form intervals. I have found the method ExecTuplesMatch, but it is only
> for equality comparison, I think. Another one is ApplySortComparator...
> maybe that's the correct way to go?
> 
> Some code to make things clearer...
> 
> Datum x = heap_getattr(out->tts_tuple,
>         node->xpos,
>         out->tts_tupleDescriptor,
>         &isNull1);
> Datum y = slot_getattr(curr, node->ypos, &isNull2);
> 
> if (compareDatumWithCorrectMethod(x,y) < 0)
> {
>  /* do something */
> }

The tuple descriptor will contain the data type of the datum, so you can
use that to look up the default btree operator class and call the
respective operators in there.  But note that there is no single notion
of comparison in the system.  Comparison depends on operator class,
access method, possibly collation, null value treatment.  Some types
don't support comparison beyond equality.  A robust patch would need to
take that into account.

Am 11.08.2015 um 21:03 schrieb Peter Eisentraut:
> On 8/10/15 12:36 PM, Peter Moser wrote:
>> Can someone tell me, how I can compare two datum fields, when I do not
>> know the data type in advance inside an executor function?
>>
>> For example, "x less than y" where x and y are of various types that
>> form intervals. I have found the method ExecTuplesMatch, but it is only
>> for equality comparison, I think. Another one is ApplySortComparator...
>> maybe that's the correct way to go?
>>
>> Some code to make things clearer...
>>
>> Datum x = heap_getattr(out->tts_tuple,
>>          node->xpos,
>>          out->tts_tupleDescriptor,
>>          &isNull1);
>> Datum y = slot_getattr(curr, node->ypos, &isNull2);
>>
>> if (compareDatumWithCorrectMethod(x,y) < 0)
>> {
>>   /* do something */
>> }
>
> The tuple descriptor will contain the data type of the datum, so you can
> use that to look up the default btree operator class and call the
> respective operators in there.  But note that there is no single notion
> of comparison in the system.  Comparison depends on operator class,
> access method, possibly collation, null value treatment.  Some types
> don't support comparison beyond equality.  A robust patch would need to
> take that into account.
>

Ok, thank you.
Now I have a first solution. I am just wondering if this is robust, or 
do I miss something? Thanks for any comments...

My executor consumes rows from my own rewritten sub-query. From this 
sub-query I extract one sortGroupClause and from that the "eqop" and 
"sortop" during planning.

sgc = (SortGroupClause *) llast(sortClause);
node->eqOperator = sgc->eqop;
node->ltOperator = sgc->sortop;

The last sort clause uses the same types as the executor needs to 
compare later.

The executor initializes the methods with:

state->ltFunctionInfo = (FmgrInfo *) palloc(sizeof(FmgrInfo));
ltOperatorId = get_opcode(node->ltOperator);
fmgr_info(ltOperatorId, state->ltFunctionInfo);

state->eqFunctionInfo = (FmgrInfo *) palloc(sizeof(FmgrInfo));
eqOperatorId = get_opcode(node->eqOperator);
fmgr_info(eqOperatorId, state->eqFunctionInfo);

Finally I use them in this way...

static bool
isLessThan(Datum a, Datum b, FmgrInfo *ltFunctionInfo)
{return DatumGetBool(FunctionCall2(ltFunctionInfo, a, b));
}

static bool
isEqual(Datum a, Datum b, FmgrInfo *eqFunctionInfo)
{return DatumGetBool(FunctionCall2(eqFunctionInfo, a, b));
}

Am 11.08.2015 um 13:41 schrieb Anastasia Lubennikova:
>     Can someone tell me, how I can compare two datum fields, when I do
>     not know the data type in advance inside an executor function?
>
>
> In a nutshell, there is no way to compare Datums.
> Datum is an abstact data type. It's the backend internal representation
> of a single value of any SQL data type.
> The code using Datum has to know which type it is, since the Datum
> itself doesn't contain that information.
>
>     For example, "x less than y" where x and y are of various types that
>     form intervals. I have found the method ExecTuplesMatch, but it is
>     only for equality comparison, I think. Another one is
>     ApplySortComparator... maybe that's the correct way to go?
>
>     Some code to make things clearer...
>
>     Datum x = heap_getattr(out->tts_tuple,
>                      node->xpos,
>                      out->tts_tupleDescriptor,
>                      &isNull1);
>     Datum y = slot_getattr(curr, node->ypos, &isNull2);
>
>     if (compareDatumWithCorrectMethod(x,y) < 0)
>     {
>       /* do something */
>     }
>
>     Thx,
>     Peter
>
>
>     --
>     Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org
>     <mailto:pgsql-hackers@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-hackers
>
>
> Maybe you can use DatumGetXXX function to extract value. For example,
> DatumGetInt32.
> <http://doxygen.postgresql.org/postgres_8h.html#aacbc8a3ac6d52d85feaf0b7ac1b1160c>
> --
> Best regards,
> Lubennikova Anastasia

I did this with another column, that has always the result of 
row_number() as content, which I think will be fine when I use 
DatumGetInt32.

Thanks,
Peter