In Files

PLRuby::Description::Function

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=#

[Validate]

Generated with the Darkfish Rdoc Generator 2.