=begin
= PL/Ruby
* ((<Defining function in PL/Ruby>))
* ((<Trigger procedures in PL/Ruby>))
* ((<plruby_singleton_methods>))
* ((<Class and modules>))
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=#
=end