Module PLRuby::Description::Function
In: plruby.rb

To create a function in the PLRuby language use the syntax

   CREATE FUNCTION funcname(arguments_type) RETURNS type AS '

    # PLRuby function body

   ' LANGUAGE 'plruby';

when calling the function in a query, the arguments are given in the array args. To create a little max function returning the higher of two int4 values write :

   CREATE FUNCTION ruby_max(int4, int4) RETURNS int4 AS '
       if args[0] > args[1]
           return args[0]
       else
           return args[1]
       end
   ' LANGUAGE 'plruby';

Tuple arguments are given as hash. Here is an example that defines the overpaid_2 function (as found in the older Postgres documentation) in PLRuby.

   CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS '
       args[0]["salary"] > 200000 ||
          (args[0]["salary"] > 100000 && args[0]["age"] < 30)
   ' LANGUAGE 'plruby';

Warning : with PostgreSQL >= 7.4 "array" are given as a ruby Array

For example to define a function (int4[], int4) and return int4[], in version < 7.4 you write

   CREATE FUNCTION ruby_int4_accum(_int4, int4) RETURNS _int4 AS '
       if /\\{(\\d+),(\\d+)\\}/ =~ args[0]
           a, b = $1, $2
           newsum = a + args[1]
           newcnt = b + 1
       else
           raise "unexpected value #{args[0]}"
       end
       "{#{newsum},#{newcnt}}"
   ' LANGUAGE 'plruby';

This must now (>= 7.4) be written

   CREATE FUNCTION ruby_int4_accum(_int4, int4) RETURNS _int4 AS '
      a = args[0]
      [a[0] + args[1], a[1] + 1]
   ' LANGUAGE 'plruby';

Release PostgreSQL 8.0

With this version, plruby can have named arguments and the previous functions can be written

   CREATE FUNCTION ruby_max(a int4, b int4) RETURNS int4 AS '
       if a > b
           a
       else
           b
       end
   ' LANGUAGE 'plruby';

   CREATE FUNCTION overpaid_2 (emp EMP) RETURNS bool AS '
       emp["salary"] > 200000 ||
          (emp["salary"] > 100000 && emp["age"] < 30)
   ' LANGUAGE 'plruby';

With this version, you can also use transaction. For example

   plruby_test=# create table tu (a int, b int);
   CREATE TABLE
   plruby_test=# create or replace function tt(abort bool) returns bool as '
   plruby_test'#    transaction do |txn|
   plruby_test'#       PL.exec("insert into tu values (1, 2)")
   plruby_test'#       transaction do |txn1|
   plruby_test'#          PL.exec("insert into tu values (3, 4)")
   plruby_test'#          txn1.abort
   plruby_test'#       end
   plruby_test'#       PL.exec("insert into tu values (5, 6)")
   plruby_test'#       txn.abort if abort
   plruby_test'#    end
   plruby_test'#    abort
   plruby_test'# ' language 'plruby';
   CREATE FUNCTION
   plruby_test=#
   plruby_test=# select tt(true);
    tt
   ----
    t
   (1 row)

   plruby_test=# select * from tu;
    a | b
   ---+---
   (0 rows)

   plruby_test=# select tt(false);
    tt
   ----
    f
   (1 row)

   plruby_test=# select * from tu;
    a | b
   ---+---
    1 | 2
    5 | 6
   (2 rows)

   plruby_test=#

Classes and Modules

Class PLRuby::Description::Function::ExprMultiResult
Class PLRuby::Description::Function::SFRM

[Validate]