title: Loading, listing, and using R Modules and Functions in PL/R
body: <p>I am having difficulty with:</p>
  
  <ul>
  <li>Listing the R packages and functions available to PostgreSQL.</li>
  <li>Installing a package (such as <a href="http://rss.acs.unt.edu/Rdoc/library/Kendall/html/Kendall.html" rel="nofollow noreferrer">Kendall</a>) for use with PL/R</li>
  <li>Calling an R function within PostgreSQL</li>
  </ul>
  
  <h3>Listing Available R Packages</h3>
  
  <p><strong>Q.1.</strong> How do you find out what R modules have been loaded?</p>
  
  <pre><code>SELECT * FROM r_typenames();
  </code></pre>
  
  <p>That shows the types that are available, but what about checking if <code>Kendall( X, Y )</code> is loaded? For example, the <a href="http://www.joeconway.com/plr/doc/plr-module-funcs.html" rel="nofollow noreferrer">documentation</a> shows:</p>
  
  <pre><code>CREATE TABLE plr_modules (
    modseq int4,
    modsrc text
  );
  </code></pre>
  
  <p>That seems to allow inserting records to dictate that <code>Kendall</code> is to be loaded, but the following code doesn't explain, syntactically, how to ensure that it gets loaded:</p>
  
  <pre><code>INSERT INTO plr_modules
    VALUES (0, 'pg.test.module.load &lt;-function(msg) {print(msg)}');
  </code></pre>
  
  <p><strong>Q.2.</strong> What would the above line look like if you were trying to load <code>Kendall</code>?<br />
  <strong>Q.3.</strong> Is it applicable?</p>
  
  <h3>Installing R Packages</h3>
  
  <p>Using the "synaptic" package manager the following packages have been installed:</p>
  
  <pre><code>r-base
  r-base-core
  r-base-dev
  r-base-html
  r-base-latex
  r-cran-acepack
  r-cran-boot
  r-cran-car
  r-cran-chron
  r-cran-cluster
  r-cran-codetools
  r-cran-design
  r-cran-foreign
  r-cran-hmisc
  r-cran-kernsmooth
  r-cran-lattice
  r-cran-matrix
  r-cran-mgcv
  r-cran-nlme
  r-cran-quadprog
  r-cran-robustbase
  r-cran-rpart
  r-cran-survival
  r-cran-vr
  r-recommended
  </code></pre>
  
  <p><strong>Q.4.</strong> How do I know if Kendall is in there?<br />
  <strong>Q.5.</strong> If it isn't, how do I find out what package it is in?<br />
  <strong>Q.6.</strong> If it isn't in a package suitable for installing with <code>apt-get</code> (<code>aptitude</code>, <code>synaptic</code>, <code>dpkg</code>, what have you), how do I go about installing it on Ubuntu?<br />
  <strong>Q.7.</strong> Where are the installation steps documented?<br /></p>
  
  <h3>Calling R Functions</h3>
  
  <p>I have the following code:</p>
  
  <pre><code>EXECUTE 'SELECT '
    'regr_slope( amount, year_taken ),'
    'regr_intercept( amount, year_taken ),'
    'corr( amount, year_taken ),'
    'sum( measurements ) AS total_measurements '
  'FROM temp_regression'
  INTO STRICT slope, intercept, correlation, total_measurements;
  </code></pre>
  
  <p>This code calls the PostgreSQL function <code>corr</code> to calculate Pearson's correlation over the data. Ideally, I'd like to do the following (by switching <code>corr</code> for <code>plr_kendall</code>):</p>
  
  <pre><code>EXECUTE 'SELECT '
    'regr_slope( amount, year_taken ),'
    'regr_intercept( amount, year_taken ),'
    'plr_kendall( amount, year_taken ),'
    'sum( measurements ) AS total_measurements '
  'FROM temp_regression'
  INTO STRICT slope, intercept, correlation, total_measurements;
  </code></pre>
  
  <p><strong>Q.8.</strong> Do I have to write <code>plr_kendall</code> myself?<br />
  <strong>Q.9.</strong> Where can I find a <em>simple</em> example that walks through:</p>
  
  <ol>
  <li>Loading an R module into PG.</li>
  <li>Writing a PG wrapper for the desired R function.</li>
  <li>Calling the PG wrapper from a SELECT.</li>
  </ol>
  
  <p>For example, would the last two steps look like:</p>
  
  <pre><code>create or replace function plr_kendall( _float8, _float8 ) returns float as '
    agg_kendall(arg1, arg2)
  ' language 'plr';
  
  CREATE AGGREGATE agg_kendall (
    sfunc = plr_array_accum,
    basetype = float8, -- ???
    stype = _float8, -- ???
    finalfunc = plr_kendall
  );
  </code></pre>
  
  <p>And then the <code>SELECT</code> as above?</p>
  
  <p>Thank you!</p>