title: How to compile a user defined function in SQLite
body: <p>I have an example C function that I would like to attach to a database <code>temp.sqlite</code> (it's from an O'Reilly book, so I know it works). I read the section in the book and sqlite.org, but they assume away that I know how and where to compile this thing with the proper settings. I'm on either Mac (with XCode) or Ubuntu.</p>
  
  <p>I know enough C to alter the code to do what I want, but I have no idea what to do call it from my database <code>temp.sqlite</code>.</p>
  
  <p>Thanks for your help! I've been churning on this!</p>
  
  <p>Update: A few more hours in and I've scrapped together enough stuff from abandoned webpages to create a compile command and generate an error:</p>
  
  <pre><code>richard$ gcc -o wtavg wtavg.c -Wall -W -O2 -L/usr/local/lib -lsqlite3
  wtavg.c: In function ‘wtavg_init’:
  wtavg.c:63: warning: unused parameter ‘error’
  Undefined symbols:
    "_main", referenced from:
        start in crt1.10.6.o
  ld: symbol(s) not found
  collect2: ld returned 1 exit status
  </code></pre>
  
  <p>FWIW, here's wtavg.c, which is straight from the O'Reilly site provided in my book:</p>
  
  <pre><code>/* wtavg.c */
  
  #include "sqlite3ext.h"
  SQLITE_EXTENSION_INIT1;
  
  #include &lt;stdlib.h&gt;
  
  
  
  
  typedef struct wt_avg_state_s {
     double   total_data;  /* sum of (data * weight) values */
     double   total_wt;    /* sum of weight values */
  } wt_avg_state;
  
  
  static void wt_avg_step( sqlite3_context *ctx, int num_values, sqlite3_value **values )
  {
      double         row_wt = 1.0;
      int            type;
      wt_avg_state   *st = (wt_avg_state*)sqlite3_aggregate_context( ctx,
                                                 sizeof( wt_avg_state ) );
      if ( st == NULL ) {
          sqlite3_result_error_nomem( ctx );
          return;
      }
  
      /* Extract weight, if we have a weight and it looks like a number */
      if ( num_values == 2 ) {
          type = sqlite3_value_numeric_type( values[1] );
          if ( ( type == SQLITE_FLOAT )||( type == SQLITE_INTEGER ) ) {
              row_wt = sqlite3_value_double( values[1] );
          }
      }
  
      /* Extract data, if we were given something that looks like a number. */
      type = sqlite3_value_numeric_type( values[0] );
      if ( ( type == SQLITE_FLOAT )||( type == SQLITE_INTEGER ) ) {
          st-&gt;total_data += row_wt * sqlite3_value_double( values[0] );
          st-&gt;total_wt   += row_wt;
      }
  }
  
  
  static void wt_avg_final( sqlite3_context *ctx )
  {
      double         result = 0.0;
      wt_avg_state   *st = (wt_avg_state*)sqlite3_aggregate_context( ctx,
                                                 sizeof( wt_avg_state ) );
      if ( st == NULL ) {
          sqlite3_result_error_nomem( ctx );
          return;
      }
  
      if ( st-&gt;total_wt != 0.0 ) {
          result = st-&gt;total_data / st-&gt;total_wt;
      }
      sqlite3_result_double( ctx, result );
  }
  
  
  int wtavg_init( sqlite3 *db, char **error, const sqlite3_api_routines *api )
  {
      SQLITE_EXTENSION_INIT2(api);
  
      sqlite3_create_function( db, "wtavg", 1, SQLITE_UTF8,
              NULL, NULL, wt_avg_step, wt_avg_final );
      sqlite3_create_function( db, "wtavg", 2, SQLITE_UTF8,
              NULL, NULL, wt_avg_step, wt_avg_final );
  
      return SQLITE_OK;
  }
  </code></pre>