PL/Ruby
PL/Ruby is a loadable procedural language for the Postgres database system that enable the Ruby language to create functions and trigger procedures
Functions and triggers are singleton methods of the module PLtemp.
WARNING
All arguments (to the function or the triggers) are passed as string values, except for NULL values represented by Qnil. You must explicitely call a conversion function (like to_i) if you want to use an argument as an integer
Defining function in PL/Ruby
To create a function in the PL/Ruby language use the syntax
CREATE FUNCTION funcname(arguments_type) RETURNS type AS ' # PL/Ruby function body ' LANGUAGE 'plruby';
when calling the function in a query, the arguments are given as string values 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].to_i > args[1].to_i
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 PL/Ruby.
CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS '
args[0]["salary"].to_f > 200000 ||
(args[0]["salary"].to_f > 100000 && args[0]["age"].to_i < 30)
' LANGUAGE 'plruby';
Trigger procedures in PL/Ruby
Trigger procedures are defined in Postgres as functions without arguments and a return type of opaque. In PL/Ruby the procedure is called with 4 arguments :
- new (hash, tainted)
-
an hash containing the values of the new table row on INSERT/UPDATE actions, or empty on DELETE.
- old (hash, tainted)
-
an hash containing the values of the old table row on UPDATE/DELETE actions, or empty on INSERT
- args (array, tainted, frozen)
-
An array of the arguments to the procedure as given in the CREATE TRIGGER statement
- tg (hash, tainted, frozen)
-
The following keys are defined
- name
-
The name of the trigger from the CREATE TRIGGER statement.
- relname
-
The name of the relation who has fired the trigger
- relid
-
The object ID of the table that caused the trigger procedure to be invoked.
- relatts
-
An array containing the name of the tables field.
- when
-
The constant PLruby::BEFORE, PLruby::AFTER or PLruby::UNKNOWN depending on the event of the trigger call.
- level
-
The constant PLruby::ROW or PLruby::STATEMENT depending on the event of the trigger call.
- op
-
The constant PLruby::INSERT, PLruby::UPDATE or PLruby::DELETE depending on the event of the trigger call.
The return value from a trigger procedure is one of the constant PLruby::OK or PLruby::SKIP, or an hash. If the return value is PLruby::OK, the normal operation (INSERT/UPDATE/DELETE) that fired this trigger will take place. Obviously, PLruby::SKIP tells the trigger manager to silently suppress the operation. The hash tells PL/Ruby to return a modified row to the trigger manager that will be inserted instead of the one given in new (INSERT/UPDATE only). Needless to say that all this is only meaningful when the trigger is BEFORE and FOR EACH ROW.
Here's a little example trigger procedure that forces an integer value in a table to keep track of the # of updates that are performed on the row. For new row's inserted, the value is initialized to 0 and then incremented on every update operation :
CREATE FUNCTION trigfunc_modcount() RETURNS OPAQUE AS '
case tg["op"]
when PLruby::INSERT
new[args[0]] = 0
when PLruby::UPDATE
new[args[0]] = old[args[0]].to_i + 1
else
return PLruby::OK
end
new
' LANGUAGE 'plruby';
CREATE TABLE mytab (num int4, modcnt int4, descr text);
CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');
A more complex example (extract from test_setup.sql in the distribution) which use the global variable $Plans to store a prepared plan
create function trig_pkey2_after() returns opaque as '
if ! $Plans.key?("plan_dta2_upd")
$Plans["plan_dta2_upd"] =
PLruby.prepare("update T_dta2
set ref1 = $3, ref2 = $4
where ref1 = $1 and ref2 = $2",
["int4", "varchar", "int4", "varchar" ])
$Plans["plan_dta2_del"] =
PLruby.prepare("delete from T_dta2
where ref1 = $1 and ref2 = $2",
["int4", "varchar"])
end
old_ref_follow = false
old_ref_delete = false
case tg["op"]
when PLruby::UPDATE
new["key2"] = new["key2"].upcase
old_ref_follow = (new["key1"] != old["key1"]) ||
(new["key2"] != old["key2"])
when PLruby::DELETE
old_ref_delete = true
end
if old_ref_follow
n = $Plans["plan_dta2_upd"].exec([old["key1"], old["key2"], new["key1"],
new["key2"]])
warn "updated #{n} entries in T_dta2 for new key in T_pkey2" if n != 0
end
if old_ref_delete
n = $Plans["plan_dta2_del"].exec([old["key1"], old["key2"]])
warn "deleted #{n} entries from T_dta2" if n != 0
end
PLruby::OK
' language 'plruby';
create trigger pkey2_after after update or delete on T_pkey2
for each row execute procedure
trig_pkey2_after();
plruby_singleton_methods
Sometime it can be usefull to define methods (in pure Ruby) which can be called from a PL/Ruby function or a PL/Ruby trigger.
In this case, you have 2 possibilities
- the "stupid" way
:-) :-) :-)
just close the current definition of the function (or trigger) with a
end and define your singleton method without the final end
Here a small and useless example
toto=> CREATE FUNCTION tutu() RETURNS int4 AS ' toto'> toto(1, 3) + toto(4, 4) toto'> end toto'> toto'> def PLtemp.toto(a, b) toto'> a + b toto'> ' LANGUAGE 'plruby'; CREATE toto=> select tutu(); tutu ---- 12 (1 row) toto=>
- create a table plruby_singleton_methods with the columns (name, args, body)
At load time, PL/Ruby look if it exist a table plruby_singleton_methods and if found try, for each row, to define singleton methods with the template :
def PLtemp.#{name} (#{args})
#{body}
end
The previous example can be written (you have a more complete example in ???)
toto=> SELECT * FROM plruby_singleton_methods; name|args|body ----+----+----- toto|a, b|a + b (1 row) toto=> CREATE FUNCTION tutu() RETURNS int4 AS ' toto'> toto(1, 3) + toto(4, 4) toto'> ' LANGUAGE 'plruby'; CREATE toto=> select tutu(); tutu ---- 12 (1 row) toto=>
Class and modules
Global
- warn [level], message
-
Ruby interface to PostgreSQL elog()
Possible value for level are NOTICE, DEBUG and NOIND
Use raise() if you want to simulate elog(ERROR, "...")
- $Plans (hash, tainted)
-
can be used to store prepared plans.
module PLruby
- quote string
-
Duplicates all occurences of single quote and backslash characters. It should be used when variables are used in the query string given to spi_exec or spi_prepare (not for the value list on execp).
- exec(string [, count [, type]])
- spi_exec(string [, count [, type]])
-
Call parser/planner/optimizer/executor for query. The optional count value tells spi_exec the maximum number of rows to be processed by the query.
- SELECT
-
If the query is a SELECT statement, an array is return (if count is not specified or with a value > 1). Each element of this array is an hash where the key is the column name. For example this procedure display all rows in the table pg_table.
CREATE FUNCTION pg_table_dis() RETURNS int4 AS ' res = PLruby.exec("select * from pg_class") res.each do |x| warn "======================" x.each do |y, z| warn "name = #{y} -- value = #{z}" end warn "======================" end return res.size ' LANGUAGE 'plruby';if type is specified it can take the value
- "array" return an array with the element ["name", "value", "type", "len", "typeid"]
- "hash" return an hash with the keys {"name", "value", "type", "len", "typeid"}
Example : create table T_pkey1 ( skey1 int4, skey2 varchar(20), stxt varchar(40) ); create function toto() returns bool as ' warn("=======") PLruby.exec("select * from T_pkey1", 1, "hash") do |a| warn(a.inspect) end warn("=======") PLruby.exec("select * from T_pkey1", 1, "array") do |a| warn(a.inspect) end warn("=======") PLruby.exec("select * from T_pkey1", 1) do |a| warn(a.inspect) end warn("=======") return true ' language 'plruby'; plruby_test=# select toto(); NOTICE: ======= NOTICE: {"name"=>"skey1", "typeid"=>23, "type"=>"int4", "value"=>"12", "len"=>4} NOTICE: {"name"=>"skey2", "typeid"=>1043, "type"=>"varchar", "value"=>"a", "len"=>20} NOTICE: {"name"=>"stxt", "typeid"=>1043, "type"=>"varchar", "value"=>"b", "len"=>40} NOTICE: ======= NOTICE: ["skey1", "12", "int4", 4, 23] NOTICE: ["skey2", "a", "varchar", 20, 1043] NOTICE: ["stxt", "b", "varchar", 40, 1043] NOTICE: ======= NOTICE: ["skey1", "12"] NOTICE: ["skey2", "a"] NOTICE: ["stxt", "b"] NOTICE: ======= toto ------ t (1 row) plruby_test=#A block can be specified, in this case a call to yield() will be made.
If count is specified with the value 1, only the first row (or FALSE if it fail) is returned as a hash. Here a little example :
CREATE FUNCTION pg_table_dis() RETURNS int4 AS ' PLruby.exec("select * from pg_class", 1) { |y, z| warn "name = #{y} -- value = #{z}" } return 1 ' LANGUAGE 'plruby'; - SELECT INTO, INSERT, UPDATE, DELETE
-
return the number of rows insered, updated, deleted, ...
- UTILITY
-
return TRUE
- prepare(string, [array])
- spi_prepare(string, [array])
-
Prepares AND SAVES a query plan for later execution. It is a bit different from the C level SPI_prepare in that the plan is automatically copied to the toplevel memory context. Thus, there is currently no way of preparing a plan without saving it.
If the query references arguments, the type names must be given as a Ruby array of strings. The return value from prepare is a PLrubyplan object to be used in subsequent calls to PLrubyplan#exec.
class PLrubyplan
- exec(values, [count [, type]])
- execp(values, [count [, type]])
- exec("values" => values, "count" => count, "output" => type)
- execp("values" => values, "count" => count, "output" => type)
-
Execute a prepared plan from PLruby#prepare with variable substitution. The optional count value tells PLrubyplan#exec the maximum number of rows to be processed by the query.
If there was a typelist given to PLruby#prepare, an array of values of exactly the same length must be given to PLrubyplan#exec as first argument. If the type list on PLruby#prepare was empty, this argument must be omitted.
If the query is a SELECT statement, the same as described for PLruby#exec happens for the loop-body and the variables for the fields selected.
If type is specified it can take the values
- "array" return an array with the element ["name", "value", "type", "len", "typeid"]
- "hash" return an hash with the keys {"name", "value", "type", "len", "typeid"}
Here's an example for a PL/Ruby function using a prepared plan :
CREATE FUNCTION t1_count(int4, int4) RETURNS int4 AS ' if ! $Plans.key?("plan") # prepare the saved plan on the first call $Plans["plan"] = PLruby.prepare("SELECT count(*) AS cnt FROM t1 WHERE num >= $1 AND num <= $2", ["int4", "int4"]) end n = $Plans["plan"].exec([args[0], args[1]], 1) n["cnt"] ' LANGUAGE 'plruby'; - each(values, [count [, type ]]) { ... }
- fetch(values, [count [, type ]]) { ... }
- each("values" => values, "count" => count, "output" => type) { ... }
- fetch("values" => values, "count" => count, "output" => type) { ... }
-
Same then #exec but a call to SPI_cursor_open(), SPI_cursor_fetch() is made.
Can be used only with a block and a SELECT statement
create function toto() returns bool as ' plan = PLruby.prepare("select * from T_pkey1") warn "=====> ALL" plan.each do |x| warn(x.inspect) end warn "=====> FIRST 2" plan.each("count" => 2) do |x| warn(x.inspect) end return true ' language 'plruby'; plruby_test=# select * from T_pkey1; skey1 | skey2 | stxt -------+-------+------ 12 | a | b 24 | c | d 36 | e | f (3 rows) plruby_test=# plruby_test=# select toto(); NOTICE: =====> ALL NOTICE: {"skey1"=>"12", "skey2"=>"a", "stxt"=>"b"} NOTICE: {"skey1"=>"24", "skey2"=>"c", "stxt"=>"d"} NOTICE: {"skey1"=>"36", "skey2"=>"e", "stxt"=>"f"} NOTICE: =====> FIRST 2 NOTICE: {"skey1"=>"12", "skey2"=>"a", "stxt"=>"b"} NOTICE: {"skey1"=>"24", "skey2"=>"c", "stxt"=>"d"} toto ------ t (1 row) plruby_test=#