Creating a user defined function for atan2 in Open Ingres

Some RDBMSs have a CREATE FUNCTION statement to allow the creation of user defined functions. Ingres does not have this statement (at least not yet), but does allow the creation of user defined functions using C. Its a bit more work but not that hard really. I needed an atan2 function for some route planning code, so I set about creating a UDF in Ingres.
I started by creating an empty .c file and copying in one of the functions from $II_SYSTEM/ingres/demo/udadts/op.c, I used midpoint.c as it also took 2 parameters. I changed the name to arctan2:

 #ifdef __STDC__
int arctan2(II_SCB       *scb ,
               II_DATA_VALUE   *dv_1 ,
               II_DATA_VALUE   *dv_2 ,
               II_DATA_VALUE   *dv_result )
#else
int arctan2(scb, dv_1, dv_2, dv_result )
II_SCB          *scb;
II_DATA_VALUE   *dv_1;
II_DATA_VALUE   *dv_2;
II_DATA_VALUE   *dv_result;
#endif

Then I declared the local variables I would need to perform the calculation:

	double     dx;
	double     dy;
	double		res;

Then modify the error checking to check all parameters are floats:

    if (    (dv_1->db_datatype == II_FLOAT)
        &&  (dv_2->db_datatype == II_FLOAT)
        &&  (dv_result->db_datatype == II_FLOAT)
        &&  (dv_result->db_length == 8)
        &&  (dv_result->db_data)
       )
    {

Then copy the parameters from the function to the local variables where they will be strongly typed. I copied the macros from op.c for this:

    	F8ASSIGN_MACRO(*dv_1->db_data, dy);
    	F8ASSIGN_MACRO(*dv_2->db_data, dx);

The actual calculation is trivial as its already in libm:

 		res = atan2(dy, dx);

Then change the line which copies the result to the dv_result return structure:

 		F8ASSIGN_MACRO(res, *dv_result->db_data);

Finally return and leave the error checking as is:

    if (result)
        us_error(scb, 0x200023, "atan2: Invalid input");
    return(result);

I copied into in my c file the code for us_error and byte_copy. Next you'll need a few structures and macros to declare your function and its definition:

#define             FI_ATAN2          II_FISTART
static  II_DT_ID atan2_parms[] = { II_FLOAT, II_FLOAT};

#define         min(a,b)        ((a) <= (b) ? (a) : (b))

#define             F8ASSIGN_MACRO(a,b)         ((*(double *)&(b)) = (*(double *)&(a)))
#define             OP_ATAN2         II_OPSTART

static IIADD_FO_DFN   operators[] =
    {
        { II_O_OPERATION, { "atan2"},
            OP_ATAN2, II_NORMAL }       
    };
static IIADD_FI_DFN   function_instances[] =
    {
            { II_O_FUNCTION_INSTANCE , FI_ATAN2, II_NO_FI,
                OP_ATAN2, II_NORMAL, II_FID_F0_NOFLAGS, 0,
            2, atan2_parms, II_FLOAT, II_RES_FIXED, 8, II_PSVALID,
            arctan2, 0 }
    };
    
static  IIADD_DEFINITION        register_block =
{
    (IIADD_DEFINITION *) 0,
    (IIADD_DEFINITION *) 0,
    sizeof(IIADD_DEFINITION),
    IIADD_DFN2_TYPE,
    0,
    0,
    0,
    0,
    IIADD_INCONSISTENT, /* Ignore the current datatypes known by the recovery
                                    system */
    2,  /* Major id */
    0,  /* minor id */
    50,
    "UDT atan2",
    IIADD_T_FAIL_MASK | IIADD_T_LOG_MASK,
    0,
    0,
    0,
    (sizeof(operators)/sizeof(IIADD_FO_DFN)),
    operators,
    (sizeof(function_instances)/sizeof(IIADD_FI_DFN)),
    function_instances
};

Finally, the missing link (no Paul, that's not you) is to provide the hook so the dbms can register your function;

II_STATUS
#ifdef __STDC__
IIudadt_register(IIADD_DEFINITION  **ui_block_ptr ,
                  II_CALLBACKS  *callback_block )
#else
    IIudadt_register(ui_block_ptr, callback_block)
IIADD_DEFINITION  **ui_block_ptr;
II_CALLBACKS      *callback_block;
#endif
{

    register_block.add_count = register_block.add_dt_cnt +
                                register_block.add_fo_cnt +
                                register_block.add_fi_cnt;
    *ui_block_ptr = ®ister_block;

    return(II_OK);
}

Now you are ready to build it. A Makefile section like this should do:

atan2.o : atan2.c
	${CC} atan2.c -c -fPIC

And finally stop ingres, run iilink and when prompted enter the full path name of the atan2.o, and restart Ingres. If all went will then you should be able to run

select atan2(1,0);
select atan2(0,1);
select atan2(-1,0);
select atan2(0,-1);

and get the radians for these vectors. Y is the first parameter, so atan2(1, 0) is an upwards vertical vector, so has an atan2 value of pi/2 or about 1.571.

The full atan2.c file.

This is just a small part of what can be done using the Object Management Extension in Ingres (OME). You can also create user defined types and operators. See the demos/udadts directory for more stuff.