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';
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';
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=#
Generated with the Darkfish Rdoc Generator 2.