FoxTalk

Today I had to build some complex functions for my postgresql server. Well, ordinarily they wouldn't be THAT complex but when limited to PL/PGSQL things like operations on timestamps can be quite tricky. So I thought I'd give PLRuby a go. You can get it from Rubyforge.

Building it on Ubuntu

Once downloaded, I needed to build it against my postgresql install. Ensure that when you are building it, that you use --enable-conversions to make things easier for you when handling native SQL types.

sudo apt-get install postgresql-server-dev-8.3
tar -zxpvf plruby-0.5.3.tar.gz
cd plruby-0.5.3/
ruby ./extconf --enable-conversions \
  --with-pgsql-include=/usr/include/postgresql/8.3/server/
make
sudo make install

Creating a Function

Creating a function was easy! All you need to know is that your arguments come as the 'args' array and that if you compiled with --enable-conversions the arguments will be native ruby types. You can find more examples of PLRuby functions here. Happy Rubying!

CREATE OR REPLACE FUNCTION bin(timestamp with time zone,
     integer) RETURNS timestamp AS '
  esec = args[0].to_i
  return Time.at(esec - (esec % args[1]))
' LANGUAGE 'plruby';