module PLRuby::Description::Function - RDoc Documentation

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