Michael> I would like to query a table with two columns, 1st being just a
key field,
Michael> second being a debit field (float) ie
Michael> account:
Michael> service | debit
Michael> _______________________
Michael> 1 | 50
Michael> 2 | 100
Michael> 3 | -30
Michael> I would like to query the table with a select and get back a
balance field
Michael> calculated at run time as well ie
Michael> select debit, balance()
Michael> from account
Michael> debit | debit
Michael> _______________________
Michael> 50 | 50
Michael> 100 | 150
Michael> -30 | 120
>Yes, you can do the above with an UDF function, but it will not work
>if you use 'order by' on it!
>Check the udf_example.c file and the MySQL documentation how to create
>udf functions.
OK I read the UDF function help, and udf_example.c, and but can't see how
to access adjacent cells. Optimistically I would like to be able to write
some code like this, but dont know how to access currentRow, or the value
at a particular coordinate.
CREATE FUNCTION balance RETURNS REAL SONAME "udf_example.so";
double balance(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
char *error)
{
if(currentRow == 0)
return value(currentRow, currentColumn-1)
return value(currentRow-1, currentColumn) + value(currentRow, currentColum
-1)
}
Apparently my code may have to look more like this, by using the primary
key of the table to get the values I need.
double balance(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
char *error)
{
if(indexId == 1)
return indexId.debit;
return account(indexId).debit + account(indexId - 1).balance
}
or
double balance(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
char *error)
{
float total = 0;
for(int i = 1; i<=indexId; i++)
total += account(i).debit;
}
If anyone can tell me how to access the data structures in MySQL to get any
of those things out I should be able to finish this little problem.
Mike
