Postgres C - Comparing Two Datum

Question

I am trying to do this in C so I can learn how to program C stored procedures in postgres. Please help me find the answer in C regardless of whether there is a solution in pure SQL.

I would like to write a simple C function that can be called as follows.

SELECT f(col1, col2, col3, col4) from T

If col1 < col2, return col3
If col1 > col2, return col4
Else return null

I do not know the types of any of the columns before the function is called. However, it is guaranteed that col1 and col2 will be of the same type (again, that type won't be known until the function is called). It is also guaranteed that col3 and col4 are of the same type as one another, but not necessarily the same as the type of col1 or col2. I am deliberately placing these constraints to avoid the use of anyelement in the answers; the argument types and the return type is unknown and we cannot use variadic anyelement since all types in the signature are not the same.

I started to write the function.

Datum f( PG_FUNCTION_ARGS )
{
     Datum      *args;
     bool       *nulls;
     Oid        *types;

     /* fetch argument values to build the object */
     nargs = extract_variadic_args(fcinfo, 0, false, &args, &types, &nulls);

     if (args[0] < args[1]) PG_RETURN_DATUM(args[2]);   // This has to be wrong
     else if (args[0] > args[1]) PG_RETURN_DATUM(args[3]);
     else PG_RETURN_NULL();
}

CREATE FUNCTION f(VARIADIC "any") RETURNS ??IDontKnow??
        AS '/var/lib/postgresql/f.so'
        LANGUAGE C IMMUTABLE STRICT;

I quickly found myself stuck with two issues.

I do not know how to do a type-safe comparison of two datums if I don't know their types beforehand.
I am not sure what the return type is for the CREATE FUNCTION.

EDIT - Progressing To A Working Comparison (Is it right? Who knows?)

Datum max_by_cols( PG_FUNCTION_ARGS )
{
     int         nargs;
     int         i;
     const char *sep = "";
     StringInfo  result;
     Datum      *args;
     bool       *nulls;
     Oid        *types;

     /* fetch argument values to build the object */
     nargs = extract_variadic_args(fcinfo, 0, false, &args, &types, &nulls);

     Oid elemtype = get_fn_expr_argtype(fcinfo->flinfo, 0);
     TypeCacheEntry *typentry = lookup_type_cache(elemtype, TYPECACHE_CMP_PROC_FINFO);
     FmgrInfo   *cmpfunc = &typentry->cmp_proc_finfo;
     Datum       c;

     c = FunctionCall2Coll(cmpfunc, typentry->typcollation, args[0], args[1]);
     PG_RETURN_DATUM(c);
}

CREATE OR REPLACE FUNCTION max_by_cols(VARIADIC "any") RETURNS INT
        AS '/var/lib/postgresql/max_by_cols.so'
        LANGUAGE C IMMUTABLE STRICT;

insert into test_t2 values
  ('Bob','Tom'),
  ('Tom', 'Bob'),
  ('Rich', 'Rich');

create table test_t3(rc int, rc2 int);
insert into test_t3 values
  (1,2),
  (2,1),
  (3,3);

select max_by_cols(rc, rc2) from test_t2;
-18
18
0

select max_by_cols(rc, rc2) from test_t3;
-1
1
0

The comparison now works for at least the two different data types I tested above. I do wonder if my compares should be using SortSupport, but that seems to be even more involved. I also still don't know how to specify an unknown return type (that isn't anyelement) in a CREATE FUNCTION statement. Maybe the magic word is INTERNAL. Time will tell. By then, I suspect I might be good friends with Laurenz, but first I must pass the twelve labors.

Edit 2 - A good non-C solution

https://dba.stackexchange.com/questions/63661/in-postgresql-is-there-a-type-safe-first-aggregate-function

select (array_agg(rc ORDER BY col1 desc, col2 desc, col3 desc))[1] as rc_value_prioritized
from test_t

I will continue to try to solve in C as time permits, but this solution seems quite good.

Postgres C - Comparing Two Datum

0 Answers