mysql aggregate UDF (user defined function) in C

Question

I need to write an aggregate extension function (implemented in C) for mySQL 5.x. I have scoured the documentation (including browsing sql/udf_example.c) but I do not find anything that is brief, to the point and shows me just what I need to do.

This is the problem:

I have a C struct (Foo)
I have a C function that takes an array of these Foo structs, performs an operation, on the array, and returns a double.

struct FooBar { char * date; double age; double wight; double salary; int eye_color; };

/* Processing function / double processFooBars(struct FooBar foobars, const size_t size);

/* MySQL table */ CREATE TABLE foo_bar( the_date DATE, double age, double weight, double salary, int eye_color};

I want to be able to create an aggregate function thus: (I maybe using PostgreSQL syntax)

CREATE AGGREGATE FUNCTION proc_foobar RETURNS REAL soname myshlib.so ALIAS my_wrapper_func

I can then use it in a MySQL Query thus:

SELECT proc_foobar() as likeability FROM foo_bar WHERE the_date BETWEEN '1-Jan-09' and '1-Dec-09'

What this query should then do would be to fetch all the the matching records from the table foo_bar, pass them to my wrapper function around processFooBar, which will then extract FooBar structs from the records received and then pass them to the C function that does the work and returns the value.

Its simpler to explain using (pseudo)code:

#ifdefined __cplusplus
extern "C" {
#endif

  /* this is the wrapper function that mySQL calls and passes the records to */
  double my_wrapper_func(/*Matching rows sent by mySQL + other info .. ?*/)
  {
     /* create FooBar Array from received record */
     struct FooBar ** the_array = ExtractArrayFromRowset(/*some params*/);
     double result = processFooBar(the_array, ARRAY_SIZE_MACRO(*the_array));

     /* free resources */
     FreeFooBarArray(the_array);
     RETURN_DOUBLE(result);  /* or similar macro to return a double in MySQL */
  }

#ifdefined __cplusplus
};
#endif

Could anyone provide a little snippet (or direct me to a snippet) that shows me how I can write the my_wrapper_func - or more to the point how I can implement the required functionality of writing an aggregate function as described above, as an extension function in C/C++.

Yada · Accepted Answer · 2009-12-16 15:58:45Z

2

Doesn't answer your question but article on MySQL udf is pretty good:

http://www.codeproject.com/KB/database/MySQL_UDFs.aspx

answered Dec 16, 2009 at 15:58

Yada

31.5k25 gold badges105 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stick it to THE MAN Over a year ago

Was a good link, but I found the PostgreSQL dodumentation more useful (than mySQL docs) in explaining the use of state variable and state transition function. I will accept your answer though.

Yada Over a year ago

cool. i'm learning UDF myself. If I find more info I'll make sure to post here

Collectives™ on Stack Overflow

mysql aggregate UDF (user defined function) in C

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related